From: Stefan Andreatta <s.andreatta@synedra.com>
To: Peter Geoghegan <peter@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: serious under-estimation of n_distinct for clustered distributions
Date: Fri, 04 Jan 2013 06:14:34 +0100
Message-ID: <50E6653A.4060900@synedra.com> (raw)
In-Reply-To: <CAEYLb_VDRSye1zieFcuKH2ZnCaht11O5DZX8-cWRs9CKs0M-cg@mail.gmail.com>
References: <50DF5920.1090109@synedra.com>
<CAEYLb_VDRSye1zieFcuKH2ZnCaht11O5DZX8-cWRs9CKs0M-cg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 12/29/2012 10:57 PM, Peter Geoghegan wrote:
> On 29 December 2012 20:57, Stefan Andreatta <s.andreatta@synedra.com> wrote:
...
> The general advice here is:
>
> 1) Increase default_statistics_target for the column.
I tried that, but to get good estimates under these circumstances, I
need to set the statistics_target so high that the whole table gets
analyzed. As this problem matters most for all of our large tables, I
would have to set default_statistics_target to something like 100000 -
that's a bit scary for production systems with tables of appr. 100GB, I
find.
> 2) If that doesn't help, consider using the following DDL:
>
> alter table foo alter column bar set ( n_distinct = 5.0);
>
Yes, that's probably best - even if it means quite some maintenance
work. I do it like that:
ALTER TABLE test_1 ALTER COLUMN clustered_random_2000k SET (n_distinct
= -0.05);
btw: Postgres will never set relative n_distinct values for anything
larger than -0.1. If I determine (or know) it to be a constant but lower
fraction, could it be a problem to explicitly set this value to between
-0.1 and 0?
To activate that setting, however, an ANALYZE has to be run. That was
not clear to me from the documentation:
ANALYZE verbose test_1;
To check column options and statistics values:
SELECT pg_class.relname AS table_name,
pg_attribute.attname AS column_name, pg_attribute.attoptions
FROM pg_attribute
JOIN pg_class ON pg_attribute.attrelid = pg_class.oid
WHERE pg_attribute.attnum > 0
AND pg_class.relname = 'test_1'
AND pg_attribute.attname = 'clustered_random_2000k';
SELECT tablename AS table_name, attname AS column_name,
null_frac, avg_width, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'test_1' and attname = 'clustered_random_2000k';
And finally, we can undo the whole thing, if necessary:
ALTER TABLE test_1 ALTER COLUMN clustered_random_2000k RESET (n_distinct);
ANALYZE VERBOSE test_1;
Regards,
Stefan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: s.andreatta@synedra.com, peter@2ndquadrant.com
Subject: Re: serious under-estimation of n_distinct for clustered distributions
In-Reply-To: <50E6653A.4060900@synedra.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