Greetings all!
On Tue, 14 May 2002, Neil Conway wrote:
> On Tue, May 14, 2002 at 09:18:43PM -0400, Doug Fields wrote:
> > Perhaps you can suggest the fastest way of getting a table count, if it is
> > not
> >
> > SELECT COUNT(*) FROM x WHERE ...;
>
> Well, if you have a qualification (a WHERE ... clause), then count()
> can be fast: it depends on the number of rows that match the
> qualification. I can't see an obvious way to optimize count() on a
> large subset of a table.
>
> If you don't have a qualification (i.e. SELECT count(*) FROM x), there
> are a couple ways to do it: use triggers to increment/decrement a
> counter of the number of rows in the table, create a btree-indexed
> SERIAL column and do an ORDER BY serial_column DESC LIMIT 1 on it (note
> that this is fragile, your sequence could easily have holes), or if you
> only need an approximation, you could use the pg_class attribute
> "reltuples" for the OID of your table. My impression is that most
> people choose the first method.
Unfortunately, for any database that is being used in a drilldown manner
(ie, allowing the almost arbitrary addition of constraints to select
subsets) (note: this is in fact quite a common configuration for data
mining systems!!) then the use of triggers is almost a no show. This
basically requires that you know _what_ queries will be run (and that you
must rewrite said queries to use the special table).
In addition, even if you have a limited set of queries, each trigger will
slow down inserts, which potentially could be to the point of being slower
than data arrival rates (note that I have a system where this could be the
case very soon, in which event, Postgresql is unfortunately going to have
to be replaced by something else).
Is there any timeline in which these problems will be fixed/alleviated, or
should I be looking at alternatives now?
Regards,
Ed