Re: Paged Query

On Mon, Jul 9, 2012 at 8:16 AM, Craig James <cjames(at)emolecules(dot)com> wrote:

>
> A good solution to this general problem is "hitlists." I wrote about this
> concept before:
>
> http://archives.postgresql.org/pgsql-performance/2010-05/msg00058.php
>
>
I implemented this exact strategy in our product years ago. Our queries
were once quite complicated involving many nested sub-SELECT's and several
JOIN's per SELECT. The basics of our implementation now consists of

1. A table tracking all "cache" tables. A cache table is a permanent
table once represented as one of the former sub-SELECT's. The table
includes the MD5 hash of the query used to create the table, time created,
query type (helps to determine expire time), and a comment field to help in
debugging.
2. Simple logic checking for the existence of the cache table and creating
it if it does not.
3. Using one or many of the named cache tables in the final query using
ORDER BY-LIMIT-OFFSET in a CURSOR.
4. One scheduled backend process to clear the "expired" cache tables based
on the query type.

Reason for the CURSOR is to execute once to get a tally of records for
pagination purposes then rewind and fetch the right "page".

Highly recommended.

-Greg