Re: Yet Another COUNT(*)...WHERE...question

Phoenix Kiula wrote:
>>>
>>> SELECT COUNT(*) FROM TABLE WHERE ....(conditions)...
>
> I am not sure what the advice here is. The WHERE condition comes from
> the indices. So if the query was not "COUNT(*)" but just a couple of
> columns, the query executes in less than a second. Just that COUNT(*)
> becomes horribly slow.

The count(*) shouldn't slow things down compared to running the query to
fetch columns. It should be at least as fast, or faster if the columns
you fetch are large.
1. Do you have an example?
2. You're not running a query to get the columns, then a separate
count(*) to get a rowcount are you?

> And since the file system based query caching
> feature of PG is unclear to me (I am just moving from MySQL where the
> cache is quite powerful) I don't quite know what to do to speed up
> these queries!

There isn't a "file system based query caching" feature, there's your
operating-systems file-cache and PG's buffers. Neither of which cache
query-results, but cache disk pages instead.

--
Richard Huxton
Archonet Ltd