From: | Guido Neitzer <lists(at)event-s(dot)net> |
---|---|
To: | "(dot)ep" <erick(dot)papa(at)gmail(dot)com>, Postgresql General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Database Select Slow |
Date: | 2007-08-10 15:45:16 |
Message-ID: | 5EB74578-FD29-4017-AED6-4A99450A4B13@event-s.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Smith | 2007-08-10 15:48:28 | Re: UPDATES hang every 5 minutes |
Previous Message | Alan J Batsford | 2007-08-10 15:34:22 | Problem Designing Index |