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

On 8/15/07, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
> 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.

Sorry, but I don't believe you. if you're doing a count(*) on the
same dataset that returns in < 1 second, then the count(*) with the
same where clause will run in < 1 second. I haven't seen pgsql do
anything else.

> And since the file system based query caching
> feature of PG is unclear to me

There is no "query caching" in pgsql. There is data caching. Each
query has to get planned and executed though (unless prepared, then
just executed)

> (I am just moving from MySQL where the
> cache is quite powerful)

As long as nothing is changing behind the query, and invalidating the
query cache. It is useful for reporting apps, but in a constantly
updating db pretty much useless.

> I don't quite know what to do to speed up
> these queries!

Post them with explain analyze output. i.e.

explain analyze yourqueryhere

cut and past the query and the output. as well as the table schema.