Btee-index select query takes long time My query is below:
WITH CTE AS(
         SELECT Id,
                SaleTime
         FROM   tbl_sales_log
         WHERE  saletime &gt; ’2019 -06 -01 00:00:00’
                AND saletime &lt; ’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 &gt; ’2019 -06 -01 00:00:00’
       AND R.saletime &lt; ’2019 -06 -30 23:59:59’
ORDER BY
       R.saletime DESC limit 1000;

Explain (analyze,buffers) is below:

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.
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 &gt; ’2019 -06 -01 00:00:00’
            AND saletime &lt; ’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 &gt; ’2019 -06 -01 00:00:00’
       AND R.saletime &lt; ’2019 -06 -30 23:59:59’
ORDER BY
       R.saletime DESC limit 1000;