Skip site navigation (1) Skip section navigation (2)

Select count(*) takes a long time

From: "Jeff Johnson" <jeff(at)jeffjohnson(dot)net>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-interfaces(at)postgresql(dot)org>
Subject: Select count(*) takes a long time
Date: 2001-08-09 13:51:42
Message-ID: B9C9130B5D27D4119D5D00A0C9D3A987109583@SERVER (view raw or flat)
Thread:
Lists: pgsql-interfaces
Hello,

Forgive me if this is the wrong forum for such a question.

What can I do to make "select count(*) from ..." return quickly?  I
think I had read that PostgreSQL, unlike most RDBMSs, does not store
the current row count and so must be re-calculated on the fly.  In my
particular situation I have a home page that must select a "featured"
article by choosing one at random from a table of 300 thousand or so.
Doing select count(*) takes a few seconds.  I could probably select
all the rows and get the data in the same time.

I thought I came up with a good solution, now I "select ... from ...
order by random() limit 1", which is nice because it only requires one
query to get what I want but it's still slow.

I now cache the "featured" article because this query is too slow to
make during a web page request.  I have indexes on all the columns
referenced by the where clause.  Am I missing something obvious or
does everyone just deal with slow "select count(*)" when using
PostgreSQL?

Thanks,
Jeff

PS: I'm running PostgreSQL 7.1.2 on FreeBSD 4.3.


Responses

pgsql-interfaces by date

Next:From: Tom LaneDate: 2001-08-09 16:36:15
Subject: Re: Select count(*) takes a long time
Previous:From: Lee KindnessDate: 2001-08-09 08:13:35
Subject: Argh (was about an ECPG bug)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group