0

My query is below:

WITH CTE AS(
         SELECT Id,
                SaleTime
         FROM   tbl_sales_log
         WHERE  saletime > ’2019 -06 -01 00:00:00’
                AND saletime < ’2019 -06 -30 23:59:59’
                AND 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:00’
       AND R.saletime < ’2019 -06 -30 23:59:59’
ORDER 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.

3
  • 1
    Hi, and welcome to the forum! Please never post screenshots for reasons outlined here. There are some tips on asking questions here on my profile - you might want to take a look? Help us to help you - these forums are a great resource and you will get all the more out of them if you follow the guidelines - Best... – Vérace Jan 7 at 8:57
  • Also, please post the DDL for all tables referred to in your query. – Vérace Jan 7 at 9:08
  • What indexes do you have? Perhaps (SaleTime, Id) in that order? – Rick James Jan 10 at 18:41
0

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.

0

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:00’
            AND saletime < ’2019 -06 -30 23:59:59’
            AND 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:00’
       AND R.saletime < ’2019 -06 -30 23:59:59’
ORDER BY
       R.saletime DESC limit 1000;
2
  • CTE query executed with in 500 millisecond, but querying from my_table_6 only takes more time.I have checked many times – nikitha palani Jan 7 at 4:57
  • @nikithapalani Sorry it was hard to read the screenshot of your EXPLAIN ANALYZE (please try to include the actual text next time). Please see my updated answer. – J.D. Jan 7 at 13:15

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.