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

On 15/08/07, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
> "Phoenix Kiula" <phoenix(dot)kiula(at)gmail(dot)com> writes:
>
> > I'm grappling with a lot of reporting code for our app that relies on
> > queries such as:
> >
> > SELECT COUNT(*) FROM TABLE WHERE ....(conditions)...
> >...
> > The number of such possibilities for multiple WHERE conditions is
> > infinite...
>
> Depends on the "conditions" bit. You can't solve all of the infinite
> possibilities -- well you can, just run the query above -- but if you want > to do better it's all about understanding your data.

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. 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!