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
my_table_6 has 35 million records.It has the btee-index for the column I’d.
When I select just 1000 records it takes almost 30 seconds.
How to optimize it.
Note: PostgreSQL 12 server is running on Windows