Re: query of partitioned object doesnt use index in qa

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(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> On 14 September 2017 at 08:28, Mike Broers <mbroers(at)gmail(dot)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
>