 

> -----Original Message-----
> From: Robert Treat [mailto:xzilla@users.sourceforge.net] 
> Sent: 11 October 2004 22:30
> To: Dave Page
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] PL/PgSQL for counting all rows in all tables.
> 
> How do you handle table growth that makes the reltuples value 
> out of whack since the last analyze?  Seems unfortunate that 
> people may not realize that the numbers they are looking at 
> are incorrect but I don't see much way to avoid it. 

Right-click the table object and select 'Count' on the current versions.
Previously, iirc it showed the message 'Refresh table to count' in the
actual count field, so you did a right-click -> Refresh.

> Seems new tables would have that problem too since they would 
> default to 1000... do you analyze after table creation?

No. 1000 is below the default threshold, so it'll get counted. If you
lower the threshold, it will say 'not counted' in the actual count
field.

Regards, Dave.

Tue, 12 Oct 2004 08:22:30 +0100
"Dave Page" <dpage@vale-housing.co.uk>
Re: PL/PgSQL for counting all rows in all tables.
On Tuesday 12 October 2004 03:22, Dave Page wrote:
> > -----Original Message-----
> > From: Robert Treat [mailto:xzilla@users.sourceforge.net]
> > Sent: 11 October 2004 22:30
> > To: Dave Page
> > Cc: pgsql-hackers@postgresql.org
> > Subject: Re: [HACKERS] PL/PgSQL for counting all rows in all tables.
> >
> > How do you handle table growth that makes the reltuples value
> > out of whack since the last analyze?  Seems unfortunate that
> > people may not realize that the numbers they are looking at
> > are incorrect but I don't see much way to avoid it.
>
> Right-click the table object and select 'Count' on the current versions.
> Previously, iirc it showed the message 'Refresh table to count' in the
> actual count field, so you did a right-click -> Refresh.
>

Maybe I didn't phrase that quite right. How would a user know that he needs to 
do a real count?  For example, if I have a table with est 1 million rows, and 
I load another 1 million rows into it, wont pgadmin show me 1 million rows 
until I run an analyze? Even if I run a manual count, wont it show 1 million 
next time I come into the application, and that time I may not realize that 
the table is off by 1 million rows so I take the estimated count at face 
value.  

BTW The reason I'm asking about this is we're trying to come up with a good 
scheme for phppgadmin to show estimated counts without showing incorrect 
numbers to users... or at least giving them a clue that the numbers might be 
really off. 

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Tue, 12 Oct 2004 16:21:00 -0400
Robert Treat <xzilla@users.sourceforge.net>
Re: PL/PgSQL for counting all rows in all tables.
> BTW The reason I'm asking about this is we're trying to come up with a good 
> scheme for phppgadmin to show estimated counts without showing incorrect 
> numbers to users... or at least giving them a clue that the numbers might be 
> really off. 

The vacuum daemon should know how far off the count might be. You can
display 100 +- 15%, where the 100 is collected by ANALYZE and the 15% is
determined based on the vacuum daemon estimation.

Of course, AVD needs to be implemented and the interface created (a
pg_count view?)...


Tue, 12 Oct 2004 16:33:22 -0400
Rod Taylor <pg@rbt.ca>
Re: PL/PgSQL for counting all rows in all tables.
Robert Treat wrote:

>>
>>Right-click the table object and select 'Count' on the current versions.
>>Previously, iirc it showed the message 'Refresh table to count' in the
>>actual count field, so you did a right-click -> Refresh.
>>
> 
> 
> Maybe I didn't phrase that quite right. How would a user know that he needs to 
> do a real count?  For example, if I have a table with est 1 million rows, and 
> I load another 1 million rows into it, wont pgadmin show me 1 million rows 
> until I run an analyze? Even if I run a manual count, wont it show 1 million 
> next time I come into the application, and that time I may not realize that 
> the table is off by 1 million rows so I take the estimated count at face 
> value.  

pgAdmin will either show the true row count from a SELECT COUNT(*), or 
"not counted". The last rowcount from vacuum is named "estimated row 
count", so it's clearly distinguishable which number is counted and 
which is estimated.

Regards,
Andreas



Tue, 12 Oct 2004 20:48:36 +0000
Andreas Pflug <pgadmin@pse-consulting.de>
Re: PL/PgSQL for counting all rows in all tables.