database - pgsql - time between postgres

Mejora del rendimiento de OFFSET en PostgreSQL

Question

Tengo una tabla en la que estoy haciendo un ORDER BY antes de un LIMIT y OFFSET para paginar.

Agregar un índice en la columna ORDER BY hace una gran diferencia en el rendimiento (cuando se usa en combinación con un LIMIT pequeño). En una tabla de 500,000 filas, vi una mejora de 10,000x al agregar el índice, siempre y cuando hubiera un pequeño LIMIT.

Sin embargo, el índice no tiene impacto para OFFSET altos (es decir, páginas posteriores en mi paginación). Esto es comprensible: un índice b-tree hace que sea fácil iterar en orden desde el principio, pero no para encontrar el enésimo elemento.

Parece que lo que ayudaría es un índice b-tree contado , pero no conozco el soporte para estos en PostgreSQL. ¿Hay alguna otra solución? Parece que la optimización para OFFSET grandes (especialmente en casos de uso de paginación) no es tan inusual.

Desafortunadamente, el manual de PostgreSQL simplemente dice "Las filas omitidas por una cláusula OFFSET todavía tienen que calcularse dentro del servidor, por lo tanto, un OFFSET grande puede ser ineficiente".

Answer 1

No sé nada sobre los "índices b-tree contados", pero una cosa que hemos hecho en nuestra aplicación para ayudar con esto es dividir nuestras consultas en dos, posiblemente usando una subconsulta. Mis disculpas por perder el tiempo si ya estás haciendo esto.

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

La ventaja aquí es que, aunque todavía tiene que calcular el orden correcto de todo, no ordena toda la fila, solo la columna de id .

Answer 2

Parece que la optimización para OFFSET grandes (especialmente en casos de uso de paginación) no es tan inusual.

Me parece un poco inusual La mayoría de las personas, la mayoría de las veces, no parecen hojear muchas páginas. Es algo que apoyaría, pero no trabajaría duro para optimizar.

Pero de todos modos . . .

Dado que el código de su aplicación sabe qué valores ordenados ya se han visto, debería poder reducir el conjunto de resultados y reducir el desplazamiento al excluir esos valores en la cláusula WHERE. Suponiendo que pide una sola columna, y está ordenada en orden ascendente, su código de aplicación puede almacenar el último valor en la página, luego agregue AND your-ordered-column-name > last-value-seen a la cláusula WHERE de alguna manera apropiada.

Answer 3

Es posible que desee un índice calculado.

Vamos a crear una tabla:

create table sales(day date, amount real);

Y llenarlo con algunas cosas al azar:

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

Indíquelo por día, nada especial aquí:

create index sales_by_day on sales(day);

Crea una función de posición de fila. Hay otros enfoques, este es el más simple:

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

Compruebe si funciona (no lo llame así en grandes conjuntos de datos):

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
..................

Ahora la parte complicada: agregue otro índice calculado en los valores de la función sales_pos:

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

Aquí es cómo lo usa. 5 es su "compensación", 10 es el "límite":

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
...............

Es rápido, porque cuando lo llamas así, Postgres usa valores precalculados del índice:

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))

Espero eso ayude.

Answer 4

Hace poco trabajé en un problema como este y escribí un blog sobre cómo enfrentar ese problema. es muy parecido, espero ser útil para cualquiera. utilizo el enfoque de lista diferida con la adquisición parcial. Reemplacé el límite y el desplazamiento o la paginación de la consulta a una paginación manual. En mi ejemplo, el select devuelve 10 millones de registros, los obtengo y los inserto en una "tabla temporal":

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;

después de eso, puedo paginar sin contar cada fila pero usando la secuencia asignada:

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

Desde la perspectiva de Java, implementé esta paginación a través de una adquisición parcial con una lista perezosa. esto es, una lista que se extiende desde la lista Resumen e implementa el método get (). El método get puede usar una interfaz de acceso a datos para continuar obteniendo el siguiente conjunto de datos y liberar el montón de memoria:

@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>
}

por otro lado, la interfaz de acceso a datos utiliza la consulta para paginar e implementa un método para iterar de forma progresiva, cada 25000 registros para completarlo todo.

los resultados de este enfoque se pueden ver aquí http://www.arquitecturaysoftware.co/2013/10/laboratorio-1-iterar-millones-de.html