Btee-index select query takes long time

Posted on

Question :

My query is below:

WITH CTE AS(
         SELECT Id,
                SaleTime
         FROM   tbl_sales_log
         WHERE  saletime >2019 -06 -01 00:00:00AND saletime <2019 -06 -30 23:59:59AND centerid IN (708, 805, 432, 403)
                AND itemid IN (3, 6)
         ORDER BY
                saletime DESC limit 1000 offset 0
     ) 

SELECT Id,
       col1,
       col2,
       col3,
       col4,
       col5,
       col6,
       col7,
       col8,
       saletime
FROM   my_table_6 R
WHERE  R.id = ANY(
           SELECT Id
           FROM   CTE limit 1000
       )
       AND R.saletime >2019 -06 -01 00:00:00AND R.saletime <2019 -06 -30 23:59:59ORDER BY
       R.saletime DESC limit 1000;

Explain (analyze,buffers) is below:

Explain statement picture

my_table_6 has 35 million records. It has a btree-index for the column Id.

When I select just 1000 records it takes almost 30 seconds.

How can I optimize it?

Note: PostgreSQL 12 server is running on Windows.

CTE query executed within 500 millisecond, but querying from my_table_6 takes more time. I have checked this many times.

Answer :

Please don’t post execution plans as screen shots.

The problem is that the index scan on my_table_6 takes 26.5 milliseconds on average and is executed 1000 times.

Try VACUUM on the table to remove dead rows. If the index is bloated, REINDEX might help.

It looks like you don’t have a covering index for your CTE query on the my_table_6 table, so the PostgreSQL engine is doing its best by scanning the closest index it can find.

Create an index on the my_table_6 table that covers id and saletime, and then recheck the EXPLAIN ANALYZE is showing an index seek operation instead of index scan.

Also why not try re-writing your query in a more relational way like this, to see if you get a better query plan:

WITH CTE AS
(
     SELECT Id,
            SaleTime
     FROM   tbl_sales_log
     WHERE  saletime >2019 -06 -01 00:00:00AND saletime <2019 -06 -30 23:59:59AND centerid IN (708, 805, 432, 403)
            AND itemid IN (3, 6)
     ORDER BY
            saletime DESC limit 1000 offset 0
) 

SELECT Id,
       col1,
       col2,
       col3,
       col4,
       col5,
       col6,
       col7,
       col8,
       saletime
FROM my_table_6 R
INNER JOIN CTE C
    ON R.id = C.id
WHERE  R.saletime >2019 -06 -01 00:00:00AND R.saletime <2019 -06 -30 23:59:59ORDER BY
       R.saletime DESC limit 1000;

Leave a Reply

Your email address will not be published. Required fields are marked *