X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 4817AD1B438 for ; Tue, 6 Jan 2004 19:29:19 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 53128-06 for ; Tue, 6 Jan 2004 15:28:30 -0400 (AST) Received: from linda-2.paradise.net.nz (bm-2a.paradise.net.nz [202.0.58.21]) by svr1.postgresql.org (Postfix) with ESMTP id F2FCAD1B4D4 for ; Tue, 6 Jan 2004 15:28:14 -0400 (AST) Received: from smtp-3.paradise.net.nz (smtp-3a.paradise.net.nz [202.0.32.196]) by linda-2.paradise.net.nz (Paradise.net.nz) with ESMTP id <0HR300A9022WT0@linda-2.paradise.net.nz> for pgsql-performance@postgresql.org; Wed, 07 Jan 2004 08:28:09 +1300 (NZDT) Received: from paradise.net.nz (218-101-13-98.paradise.net.nz [218.101.13.98]) by smtp-3.paradise.net.nz (Postfix) with ESMTP id D2943ADF4A; Wed, 07 Jan 2004 08:28:07 +1300 (NZDT) Date: Wed, 07 Jan 2004 08:28:45 +1300 From: Mark Kirkwood Subject: Re: Select max(foo) and select count(*) optimization In-reply-to: <200401060718.08444.darcy@druid.net> To: "D'Arcy J.M. Cain" Cc: Shridhar Daithankar , Postgresql Performance Message-id: <3FFB0C6D.9020704@paradise.net.nz> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7bit X-Accept-Language: en-us, en User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.5) Gecko/20031213 References: <1073332325.8958.8.camel@jester> <200401061212.21344.shridhar_daithankar@persistent.co.in> <200401060718.08444.darcy@druid.net> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/71 X-Sequence-Number: 5246 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. > > >