From: Mark Kirkwood <markir@paradise.net.nz>
To: D'Arcy J.M. Cain <darcy@druid.net>
Cc: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Select max(foo) and select count(*) optimization
Date: Wed, 07 Jan 2004 08:28:45 +1300
Message-ID: <3FFB0C6D.9020704@paradise.net.nz> (raw)
In-Reply-To: <200401060718.08444.darcy@druid.net>
References: <BC1F1F20.71370%siracusa@mindspring.com>
<1073332325.8958.8.camel@jester>
<200401061212.21344.shridhar_daithankar@persistent.co.in>
<200401060718.08444.darcy@druid.net>
if this situation persists after 'analyze certificate', then you need to:
increase the statistics target 'alter table certificate alter column
certificate_id set statistics 100'
or
'vacuum full certificate'
i.e : there are lots of (dead) updated or deleted tuples in the
relation, distributed in such a way as to throw off analyze's estimate.
regards
Mark
D'Arcy J.M. Cain wrote:
>
>Well, I did this:
>
>cert=# select relpages,reltuples from pg_class where relname= 'certificate';
> relpages | reltuples
>----------+-------------
> 399070 | 2.48587e+07
>(1 row)
>
>Casting seemed to help:
>
>cert=# select relpages,reltuples::bigint from pg_class where relname=
>'certificate';
> relpages | reltuples
>----------+-----------
> 399070 | 24858736
>(1 row)
>
>But:
>
>cert=# select count(*) from certificate;
>[*Crunch* *Crunch* *Crunch*]
> count
>----------
> 19684668
>(1 row)
>
>Am I missing something? Max certificate_id is 20569544 btw.
>
>
>
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: markir@paradise.net.nz, darcy@druid.net, shridhar_daithankar@persistent.co.in
Subject: Re: Select max(foo) and select count(*) optimization
In-Reply-To: <3FFB0C6D.9020704@paradise.net.nz>
* 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