Hey Shaun, Thanks for quick reply
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. Let us know your thoughts. On Thu, Nov 6, 2014 at 7:39 PM, Shaun Thomas <stho...@optionshouse.com> wrote: > Hi Krit, > > 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? > > ______________________________________________ > > See http://www.peak6.com/email_disclaimer/ for terms and conditions > related to this email > -- Kirit Parmar
