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

On 15/08/07, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> 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.

Sorry I was not clear. Imagine an Amazon.com search results page. It
has about 15 results on Page 1, then it shows "Page 1 of 190".

To show each page, the query probably has a "LIMIT 15 OFFSET 0" for
Page 1. However, to calculate the total number of pages, they probably
do a separate counts query, because doing a "select *" and then
counting the number of rows returned would be even more inefficient
than a count(*).

So, in reporting, two queries are fairly common I would think, unless
I am missing something?