Skip site navigation (1) Skip section navigation (2)

Re: [SQL] OFFSET impact on Performance???

From: PFC <lists(at)boutiquenumerique(dot)com>
To: "Greg Stark" <gsstark(at)mit(dot)edu>,"Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
Cc: "Andrei Bintintan" <klodoma(at)ar-sd(dot)net>,pgsql-performance(at)postgresql(dot)org
Subject: Re: [SQL] OFFSET impact on Performance???
Date: 2005-01-26 12:58:18
Message-ID: opsk7nngxwth1vuj@musicbox (view raw or flat)
Thread:
Lists: pgsql-performance
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.



In response to

Responses

pgsql-performance by date

Next:From: Steve PoeDate: 2005-01-26 13:07:28
Subject: Re: Ideal disk setup for Postgresql 7.4?
Previous:From: Leeuw van der, TimDate: 2005-01-26 10:36:35
Subject: Re: [SQL] OFFSET impact on Performance???

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group