pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFaster count(*)?
2+ messages / 2 participants
[nested] [flat]
* Faster count(*)?
@ 2005-08-09 22:39 Owen Jacobson <ojacobson@osl.com>
0 siblings, 1 reply; 2+ messages in thread
From: Owen Jacobson @ 2005-08-09 22:39 UTC (permalink / raw)
To: pgsql-sql
Salve.
I understand from various web searches and so on that PostgreSQL's MVCC
mechanism makes it very hard to use indices or table metadata to optimise
count(*). Is there a better way to guess the "approximate size" of a table?
I'm trying to write a trigger that fires on insert and performs some
maintenance (collapsing overlapping boxes into a single large box,
specifically) as the table grows. My initial attempt involved count(*) and,
as the number of pages in the table grew, that trigger bogged down the
database.
Any thoughts?
-O
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Faster count(*)?
@ 2005-08-10 14:04 Richard Huxton <dev@archonet.com>
parent: Owen Jacobson <ojacobson@osl.com>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Huxton @ 2005-08-10 14:04 UTC (permalink / raw)
To: Owen Jacobson <ojacobson@osl.com>; +Cc: pgsql-sql
Owen Jacobson wrote:
> Salve.
>
> I understand from various web searches and so on that PostgreSQL's MVCC
> mechanism makes it very hard to use indices or table metadata to optimise
> count(*). Is there a better way to guess the "approximate size" of a table?
Plenty of good answers on how to estimate table-size, but it sounds like
you just want to run your maintenance function "every so often".
1. Create a sequence "my_table_tracker_seq"
2. On insert, call nextval(my_table_tracker_seq)
3. If value modulo 1000 = 0, run the maintenance routine
That's about as fast as you can get, and might meet your needs. Of
course you'll need to be more complex if you insert multiple rows at a time.
If you do a lot of deletion on the table and want to take that into
account, have a second sequence you increment on deletion and subtract
the one from the other.
Not always accurate enough, but it is quick.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-08-10 14:04 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-09 22:39 Faster count(*)? Owen Jacobson <ojacobson@osl.com>
2005-08-10 14:04 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox