multiple - select distinct on postgresql

Solución para acelerar una consulta lenta SELECT DISTINCT en Postgres

Question

La consulta es básicamente:

SELECT DISTINCT "my_table"."foo" from "my_table" WHERE...

Pretendiendo que estoy 100% seguro de que la parte DISTINCT de la consulta es la razón por la que se ejecuta lentamente, omití el resto de la consulta para evitar confusiones, ya que es la lentitud de la parte distinta lo que me preocupa principalmente Siempre es una fuente de lentitud).

La tabla en cuestión tiene 2,5 millones de filas de datos. La DISTINCT es necesaria para los fines que no se enumeran aquí (porque no quiero volver una consulta modificada, sino información general sobre cómo hacer que las consultas distintas se ejecuten más rápido en el nivel de DBMS , si es posible).

¿Cómo puedo hacer que DISTINCT ejecute más rápido (usando Postgres 9, específicamente) sin alterar el SQL (es decir, no puedo alterar el ingreso de este SQL, pero tengo acceso para optimizar algo en el nivel de DB)?

Answer 1

A menudo, puede hacer que esas consultas se ejecuten más rápido al trabajar en forma distinct utilizando un group by :

select my_table.foo
from my_table
where [whatever where conditions you want]
group by foo;

Answer 2

Puede intentar aumentar la configuración de work_mem, dependiendo del tamaño de su conjunto de datos. Puede provocar que el cambio del plan de consulta a agregados hash, que generalmente son más rápidos.

Pero antes de configurarlo demasiado alto a nivel mundial, primero lea sobre él. Puede explotar fácilmente su servidor, ya que la configuración de max_connections actúa como un multiplicador para este número.

Esto significa que si configuraba work_mem = 128MB y configuraba max_connections = 100 (el valor predeterminado), debería tener más de 12.8GB de RAM. Básicamente, le está diciendo al servidor que puede usar mucho para realizar consultas (ni siquiera está considerando ningún otro uso de memoria por parte de Postgres o de otro tipo).

Answer 3

Su DISTINCT está causando que ordene las filas de salida para encontrar duplicados. Si coloca un índice en la (s) columna (s) seleccionada (s) por la consulta, la base de datos podrá leerlos en orden de índice y guardar el paso de clasificación. Mucho dependerá de los detalles de la consulta y de las tablas involucradas; lo que usted dice es que "sabe que el problema está en la DISTINCIÓN" realmente limita el alcance de las respuestas disponibles.