Улучшение производительности OFFSET в PostgreSQL

Ответ 1

Вам может понадобиться вычисленный индекс.

Создайте таблицу:

create table sales(day date, amount real);

И залейте его каким-то случайным материалом:

insert into sales 
    select current_date + s.a as day, random()*100 as amount
    from generate_series(1,20);

Индексируйте его по дням, здесь ничего особенного:

create index sales_by_day on sales(day);

Создать функцию позиции строки. Существуют и другие подходы, один из них самый простой:

create or replace function sales_pos (date) returns bigint 
   as 'select count(day) from sales where day <= $1;' 
   language sql immutable;

Проверьте, работает ли это (не называйте это на больших наборах данных):

select sales_pos(day), day, amount from sales;

     sales_pos |    day     |  amount  
    -----------+------------+----------
             1 | 2011-07-08 |  41.6135
             2 | 2011-07-09 |  19.0663
             3 | 2011-07-10 |  12.3715
    ..................

Теперь сложная часть: добавьте еще один индекс, вычисляемый по значениям функции sales_pos:

create index sales_by_pos on sales using btree(sales_pos(day));

Вот как вы его используете. 5 - ваше "смещение", 10 - "предел":

select * from sales where sales_pos(day) >= 5 and sales_pos(day) < 5+10;

        day     | amount  
    ------------+---------
     2011-07-12 | 94.3042
     2011-07-13 | 12.9532
     2011-07-14 | 74.7261
    ...............

Это быстро, потому что, когда вы вызываете его так, Postgres использует предварительно рассчитанные значения из индекса:

explain select * from sales 
  where sales_pos(day) >= 5 and sales_pos(day) < 5+10;

                                    QUERY PLAN                                
    --------------------------------------------------------------------------
     Index Scan using sales_by_pos on sales  (cost=0.50..8.77 rows=1 width=8)
       Index Cond: ((sales_pos(day) >= 5) AND (sales_pos(day) < 15))

Надеюсь, что это поможет.

Ответ 2

Кажется, что оптимизация для больших СМЕЩЕНИЯ (особенно в разбивке на страницы прецеденты) не является чем-то необычным.

Мне кажется немного необычным. Большинство людей, в большинстве случаев, похоже, не просматривают очень много страниц. Это то, что я бы поддержал, но не буду работать над оптимизацией.

Но все равно.,.

Поскольку ваш код приложения знает, какие заказные значения он уже видел, он должен иметь возможность уменьшить набор результатов и уменьшить смещение, исключив эти значения в предложение WHERE. Предполагая, что вы заказываете один столбец и сортируете по возрастанию, ваш код приложения может сохранить последнее значение на странице, а затем добавить AND your-ordered-column-name > last-value-seen в предложение WHERE некоторым подходящим способом.

Ответ 3

В последнее время я работал над проблемой, подобной этой, и я написал блог о том, как сталкивается с этой проблемой. очень нравится, я надеюсь быть полезным для любого. Я использую метод ленивого списка с частичным приложением. я Заменен лимит и смещение или разбиение на страницы запроса на ручную разбивку на страницы. В моем примере выбор возвращает 10 миллионов записей, я получаю их и вставляю их в "временную таблицу":

create or replace function load_records ()
returns VOID as $$
BEGIN
drop sequence if exists temp_seq;
create temp sequence temp_seq;
insert into tmp_table
SELECT linea.*
FROM
(
select nextval('temp_seq') as ROWNUM,* from table1 t1
 join table2 t2 on (t2.fieldpk = t1.fieldpk)
 join table3 t3 on (t3.fieldpk = t2.fieldpk)
) linea;
END;
$$ language plpgsql;

после этого я могу разбивать страницы без подсчета каждой строки, но используя назначенную последовательность:

select * from tmp_table where counterrow >= 9000000 and counterrow <= 9025000

С точки зрения java, я реализовал эту разбивку по страницам через частичное объявление с ленивым списком. это список, который простирается от абстрактного списка и реализует метод get(). Метод get может использовать интерфейс доступа к данным, чтобы продолжить получать следующий набор данных и освобождать кучу памяти:

@Override
public E get(int index) {
  if (bufferParcial.size() <= (index - lastIndexRoulette))
  {
    lastIndexRoulette = index;
    bufferParcial.removeAll(bufferParcial);
    bufferParcial = new ArrayList<E>();
        bufferParcial.addAll(daoInterface.getBufferParcial());
    if (bufferParcial.isEmpty())
    {
        return null;
    }

  }
  return bufferParcial.get(index - lastIndexRoulette);<br>
}

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

результаты этого подхода можно увидеть здесь http://www.arquitecturaysoftware.co/2013/10/laboratorio-1-iterar-millones-de.html

Ответ 4

Я ничего не знаю о "подсчитанных индексах b-дерева", но одна вещь, которую мы сделали в нашем приложении, чтобы помочь с этим, разбить наши запросы на две части, возможно, используя подзапрос. Приносим извинения за то, что вы тратите свое время, если вы уже это делаете.

SELECT *
FROM massive_table
WHERE id IN (
    SELECT id
    FROM massive_table
    WHERE ...
    LIMIT 50
    OFFSET 500000
);

Преимущество в том, что, хотя ему еще нужно рассчитать правильное упорядочение всего, он не упорядочивает всю строку - только столбец id.