We have not changed the default_statistics_target, so it shall remain to its default value 100.
I would like to share our observation on this if you can infer anything from it. : It started using query plan 1 after a our scheduled vacuum run and it continued to use the plan for a day till next day scheduled vacuum run. it switched to optimal query plan 2 and slowness disappeared after then.
However this is what we are thinking to do incase if resurface :
1. run vacuum analyse (assuming it will help to query planner to choose best possible path as it updates the stats in pg_statistic).
2. If it does not work, increase the default_statistics_target for the column.
It looks like your actual problem is here: > Index Scan using t1_orderid_creationtime_idx on t1 > (cost=0.43..1181104.36 rows=9879754 width=158) > (actual time=0.021..60830.724 rows=2416614 loops=1
This index scan estimates 9.8M rows, and had to touch 2.4M. The issue is that your LIMIT clause makes the planner overly optimistic. The worst case cost estimate for this part of the query is about 1.2M, which is much higher than the SEQ SCAN variation you posted. The planner must think it can get the rows without incurring the full cost, otherwise I can't see how the 1.2M cost estimate wasn't rolled into the total estimate.
Unfortunately behavior like this is pretty common when using LIMIT clauses. Sometimes the planner thinks it can get results much faster than it actually can, and it ends up reading a much larger portion of the data than it assumed would be necessary.
Just out of curiosity, Can you tell me what your default_statistics_target is?
