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

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(at)sysexperts(dot)com