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.rowley@xxxxxxxxxxxxxxx> wrote:
On 14 September 2017 at 08:28, Mike Broers <mbroers@xxxxxxxxx> 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
