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