Re: Paged Query

From: Greg Spiegelberg <gspiegelberg(at)gmail(dot)com>
To: Craig James <cjames(at)emolecules(dot)com>
Cc: sthomas(at)optionshouse(dot)com, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>, Gregg Jaskiewicz <gryzman(at)gmail(dot)com>, Misa Simic <misa(dot)simic(at)gmail(dot)com>, Hermann Matthes <hermann(dot)matthes(at)web(dot)de>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Paged Query
Date: 2012-07-09 14:33:36
Message-ID: CAEtnbpVL_CGRnHfykt=3ihT++m_Ni=AskKTteeFJXomZHHq3QA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Jeff Janes 2012-07-09 17:02:50 Re: Create tables performance
Previous Message Craig James 2012-07-09 14:16:00 Re: Paged Query