public inbox for [email protected]
help / color / mirror / Atom feedFrom: Mike Broers <[email protected]>
To: David Rowley <[email protected]>
Cc: postgres performance list <[email protected]>
Subject: Re: query of partitioned object doesnt use index in qa
Date: Thu, 14 Sep 2017 08:25:22 -0500
Message-ID: <CAB9893j=+i0xMpAuyBdXGq7fVJ-eJQnfL8GPYEL2MF3yV5prZw@mail.gmail.com> (raw)
In-Reply-To: <CAKJS1f-AkrKfLEsrb7ymZve_b3e9cTKUcEdeeeJkVWnOTVdPnA@mail.gmail.com>
References: <CAB9893izHQaPTk1bGEDs8UTQUTKtpj1sk6PLyWrvXU-j0JBFaQ@mail.gmail.com>
<CAKJS1f-AkrKfLEsrb7ymZve_b3e9cTKUcEdeeeJkVWnOTVdPnA@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
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 <[email protected]>
wrote:
> On 14 September 2017 at 08:28, Mike Broers <[email protected]> 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
>
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: query of partitioned object doesnt use index in qa
In-Reply-To: <CAB9893j=+i0xMpAuyBdXGq7fVJ-eJQnfL8GPYEL2MF3yV5prZw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox