Re: COUNT and Performance ...

>
>
>But pgstattuple does do a sequential scan of the table.  You avoid a lot
>of the executor's tuple-pushing and plan-node-traversing machinery that
>way, but the I/O requirement is going to be exactly the same.
>  
>

I have tried it more often so that I can be sure that everything is in 
the cache.
I thought it did some sort of "stat" on tables. Too bad :(.


>>If people want to count ALL rows of a table. The contrib stuff is pretty 
>>useful. It seems to be transaction safe.
>>    
>>
>
>Not entirely.  pgstattuple uses HeapTupleSatisfiesNow(), which means you
>get a count of tuples that are committed good in terms of the effects of
>transactions committed up to the instant each tuple is examined.  This
>is in general different from what count(*) would tell you, because it
>ignores snapshotting.  It'd be quite unrepeatable too, in the face of
>active concurrent changes --- it's very possible for pgstattuple to
>count a single row twice or not at all, if it's being concurrently
>updated and the other transaction commits between the times pgstattuple
>sees the old and new versions of the row.
>  
>
Interesting. I have tried it with concurrent sessions and transactions - 
the results seemed to be right (I could not see the records inserted by 
open transactions). Too bad :(. It would have been a nice work around.


>>The performance boost is great (PostgreSQL 7.3, RedHat, 166Mhz
>>
>
>I think your test case is small enough that the whole table is resident
>in memory, so this measurement only accounts for CPU time per tuple and
>not any I/O.  Given the small size of pgstattuple's per-tuple loop, the
>speed differential is not too surprising --- but it won't scale up to
>larger tables.
>
>Sometime it would be interesting to profile count(*) on large tables
>and see exactly where the CPU time goes.  It might be possible to shave
>off some of the executor overhead ...
>
>            regards, tom lane
>  
>

I have tried it with the largest table on my testing system.
Reducing the overhead is great :).
   Thanks a lot,
       Hans

-- 
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at <http://www.postgresql.at>, cluster.postgresql.at 
<http://cluster.postgresql.at>, www.cybertec.at 
<http://www.cybertec.at>, kernel.cybertec.at <http://kernel.cybertec.at>