From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jorge Montero <jorge_montero@homedecorators.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hashaggregate estimates
Date: Wed, 13 Jan 2010 17:08:15 -0500
Message-ID: <5783.1263420495@sss.pgh.pa.us> (raw)
In-Reply-To: <4B4DEDB5.2E1C.0042.0@homedecorators.com>
References: <4B4DEDB5.2E1C.0042.0@homedecorators.com>
"Jorge Montero" <jorge_montero@homedecorators.com> writes:
> The killer seems to be the row aggregation. There are about 95K
> different values of sku_id in the sales table, and even the best
> seller items are a very small percentage of all rows, so expecting the
> aggregation to consolidate the rows 50:1 like it does in one of the
> explains above is a pipe dream. I've increased statistics in sku_id
> into the three digits, but results are not any better
Yeah, estimating the number of distinct values from a sample of the data
is a hard problem :-(.
> Is there any way I can coax Postgres into making a more realistic
> aggregation estimate?
There's no good way in 8.3. (In CVS HEAD there's a feature to manually
override the ndistinct estimate for a column.) In principle you could
manually update the pg_statistic.stadistinct value for the column, but
the trouble with that is the next ANALYZE will overwrite it.
regards, tom lane
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, jorge_montero@homedecorators.com
Subject: Re: Hashaggregate estimates
In-Reply-To: <5783.1263420495@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