2

I've the following query

SELECT * 
FROM "items" 
WHERE (("field1" = 'somevalue' OR "field2" = 'somevalue' OR "field3" = 'somevalue')) 
ORDER BY "createdAt" DESC 
LIMIT 20 OFFSET 0;

Here is the result of EXPLAIN ANALYZE in staging

Limit  (cost=118.72..118.77 rows=20 width=2212) (actual time=0.407..0.416 rows=20 loops=1)
  ->  Sort  (cost=118.72..118.80 rows=30 width=2212) (actual time=0.405..0.412 rows=20 loops=1)
        Sort Key: "createdAt" DESC
        Sort Method: quicksort  Memory: 75kB
        ->  Bitmap Heap Scan on items item  (cost=13.10..117.98 rows=30 width=2212) (actual time=0.078..0.231 rows=25 loops=1)
              Recheck Cond: (("col1" = 'something'::text) OR ("col2" = 'something'::text) OR (col3 = 'something'::text))
              Heap Blocks: exact=21
              ->  BitmapOr  (cost=13.10..13.10 rows=30 width=0) (actual time=0.056..0.056 rows=0 loops=1)
                    ->  Bitmap Index Scan on "item_col1_col2_col3_index"  (cost=0.00..4.47 rows=25 width=0) (actual time=0.038..0.038 rows=25 loops=1)
                          Index Cond: ("col1" = 'something'::text)
                    ->  Bitmap Index Scan on "col2_index"  (cost=0.00..4.29 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=1)
                          Index Cond: ("col2" = 'something'::text)
                    ->  Bitmap Index Scan on col3_index  (cost=0.00..4.31 rows=4 width=0) (actual time=0.008..0.008 rows=0 loops=1)
                          Index Cond: (col3 = '5a9793161131a39df46fe851'::text)
Planning time: 0.867 ms
Execution time: 0.631 ms

The item_col1_col2_col3_index, as the name suggests, is a compound index of col1, col2 and col3.

And here is the result of EXPLAIN ANALYZE in production

Limit  (cost=0.43..13020.16 rows=20 width=2378) (actual time=1060451.457..1060451.457 rows=0 loops=1)
  ->  Index Scan Backward using "createdAt_index" on items item  (cost=0.43..7711586.19 rows=11846 width=2378) (actual time=1060451.453..1060451.453 rows=0 loops=1)
        Filter: (("col1" = 'something'::text) OR ("col2" = 'something'::text) OR (col3 = 'something'::text))
        Rows Removed by Filter: 2987649
Planning time: 0.539 ms
Execution time: 1060459.032 ms

As you can see, in production it was executed with createdAt_index. The process of filtered out unmatched records were done without index though the item_col1_col2_col3_index exists. Therefore it took very long.

The main difference is in staging, the table only has about 5000 records. In production, it contains about 3.4 million records

I've been running VACUUM ANALYZE items since 2 hours ago (it's still running at the time I posted this question)

Is there any idea how can I improve the speed of the query?

  • 1
    Could you maybe add the DDL of the tables involved? That would help. – hot2use Jun 7 '18 at 7:37
  • 2
    Hmm, the query is using an index (just differently than on staging though). However, doing an index scan on 3 million rows should not take that long. This sounds like a horribly slow harddisk to me. Can you edit your question and add the output of explain (analyze, buffers) from production and the definition of the "createdAt_index" index? – a_horse_with_no_name Jun 7 '18 at 8:06
  • Compound index can NOT be used in this case effectively - it is used as a compact version of table only. – Akina Jun 7 '18 at 8:12
  • In the latter case, does the backwards index scan stop after 20 rows have been selected as specified by the limit? Assuming yes, it would seem a reasonable plan to use the created index. However if there's 3 million rows removed by the filter, maybe the column statistics are off? The composite index seems to be useless in this case anyway, even in staging it's just using multiple indexes together (and the first column of the composite, as field1 seems to be missing its own index). – Kayaman Jun 7 '18 at 8:43
  • 1
    Wait for the vacuum analyze to finish and then update the question. – jjanes Jun 8 '18 at 11:20

Your Answer

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

Browse other questions tagged or ask your own question.