1

I have this query running on Greenplum 6(Postgres 9.4) too slow as it performing a sequential scan. But I am thinking why it's not performing an index backward scan to fetch the records since there is an index on the same column.

explain analyse select * from tab1 where create_time > now() - interval '1d' order by create_time desc limit 20;

https://explain.depesz.com/s/UTA7#html

The index is a composite index (create_time,col2), could it be the reason behind sequential scan? I have already updated statistics of the table and also the bloat ≈5% only.

goodfella
  • 589
  • 4
  • 14

1 Answers1

0

When I changed query like below it executes faster by using index.

select * from tab1 where create_time > (SELECT now() - interval '1 day') order by create_time desc limit 20;

This way it helped the outer query to get the date as constant literal before it's run time. But I am not sure if it's a problem in old Postgres planner or it caused by Greenplum optimisation.

goodfella
  • 589
  • 4
  • 14