pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: pgsql-perform <pgsql-performance@postgresql.org>
Subject: Bad n_distinct estimation; hacks suggested?
Date: Tue, 19 Apr 2005 12:09:05 -0700
Message-ID: <200504191209.05181.josh@agliodbs.com> (raw)

Folks,

Params:  PostgreSQL 8.0.1 on Solaris 10
Statistics = 500
(tablenames have been changed to protect NDA)

e1=# select tablename, null_frac, correlation, n_distinct from pg_stats where 
tablename = 'clickstream1' andattname = 'session_id';
      tablename       | null_frac | correlation | n_distinct
----------------------+-----------+-------------+------------
 clickstream1         |         0 |    0.412034 |     378174
(2 rows)

e1=# select count(distinct session_id) from clickstream1;
  count
---------
 3174813

As you can see, n_distinct estimation is off by a factor of 10x and it's 
causing query planning problems.   Any suggested hacks to improve the 
histogram on this?

(BTW, increasing the stats to 1000 only doubles n_distinct, and doesn't solve 
the problem)

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



view thread (53+ messages)  latest in thread

Message-ID: <200504191209.05181.josh@agliodbs.com>
Permalink:  ../200504191209.05181.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200504191209.05181.josh@agliodbs.com

 · 

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: josh@agliodbs.com
  Subject: Re: Bad n_distinct estimation; hacks suggested?
  In-Reply-To: <200504191209.05181.josh@agliodbs.com>

* 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