query - postgresql select performance

Tablas temporales de PostgreSQL

Question

Necesito realizar una consulta 2,5 millones de veces. Esta consulta genera algunas filas que necesito para AVG(column) y luego usa esta AVG para filtrar la tabla de todos los valores por debajo del promedio. Entonces necesito INSERT estos resultados filtrados en una tabla.

La única forma de hacer tal cosa con una eficiencia razonable, parece ser mediante la creación de una TEMPORARY TABLE para cada hilo de python de consulta-postmaster. Solo espero que estas TEMPORARY TABLE no se conserven en el disco duro (en absoluto) y que permanezcan en la memoria (RAM), a menos que estén sin memoria de trabajo, por supuesto.

Me gustaría saber si una TABLA TEMPORAL incurrirá en escrituras en el disco (lo que interferiría con los INSERTOS, es decir, retrasaría todo el proceso)

Answer 1

Las tablas temporales proporcionan solo una garantía: se descartan al final de la sesión. Para una mesa pequeña, probablemente tenga la mayoría de sus datos en la tienda de respaldo. Para una tabla grande, garantizo que los datos se eliminarán periódicamente en el disco ya que el motor de la base de datos necesita más espacio de trabajo para otras solicitudes.

EDITAR: si necesita absolutamente tablas temporales solo de RAM, puede crear un espacio de tabla para su base de datos en un disco RAM (/ dev / shm funciona). Esto reduce la cantidad de IO de disco, pero tenga en cuenta que actualmente no es posible hacer esto sin una escritura de disco físico; el motor DB vaciará la lista de la tabla a un almacenamiento estable cuando cree la tabla temporal.

Answer 2

Tenga en cuenta que, en Postgres, el comportamiento predeterminado para las tablas temporales es que no se descartan automáticamente y los datos persisten en la confirmación. Ver ON COMMIT .

Sin embargo, la tabla temporal se descarta al final de una sesión de base de datos :

Las tablas temporales se eliminan automáticamente al final de una sesión u opcionalmente al final de la transacción actual.

Hay varias consideraciones que debes tener en cuenta:

• Si desea DROP explícitamente una tabla temporal al final de una transacción, créela con la sintaxis CREATE TEMPORARY TABLE ... ON COMMIT DROP .
• En presencia de agrupación de conexiones , una sesión de base de datos puede abarcar varias sesiones de cliente; para evitar conflictos en CREATE , debe eliminar sus tablas temporales, ya sea antes de devolver una conexión al grupo (por ejemplo, haciendo todo dentro de una transacción y usando la sintaxis de creación ON COMMIT DROP ), o según sea necesario (por precediendo cualquier instrucción CREATE TEMPORARY TABLE con una DROP TABLE IF EXISTS , que tiene la ventaja de que también funciona fuera de las transacciones, por ejemplo, si la conexión se utiliza en modo autocompromiso).
• Mientras la tabla temporal está en uso, ¿cuánto de ella cabe en la memoria antes de desbordarse en el disco? Vea la opción temp_buffers en postgresql.conf
• ¿Algo más de lo que debería preocuparme cuando trabajo a menudo con tablas temporales? Se recomienda una aspiradora después de haber eliminado las tablas temporales, para limpiar las tuplas muertas del catálogo. Postgres vacilará automáticamente cada 3 minutos más o menos cuando auto_vacuum configuración predeterminada ( auto_vacuum ).

Además, sin relación con su pregunta (pero posiblemente relacionada con su proyecto): tenga en cuenta que, si tiene que ejecutar consultas en una tabla temporal después de haberla poblado, entonces es una buena idea crear índices apropiados y emitir un ANALYZE en la tabla temporal en cuestión una vez que haya terminado de insertar en ella. De forma predeterminada, el optimizador basado en el costo supondrá que una tabla temp recientemente creada tiene ~ 1000 filas y esto puede dar como resultado un bajo rendimiento si la tabla temporal contiene realmente millones de filas.