> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Ivan Sergio Borgonovo
> Sent: Wednesday, January 09, 2008 1:30 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] count(*) and bad design was: Experiences with
> extensibility
>
> On Wed, 09 Jan 2008 00:06:45 -0800
> "Joshua D. Drake" <jd@commandprompt.com> wrote:
>
> > Granted there are scenarios where others are FASTER (SELECT
> > COUNT(*)) but I find that if you are doing those items, you
> > normally have a weird design anyway.
>
> > Sincerely,
>
> Sincerely, would you make an example of such a bad design?
A program that estimates cardinality by doing SELECT COUNT(*) is a bad
design. Assuming you have the wherewithal to vacuum your tables (or
have autovacuum enabled) a query against the system tables will be a
much better estimate of cardinality.
Now (some may argue) what if we want an _EXACT_ value for COUNT(*)? We
had better ask ourselves (in that circumstance) "Am I willing to lock
the entire table and scan it?" because that is what will be necessary to
get a truly exact value. Otherwise, you can get totals that are wildly
off-base if someone is doing a bulk import or deleting a large number of
records.
So:
SELECT reltuples FROM pg_class WHERE relname = <table_name>;
Is more often what is really wanted.
> Or did you just mean that count(*) is bad design in postgresql since
> there are usually better alternatives in postgresql?
If you are using COUNT(*) as an existence test, then substitute:
WHERE EXISTS(<criteria>)
Use the indexes (if possible) by WHERE clause restriction:
SELECT count(1) FROM <table_name> WHERE <condition_list>
Will use indexes if appropriate.
> I'm not joking. I'd like to learn.
I think this should be a FAQ because it is a (F)requently (A)sked
(Q)uestion.
IMO-YMMV.
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 6: explain analyze is your friend