MIME-Version: 1.0
In-Reply-To: 
 <CAB9893iWzGVDh1GRKPdDwUx=fbe4uKCx2GxQOy3jDQ9Xe+uD8Q@mail.gmail.com>
References: 
 <CAB9893izHQaPTk1bGEDs8UTQUTKtpj1sk6PLyWrvXU-j0JBFaQ@mail.gmail.com>
 <CAKJS1f-AkrKfLEsrb7ymZve_b3e9cTKUcEdeeeJkVWnOTVdPnA@mail.gmail.com>
 <CAB9893hmTC-TMeFN8S91NWS_++3w2t5D0X7O-ogsZZ8zEyxv6w@mail.gmail.com>
 <3138.1505508143@sss.pgh.pa.us>
 <CAB9893g-1fpvh=0snbe7qFJKfXEsn2YxR3ZWZ6-JxrMCyaZg3Q@mail.gmail.com>
 <CAB9893iWzGVDh1GRKPdDwUx=fbe4uKCx2GxQOy3jDQ9Xe+uD8Q@mail.gmail.com>
From: David Rowley <david.rowley@2ndquadrant.com>
Date: Thu, 21 Sep 2017 11:05:43 +1200
Message-ID: 
 <CAKJS1f_ePEgnk_VwbB110c0xFOUVRLXOZvoJNhEzDjw8Jr9uiA@mail.gmail.com>
Subject: Re: query of partitioned object doesnt use index in qa
To: Mike Broers <mbroers@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
	postgres performance list <pgsql-performance@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 21 September 2017 at 04:15, Mike Broers <mbroers@gmail.com> wrote:
> Ultimately I think this is just highlighting the need in my environment to
> set random_page_cost lower (we are on an SSD SAN anyway..), but I dont think
> I have a satisfactory reason by the row estimates are so bad in the QA
> planner and why it doesnt use that partition index there.

Without the index there are no stats to allow the planner to perform a
good estimate on "e.body->>'SID' is not null", so it applies a default
of 99.5%. So, as a simple example, if you have a partition with 1
million rows. If you apply 99.5% to that you get 995000 rows. Now if
you add the selectivity for "e.validation_status_code = 'P' ", let's
say that's 50%, the row estimate for the entire WHERE clause would be
497500 (1000000 * 0.995 * 0.5). Since the 99.5% is applied in both
cases, then the only variable part is validation_status_code. Perhaps
validation_status_code  = 'P' is much more common in QA than in
production.

You can look at the stats as gathered by ANALYZE with:

\x on
select * from pg_stats where tablename = 'event__99999999' and attname
= 'validation_status_code';
\x off

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance