Re: Select max(foo) and select count(*) optimization

From: Mark Kirkwood <markir(at)paradise(dot)net(dot)nz>
To: "D'Arcy J(dot)M(dot) Cain" <darcy(at)druid(dot)net>
Cc: Shridhar Daithankar <shridhar_daithankar(at)persistent(dot)co(dot)in>, Postgresql Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Select max(foo) and select count(*) optimization
Date: 2004-01-06 19:28:45
Message-ID: 3FFB0C6D.9020704@paradise.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.
>
>
>

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Michael Shapiro 2004-01-06 20:49:16 PgAdmin startup query VERY slow
Previous Message Tom Lane 2004-01-06 18:36:44 Re: Inefficient SELECT with OFFSET and LIMIT