pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.
>
>  
>




view thread (19+ messages)  latest in thread

Message-ID: <3FFB0C6D.9020704@paradise.net.nz>
Permalink:  ../3FFB0C6D.9020704@paradise.net.nz/
Also on:    postgresql.org/message-id/3FFB0C6D.9020704@paradise.net.nz

 · 

reply

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