Thread: select count(*) status these days

I'm about to launch an in house 'Aplpha' stage project. One of the marketing driven outputs on the page is filled by a:

SELECT
  COUNT(*)
WHERE
  date_field IS NOT NULL AND
  date_field > :todays_date;

Last I heard, this kind of query is kind of slow on Postgres in particular, (Love postgres otherwise). Is this still
true? 

Is there any architectural way to speed it up? I'd actually like to run it every 2-60 seconds to update a counter on a
pagefor the marketing guy, (which for once, is me :-) 

Dennis Gearon

Signature Warning
----------------
It is always a good idea to learn from your own mistakes. It is usually a better idea to learn from others’ mistakes,
soyou do not have to make them yourself. from 'http://blogs.techrepublic.com.com/security/?p=4501&tag=nl.e036' 

EARTH has a Right To Life,
  otherwise we all die.

On 19/10/10 11:15, Dennis Gearon wrote:
> I'm about to launch an in house 'Aplpha' stage project. One of the marketing driven outputs on the page is filled by
a:
>
> SELECT
>    COUNT(*)
> WHERE
>    date_field IS NOT NULL AND
>    date_field>  :todays_date;
>
> Last I heard, this kind of query is kind of slow on Postgres in particular, (Love postgres otherwise). Is this still
true?

That depends on the selectivity of your where clause, nothing else. If
date_field is indexed and pretty selective, it'll be quick.

--
Postgresql & php tutorials
http://www.designmagick.com/

Dennis Gearon wrote:
> Is there any architectural way to speed it up? I'd actually like to run it every 2-60 seconds to update a counter on
apage for the marketing guy, (which for once, is me :- 

Could always maintain it with triggers.  See
http://wiki.postgresql.org/wiki/Slow_Counting for a discussion of this
whole issue, with pointers to articles about alternate solutions.

--
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance"    Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book