Btee-index select query takes long time
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;