I have tried it more often so that I can be sure that everything is in the cache.
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 thought it did some sort of "stat" on tables. Too bad :(.
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.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.
I have tried it with the largest table on my testing system.I think your test case is small enough that the whole table is residentThe performance boost is great (PostgreSQL 7.3, RedHat, 166Mhz
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
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>
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
