Всем привет.

Такой вопрос:

Как можно ускорить операции LIMIT, OFFSET и ORDER BY для огромных (порядка 1-10 GB) таблиц при обычных SELECT'ах?

• PostgreSQL: Как ускорить операции LIMIT, OFFSET, ORDER BY?,parad, 01:52 , 01-Авг-10
  • PostgreSQL: Как ускорить операции LIMIT, OFFSET, ORDER BY?,kutsy, 08:01 , 01-Авг-10
    • PostgreSQL: Как ускорить операции LIMIT, OFFSET, ORDER BY?,parad, 17:03 , 01-Авг-10
      • PostgreSQL: Как ускорить операции LIMIT, OFFSET, ORDER BY?,kutsy, 20:23 , 01-Авг-10
        • PostgreSQL: Как ускорить операции LIMIT, OFFSET, ORDER BY?,parad, 04:40 , 02-Авг-10
          • PostgreSQL: Как ускорить операции LIMIT, OFFSET, ORDER BY?,kutsy, 07:33 , 02-Авг-10
            • PostgreSQL: Как ускорить операции LIMIT, OFFSET, ORDER BY?,Nirnroot, 06:10 , 07-Авг-10

ввести столбец с идентификатором записи, проиндексировать по нему и выгребать:
select ... where ( row_id >= [offset] )and( row_id < [offset+limit] )

пс. 10гб - это очень мало. огромным тут и не пахнет, тем более для постгри. )

>ввести столбец с идентификатором записи, проиндексировать по нему и выгребать:
>select ... where ( row_id >= [offset] )and( row_id < [offset+limit] )
>
>пс. 10гб - это очень мало. огромным тут и не пахнет, тем
>более для постгри. )

Получается, что это будет что-то типа обычного идентификатора (ID), но его нужно будет держать жестко по-порядку без прерываний. Верно?

в зависимости от требований. к примеру вполне нормальная картина работы форума( на страницу к примеру 20 тем ):

- модераторы удаляют тему с идентификатором 105. получается, что для 6-ой странице( 100-119 ) списка тем у нас не хватает 105 чтобы ровно вывести 20.
- однако лимит с офсетом выведут 100-120 без 105.
- черевато только тем, что и на 7ой странице продублируется топик с номером 120.
- что не есть страшно. зато скорость.

>в зависимости от требований. к примеру вполне нормальная картина работы форума( на
>страницу к примеру 20 тем ):
>
> - модераторы удаляют тему с идентификатором 105. получается, что для 6-ой
>странице( 100-119 ) списка тем у нас не хватает 105 чтобы
>ровно вывести 20.
> - однако лимит с офсетом выведут 100-120 без 105.
> - черевато только тем, что и на 7ой странице продублируется топик
>с номером 120.
> - что не есть страшно. зато скорость.

Ага, я понял.
Но в данном случаи как раз критично чтобы на странице было именно 20 тем (к примеру).

Я думал о том, что нужно на удаление записи вешать триггер, что будет изменять это поле, чтобы было по-порядку, начиная с удаляемой записи и до конца. Остаётся только попробовать насколько это будет быстро для таблиц подобного размера.

Или может есть более лучшее решение?

сходу креативится одно более менее решение, но оно зависит от частоты и объемов удаляемых строк. до определенных цифр( можете посчетать сами )) ) будет работать быстрее оффсета и лимита.

- сделать проинтексированный столбец-нумератор как описано выше.
- создать таблицу, в которую будут сохранятся удаляемые идентификаторы.
- написать хранимку, выполняющую следующее:

...
select count(*) into i_count from saved_deleted where some_id<=[offset]; -- счетаем сколько было удалено в оффсете.
select ... where ( row_id >= [offset] - i_count )and( row_id < [offset+limit] - i_count ); -- селектим учитывая поправку на удаленные.
...

периодически можно уплотнять счетчик по крону - черевато блокировкой таблицы на время.
если есть возможность - повторно использовать удаленные идентификаторы.

всегда можно улучшить. все зависит от... )

Спасибо.
Я понял.

Теоретически можно и не удалять строки сразу, а только помечать как удаленные.