From: Kevin Brown <kevin@sysexperts.com>
To: pgsql-performance@postgresql.org
Subject: Re: [SQL] OFFSET impact on Performance???
Date: Thu, 27 Jan 2005 19:59:39 -0800
Message-ID: <20050128035938.GA31166@filer> (raw)
In-Reply-To: <opsk7nngxwth1vuj@musicbox>
References: <6EE64EF3AB31D5448D0007DD34EEB3412A75CB@Herge.rcsinc.local>
<87brbiah0f.fsf@stark.xeocode.com>
<opsk7nngxwth1vuj@musicbox>
PFC wrote:
>
> Supposing your searches display results which are rows coming from one
> specific table, you could create a cache table :
>
> search_id serial primary key
> index_n position of this result in the global result set
> result_id id of the resulting row.
>
> Then, making a search with 50k results would INSERT INTO cache ... SELECT
> FROM search query, with a way to set the index_n column, which can be a
> temporary sequence...
>
> Then to display your pages, SELECT from your table with index_n BETWEEN so
> and so, and join to the data table.
This is a nice way of doing a fast materialized view. But it looked
to me like one of the requirements of the original poster is that the
result set being displayed has to be "current" as of the page display
time. If inserts to the original table have been committed between
the time the current page was displayed and "now", the next page view
is supposed to show them. That basically means rerunning the query
that was used to build the cache table.
But perhaps the original poster is willing to live with the idea that
new rows won't show up in the result set, as long as updates show up
(because the cache table is just a fancy index) and deletes "work"
(because the join against the data table will show only rows that are
common between both).
--
Kevin Brown kevin@sysexperts.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kevin@sysexperts.com
Subject: Re: [SQL] OFFSET impact on Performance???
In-Reply-To: <20050128035938.GA31166@filer>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox