Re: Database Select Slow

On 10.08.2007, at 06:58, .ep wrote:

> Hi, what if I need to do a count with a WHERE condition? E.g.,
>
> SELECT count(*) from customers where cust_id = 'georgebush' and
> created_on > current_date - interval '1 week' ;
>
> Can I get the info about this from somewhere in the pg system tables
> as well? Queries like these are very common in most applications, so
> I'm hoping I can avoid the sequential scans!

If you have a qualified count(*) it goes to the index first, than
checks whether the rows are live for your transaction. The problem is
only the unqualified count with

select count(*) from table_name;

without any qualification. Or, of course, if your qualifier is not
selective enough and you get a couple of millions rows back from a
slow IO system ...

I try to do counts only if I know that the selectivity is good enough
not to kill the performance. Or I use "pleas wait" pages in the my
application to tell the user, that his request is being processed and
not hung.

cug