Re: [SQL] OFFSET impact on Performance??? - Mailing list pgsql-performance

From PFC
Subject Re: [SQL] OFFSET impact on Performance???
Date
Msg-id opsk7nngxwth1vuj@musicbox
Whole thread Raw
In response to Re: [SQL] OFFSET impact on Performance???  (Greg Stark <gsstark@mit.edu>)
Responses Re: [SQL] OFFSET impact on Performance???  (Alex Turner <armtuk@gmail.com>)
Re: [SQL] OFFSET impact on Performance???  (Kevin Brown <kevin@sysexperts.com>)
List 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.



pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: PostgreSQL clustering VS MySQL clustering
Next
From: "Van Ingen, Lane"
Date:
Subject: SQL Performance Guidelines