4

I'm using Postgres 9.4, with a recently analyzed database. These are my tables:

Materialized view "public.vw_chemical_summary_by_ccg"
     Column      |         Type         | Modifiers
-----------------+----------------------+-----------
 processing_date | date                 |
 pct_id          | character varying(3) |
 chemical_id     | character varying(9) |
 items           | bigint               |
 cost            | double precision     |
Indexes:
    "vw_idx_chem_by_ccg_chem_id" btree (chemical_id)
    "vw_idx_chem_by_ccg_chem_id_vc" btree (chemical_id varchar_pattern_ops)
    "vw_idx_chem_by_ccg_joint_id" btree (pct_id, chemical_id)

               Table "public.frontend_pct"
      Column       |          Type           | Modifiers
-------------------+-------------------------+-----------
 code              | character varying(3)    | not null
 name              | character varying(200)  |
 org_type          | character varying(9)    | not null
Indexes:
    "frontend_pct_pkey" PRIMARY KEY, btree (code)
    "frontend_pct_code_1df55e2c36c298b2_like" btree (code varchar_pattern_ops)

This is my query:

EXPAIN (ANALYSE, BUFFERS) 
SELECT pr.pct_id AS row_id, pc.name AS row_name, 
       pr.processing_date AS date, SUM(pr.cost) AS actual_cost, 
       SUM(pr.items) AS items 
FROM vw_chemical_summary_by_ccg pr 
JOIN frontend_pct pc 
  ON pr.pct_id=pc.code AND pc.org_type='CCG' 
GROUP BY row_id, row_name, date 
ORDER BY date, row_id;

The results of the analyse shows a very slow sort on 5 million rows, BEFORE running the GroupAggregate.

The result of the GroupAggregate is just 5,000 rows. So wouldn't it make more sense to aggregate first, then sort?

Explain here: http://explain.depesz.com/s/IS1

Any other suggestions for speeding up the query would also be very welcome.

  • Can you GROUP BY and ORDER BY the same set of columns, in the same order? – Craig Ringer Aug 5 '15 at 11:18
  • 2
    I think the sort step you see, is done for the group by. If you look at the "Sort Key", it's exactly the columns you specified for the group by. Maybe it's doing the two step grouping because of lack of work_mem. What happens if you increase work_mem for the session? (set work_mem='512MB') – a_horse_with_no_name Aug 5 '15 at 11:34
  • No real change if I both increase work_mem to 512MB, and also explicitly GROUP and ORDER by the same set of columns: explain.depesz.com/s/QcL – Richard Aug 5 '15 at 12:00
  • Could it be the lack of indexes on the date field that is the problem? – Richard Aug 5 '15 at 12:00
  • Apparently 512MB is still not enough, because the sorting is still done on disk – a_horse_with_no_name Aug 5 '15 at 12:08
4

I ran into the same situation, a large number of rows of raw data being sorted on disk in order to be fed into a GroupAggregate which drastically reduced the number of rows.

I tried removing the ORDER BY clause; that allowed the optimizer to choose HashAggregate, which was significantly faster. To give a picture of the speed increase, a particular subset of my data took about

  • 13.9 seconds using the original Sort and GroupAggregate
  • 9.4 seconds for a simple EXPLAIN ANALYZE SELECT without a GROUP BY clause
  • 9.6 seconds using the unsorted HashAggregate

It also scaled similarly with more data. I'm only using about a million rows, so you may still need a higher work_mem for your larger dataset (I am using 10MB). Although http://www.depesz.com/2013/05/09/explaining-the-unexplainable-part-3/#hash-aggregate suggests that HashAggregate will go to disk if it doesn't fit in memory, I found that reducing work_mem just prompted the planner to revert to the Sort/GroupAggregate plan.

I added SELECT * FROM (<unsorted query>) AS nested ORDER BY 1,2,3 to the outside and this sorts the aggregated output to give the same results as the original query, while still using the fast HashAggregate. I think there is perhaps an optimization opportunity here that the planner is not taking.

I tested this on 8.4 and 9.3 and got the same results with both.

Update:

If one of the aggregates being calculated is a COUNT(DISTINCT <fields>), then apparently HashAggregate can not be used.

| improve this answer | |
0

Unless the query engine knows that there are only going to be a few results from the GROUP BY so it can file the values and related aggregates into a fixed size number of bins while the data streams through, it is not practical to group without the data pre-sorted. So the sort operation you are seeing is specific ally for the group operation you have specified. If you can (re)arrange the query (and perhaps available indexes) in such a way that the data comes out into the grouping state naturally sorted by the appropriate columns, then you can avoid this extra step.

| improve this answer | |
  • 1
    Thanks for the advice. Any idea how I could rearrange the query to do this? The query is actually for the whole table. The underlying data should actually be in date order already, as it would have been added to the database in date order. – Richard Aug 5 '15 at 14:51
  • That could be very specific to postgres' query planner, and I'm only an MS SQL Server man when we get to that level of internal knowledge. As a general case try think how the query planned would produce the result. If you can make it start with the objects that are the source of your ordering columns then reference other objects (by syntax structural layout such as index use hints). Ordering by the same columns you are grouping by may help. Do be careful ordering the query planner around: you can create a situation that is far less efficient in cases other than those you explicitly test. – David Spillett Aug 6 '15 at 8:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.