> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Ivan Sergio Borgonovo > Sent: Wednesday, January 09, 2008 1:30 AM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: count(*) and bad design was: Experiences with > extensibility > > On Wed, 09 Jan 2008 00:06:45 -0800 > "Joshua D. Drake" <jd@xxxxxxxxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
