Re: count(*) and bad design was: Experiences with extensibility

> -----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