Thanks for the suggestions, I'll futz with random_page_cost and effective_cache_size a bit and follow up, as well as try to provide an explain analyze on both (if the longer query ever returns!)
Most appreciated. On Wed, Sep 13, 2017 at 4:57 PM, David Rowley <david.row...@2ndquadrant.com> wrote: > On 14 September 2017 at 08:28, Mike Broers <mbro...@gmail.com> wrote: > > I have a query of a partitioned table that uses the partition index in > > production but uses sequence scans in qa. The only major difference I > can > > tell is the partitions are much smaller in qa. In production the > partitions > > range in size from around 25 million rows to around 60 million rows, in > QA > > the partitions are between 4 and 12 million rows. I would think this > would > > be big enough to get the planner to prefer the index but this is the > major > > difference between the two database as far as I can tell. > > > QA: > > > │ -> Seq Scan on event__99999999 e_1 > > (cost=0.00..2527918.06 rows=11457484 width=782) │ > > > > Production: > > > > │ -> Index Scan using > > ix_event__00011162_landing_id on event__00011162 e_1 > (cost=0.56..15476.59 > > rows=23400 width=572) │ > > > If QA has between 4 and 12 million rows, then the planner's row > estimate for the condition thinks 11457484 are going to match, so a > Seqscan is likely best here. If those estimates are off then it might > be worth double checking your nightly analyze is working correctly on > QA. > > The planner may be able to be coaxed into using the index with a > higher effective_cache_size and/or a lower random_page_cost setting, > although you really should be looking at those row estimates first. > Showing us the EXPLAIN ANALYZE would have been much more useful so > that we could have seen if those are accurate or not. > > -- > David Rowley http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
