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

Поиск
Список
Период
Сортировка
От PFC
Тема Re: [SQL] OFFSET impact on Performance???
Дата
Msg-id opsk7nngxwth1vuj@musicbox
обсуждение исходный текст
Ответ на Re: [SQL] OFFSET impact on Performance???  (Greg Stark <gsstark@mit.edu>)
Ответы Re: [SQL] OFFSET impact on Performance???
Re: [SQL] OFFSET impact on Performance???
Список 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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: PostgreSQL clustering VS MySQL clustering
Следующее
От: "Van Ingen, Lane"
Дата:
Сообщение: SQL Performance Guidelines