pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.





view thread (50+ messages)  latest in thread

Message-ID: <opsk7nngxwth1vuj@musicbox>
Permalink:  ../opsk7nngxwth1vuj@musicbox/
Also on:    postgresql.org/message-id/opsk7nngxwth1vuj@musicbox

 · 

reply

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