Please can you help with a slow query that we have with a large amount of data (~500K rows). We're on Postgres 10.

SELECT mike_romeo, SUM(hotel) as counts
FROM whiskey_lima
    yankee = 'kilo'
    whiskey_india = 'a_value'
    echo = 'romeo_november'

And my table looks like this:

    Column     |            Type             | Collation | Nullable |                        Default
 id            | bigint                      |           | not null | nextval('id_seq'::regclass)
 yankee        | mike_hotel.quebec           |           | not null |
 whiskey_india | character varying           |           | not null |
 echo          | mike_hotel.romeo_yankee     |           | not null |
 mike_romeo    | character varying           |           | not null |
 oscar         | timestamp without time zone |           | not null |
 hotel         | integer                     |           | not null |
 papa          | timestamp without time zone |           |          |
    "whiskey_lima_pkey" PRIMARY KEY, btree (id)
    "index_unique" UNIQUE, btree (echo, mike_romeo, yankee, whiskey_india, oscar)
    "index_query" btree (yankee, whiskey_india, echo)
    "index_on_oscar" btree (oscar)

Our EXPLAIN (ANALYSE, BUFFERS) looks like this: https://explain.depesz.com/s/4lU

GroupAggregate  (cost=77294.010..78398.740 rows=38882 width=69) (actual time=56343.468..59194.302 rows=527807 loops=1)
    Group Key: mike_romeo
    Buffers: shared hit=61428 read=68664, temp read=23413 written=23450
  ->  Sort  (cost=77294.010..77532.650 rows=95455 width=65) (actual time=56343.461..58848.379 rows=530929 loops=1)
          Sort Key: mike_romeo
          Sort Method: external merge  Disk: 111488kB
          Buffers: shared hit=61428 read=68664, temp read=23413 written=23450
        ->  Index Scan using mike_quebec on whiskey_lima  (cost=0.560..67104.720 rows=95455 width=65) (actual time=1.647..38814.509 rows=530929 loops=1)
                Index Cond: ((yankee = 'kilo'::mike_hotel.quebec) AND ((whiskey_india)::text = 'juliet'::text) AND (echo = 'romeo_november'::mike_hotel.romeo_yankee))
                Buffers: shared hit=61425 read=68664


What should we focus on - are there any obvious solutions?

Can we avoid the sort?

Why has the query planner underestimated the rows - could this be causing some slowness? We're trying to enable extended statistics

We're looking into increasing work_mem so that the disk sort is mitigated - would this make the whole query performant?

What else am I missing?

Thanks a lot for your help


I would try with an index like:

 echo, yankee, whiskey_india, mike_romeo

Perhaps you can replace your index_unique with:

 "index_unique" UNIQUE, btree (echo, yankee, whiskey_india, mike_romeo, oscar)  

The idea is to put the predicates first in then index, followed by group by columns.

| improve this answer | |

An index on (yankee, whiskey_india, echo, mike_romeo, hotel) might be optimal. The first 3 columns satisfy the equality conditions--they don't have to be in that order, as long as those 3 columns occupy the first 3 slots in the index. Adding mike_romeo can avoid the sort by having the part of the index satisfying the first 3 be read in pre-sorted order. Adding hotel can allow the whole thing to be an index-only scan, as all the data to satisfy the query can be obtained from the index without needing to consult the table, which can generate a lot of random IO.

The planner is assuming all three equality conditions are independent so their individual selectivities can be multiplied, while apparently that is not true. But fixing it might not help anyway, as what other plan is there for it to use? Unless it is going to seq-scan the full table...

Increasing work_mem could make the sort faster, or slower (it depends on too many things to predict, like whether a merge sort is friendlier to your CPU cache structure than a giant quick sort), or it could allow the planner to switch away from sorting to doing a hashaggregate instead. Only one way to find out.

Two more things to focus on--I am assuming that your query is IO bound. If you immediately repeat the query, is it faster the 2nd time due to data being in cache already? Can you turn on track_io_timing and redo the EXPLAIN (ANALYSE, BUFFERS)

Sometimes EXPLAIN (ANALYSE) imposes enough instrumentation overhead that it distorts the timing of what it is measuring. Can you do a EXPLAIN (ANALYSE, TIMING OFF) and see if that makes the overall run time faster than the full ANALYSE? You would want to switch back and forth between then a few times to make sure it is not a caching effect.

| improve this answer | |

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.