pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: josh@agliodbs.com
Cc: pgsql-performance@postgreSQL.org
Subject: Re: Odd problem with performance in duplicate database
Date: Mon, 11 Aug 2003 20:29:22 -0400
Message-ID: <27573.1060648162@sss.pgh.pa.us> (raw)
In-Reply-To: <200308111716.22288.josh@agliodbs.com>
References: <ENEDIMLCODBHILEONAOMAEBNCEAA.pdarley@kinesis-cem.com>
	<200308111654.20929.josh@agliodbs.com>
	<27393.1060646753@sss.pgh.pa.us>
	<200308111716.22288.josh@agliodbs.com>

Josh Berkus <josh@agliodbs.com> writes:
> Tom,
>> Okay, here's our problem:
>> 
>> live DB: tgroup_id  n_distinct = -1
>> 
>> test DN: tgroup_id  n_distinct = 11
>> 
>> The former estimate actually means that it thinks tgroup_id is a unique
>> column, whereas the latter says there are only 11 distinct values in the
>> column.  I assume the former is much nearer to the truth (how many rows
>> in cases, and how many distinct tgroup_id values)?

> The real case is that there are 113 distinct tgroup_ids, which cover
> about 10% of the population of cases.  The other 90% is NULL.  The
> average tgroup_id is shared between 4.7 cases.

> So this seems like sampling error.

Partly.  The numbers suggest that in ANALYZE's default sample of 3000
rows, it's only finding about a dozen non-null tgroup_ids (yielding the
0.996 null_frac value); and that in one case all dozen are different and
in the other case there are two duplicates.  It would help if you
boosted the stats target for this column by a factor of 10.  (You can
do that in 7.2, btw --- IIRC the only problem is that a pg_dump won't
show that you did so.)

But the other part of the problem is that in 7.2, the join selectivity
estimator is way off when you are joining a unique column (like the pkey
on the other side) to a column with a very large fraction of nulls.
We only discovered this recently; it's fixed as of 7.3.3:

2003-04-15 01:18  tgl

	* src/backend/utils/adt/selfuncs.c (REL7_3_STABLE): eqjoinsel's
	logic for case where MCV lists are not present should account for
	NULLs; in hindsight this is obvious since the code for the
	MCV-lists case would reduce to this when there are zero entries in
	both lists.  Per example from Alec Mitchell.

Possibly you could backpatch that into 7.2, although I'd think an update
to 7.3.4 would be a more profitable use of time.

			regards, tom lane



view thread (14+ messages)  latest in thread

Message-ID: <27573.1060648162@sss.pgh.pa.us>
Permalink:  ../27573.1060648162@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/27573.1060648162@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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, josh@agliodbs.com
  Subject: Re: Odd problem with performance in duplicate database
  In-Reply-To: <27573.1060648162@sss.pgh.pa.us>
  References: <ENEDIMLCODBHILEONAOMAEBNCEAA.pdarley@kinesis-cem.com> <200308111654.20929.josh@agliodbs.com> <27393.1060646753@sss.pgh.pa.us> <200308111716.22288.josh@agliodbs.com> <27573.1060648162@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