Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group