This is exactly right. The table I'm searching has 220,000 records right
now, and growing. The "Rock" category within "CD's" will return over
53,000 results. The count isn't so much for display, as for knowing
whether or not to put a "next" button on the page. I don't want to
always have next buttons, but not always have more results. There is
currently the option to browse through results based on the first letter
of the result, which I guess I could make as the 3rd step and not run
the query that currently returns 53,000 results until they narrow it
down more. I wasn't really expecting this to be possible, but PG does so
many other things that I didn't think was possible, so I thought I'd ask :)
Joe
Mitch Vincent wrote:
>
> > I gather the reason you don't actually run the whole query is because the
> > resultset would be too large? But you still want the database to work out
> > exactly how many there are.
>
> Not really because it's too large but because there isn't a need.. Imagine
> your favorite search engine, you search and the results are displayed "X
> Matches, displaying matches 1 to 10"... Same thing here -- at least that's
> what my need was... The query executed could get really stout (there were
> over 60 searchable fields across a variety of tables with hundreds of
> thousands of records in some) so executing the count() query, then the other
> certainly added the overhead... A cursor wasn't usable in this situation
> because the user could chose to search once, having only ten results
> displayed of 10,000 and leave the page (thus leaving me with an open
> cursor).....
>
> But again, I see no solution to the above problem and understandably so, if
> you LIMIT a result set, you *limit* it -- asking it to contradict itself
> doesn't make any sense so I never complained that it wasn't possible :-)
>
> -Mitch
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org