From: PFC <lists@boutiquenumerique.com>
To: Greg Stark <gsstark@mit.edu>
To: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: Andrei Bintintan <klodoma@ar-sd.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: [SQL] OFFSET impact on Performance???
Date: Wed, 26 Jan 2005 13:58:18 +0100
Message-ID: <opsk7nngxwth1vuj@musicbox> (raw)
In-Reply-To: <87brbiah0f.fsf@stark.xeocode.com>
References: <6EE64EF3AB31D5448D0007DD34EEB3412A75CB@Herge.rcsinc.local>
<87brbiah0f.fsf@stark.xeocode.com>
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.
If you're worried that it might take up too much space : store an integer
array of result_id instead of just a result_id ; this way you insert fewer
rows and save on disk space. Generate it with a custom aggregate... then
just grab a row from this table, it contains all the id's of the rows to
display.
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: lists@boutiquenumerique.com, gsstark@mit.edu, merlin.moncure@rcsonline.com, klodoma@ar-sd.net
Subject: Re: [SQL] OFFSET impact on Performance???
In-Reply-To: <opsk7nngxwth1vuj@musicbox>
* 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