pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Craig Ringer <ringerc@ringerc.id.au>
Cc: Craig James <craig_james@emolecules.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance Anomaly with "col in (A, B)" vs. "col = A OR col = B" ver. 9.0.3
Date: Mon, 26 Sep 2011 20:39:23 -0400
Message-ID: <7045.1317083963@sss.pgh.pa.us> (raw)
In-Reply-To: <4E811441.6060604@ringerc.id.au>
References: <CAPcyiQ1U1k=mcpKT2ecy02Vr=2Eqpee3tnQLpfvW-aViSp=Big@mail.gmail.com>
	<9594.1317056871@sss.pgh.pa.us>
	<4E80B3F9.8070001@emolecules.com>
	<10130.1317058511@sss.pgh.pa.us>
	<4E811441.6060604@ringerc.id.au>

Craig Ringer <ringerc@ringerc.id.au> writes:
> This has me wondering about putting together a maintenance/analysis tool 
> that generates and captures stats from several ANALYZE runs and compares 
> them to see if they're reasonably consistent. It then re-runs with 
> higher targets as a one-off, again to see if the stats agree, before 
> restoring the targets to defaults. The tool could crunch comparisons of 
> the resulting stats and warn about tables or columns where the default 
> stats targets aren't sufficient.

It would certainly be useful to have such a tool, but I suspect it's
easier said than done.  The problem is to know whether the queries on
that table are particularly sensitive to having better stats.  I think
we've largely solved issues having to do with the quality of the
histogram (eg, what fraction of the table has values falling into some
range), and the remaining trouble spots have to do with predicting the
frequency of specific values that are too infrequent to have made it
into the most-common-values list.  Enlarging the MCV list helps not so
much by getting these long-tail values into the MCV list --- they
probably still aren't there --- as by allowing us to tighten the upper
bound on what the frequency of an unrepresented value must be.  So what
you have to ask is how many queries care about that.  Craig James'
example query in this thread is sort of a worst case, because the values
it's searching for are in fact not in the table at all.

			regards, tom lane



view thread (10+ messages)  latest in thread

Message-ID: <7045.1317083963@sss.pgh.pa.us>
Permalink:  ../7045.1317083963@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/7045.1317083963@sss.pgh.pa.us

 · 

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: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, ringerc@ringerc.id.au, craig_james@emolecules.com
  Subject: Re: Performance Anomaly with "col in (A, B)" vs. "col = A OR col = B" ver. 9.0.3
  In-Reply-To: <7045.1317083963@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox