Mi introducción a las bases de datos y PostgreSQL fue para el desarrollo de aplicaciones web y el análisis estadístico. Aprendí lo suficiente de SQL para obtener las consultas y devolver las respuestas correctas. Debido a mi trabajo con PostGIS (y FOSS4G) me hice amigo de Paul Ramsey. Ahora somos compañeros de trabajo en Crunchy Data y él me está ayudando a mejorar mi SQL-fu. Una de las primeras lecciones que me enseñó fue » Tratar de usar juntas en lugar de subconsultas.»

El post de hoy va a trabajar a través de este consejo, ya que Paul y yo trabajamos a través de un poco de SQL.

Lo que estamos tratando de Responder

Estoy tratando de crear algunos nuevos materiales didácticos y orales sobre el uso de SQL en la ciencia de datos y estaba trabajando en alguna manipulación de datos preanálisis. Tenía una tabla, fire_weather, que es un subconjunto de la tabla meteorológica, y quiero encontrar todas las entradas en weather que NO están en fire_weather. Mi instinto inicial era escribir una subconsulta, pero esto parecía una consulta sencilla y fácil para seguir el consejo de Paul de «usar una unión».

Consulta de unión incorrecta

Empecé con esta consulta de unión:

 select count(weather.*) from weather, fire_weather where weather.id != fire_weather.id;

Y no funcionó (de lo contrario, no estaría escribiendo esta publicación de blog). Resulta que esto hace una unión cruzada donde terminamos con todas las combinaciones en parejas de todas las filas en ambas tablas.

La Consulta de unión adecuada

Así que en este punto me aflojé (en lugar de llamar al teléfono) Paul y empezamos a discutir cómo hacer la unión adecuada. Resulta que la sintaxis correcta es:

select count(weather.*)from weather left join fire_weather on weather.id = fire_weather.idwhere fire_weather.id is null;

Básicamente, haces una unión externa izquierda, que te da todas las filas de la tabla de clima y solo las entradas fire_weather que coinciden. Luego filtra todos los registros donde hay coincidencias para fire_weather. Bastante simple de entender, pero no muy similar a un conjunto, como en el uso de la teoría de conjuntos (que es la base de las relaciones en los sistemas de bases de datos relacionales). Por otra parte, ahora tenemos una consulta de unión en funcionamiento.

Analizar esto

Como parte de mi viaje hacia una mayor comprensión de SQL en PostgreSQL, me he convertido en un gran fan de EXPLAIN ANALYZE for for timings y mirando el plan de consultas. Solo por curiosidad, decido mirar el plan de tiempo y consulta para la consulta de unión. Aquí está la salida y tomó aproximadamente 7 milisegundos con un plan de consulta algo complicado:

Aggregate (cost=1358.58..1358.59 rows=1 width=8) (actual time=6.780..6.781 rows=1 loops=1)
-> Hash Anti Join (cost=1168.76..1358.58 rows=1 width=64) (actual time=1.648..6.218 rows=6802 loops=1)
Hash Cond: (weather.id = fire_weather.id)
-> Seq Scan on weather (cost=0.00..159.05 rows=8205 width=68) (actual time=0.008..3.157 rows=8205 loops=1)
-> Hash (cost=785.56..785.56 rows=30656 width=4) (actual time=1.609..1.609 rows=1403 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 306kB
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.008..1.399 rows=1403 loops=1)
Planning Time: 0.110 ms
Execution Time: 6.807 ms

Ahora la subconsulta

Y ahora quería ver cómo funcionaría mi idea original para una subconsulta. Esta es la forma de subconsulta de responder la misma pregunta:

select count(weather.*) from weather where id not in (select id from fire_weather);

Más Análisis

Debería ver por qué esta consulta me atrajo, está muy basada en conjuntos y es muy simple de escribir. Cuando miro esta consulta con explain analyze obtengo:

Aggregate (cost=1052.02..1052.03 rows=1 width=8) (actual time=7.458..7.459 rows=1 loops=1)
-> Seq Scan on weather (cost=862.20..1041.76 rows=4102 width=64) (actual time=1.139..6.727 rows=6802 loops=1)
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 1403
SubPlan 1
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.004..0.643 rows=1403 loops=1)
Planning Time: 0.068 ms
Execution Time: 7.497 ms

Comparación de consultas

Así que terminamos con un plan muy simple y tiempos que son casi iguales a los de la unión. Paul y yo discutimos por qué los tiempos podrían ser tan similares y se nos ocurrieron al menos dos razones:

  1. El conjunto de datos tiene muy pocas filas (8k), por lo que el rendimiento de subconsulta podría degradarse con un conjunto de datos más grande.
  2. Mi máquina tiene unidades de disco NVMe que le dan al acceso secuencial una diferencia de rendimiento aún mayor.

Otra Subconsulta basada en conjuntos

Finalmente, Paul, ideó una consulta basada en conjuntos más para responder a la misma pregunta:

select * from weather exceptselect weather.* from weather join fire_weather on weather.id = fire_weather.id

Esta usa una nueva cláusula SQL, EXCEPT, que es parte de los combinadores de consulta de operación set. Una gran restricción en estas consultas es que las consultas a cada lado de la cláusula except deben devolver las mismas columnas y tipos de datos. Esto explica por qué esta consulta no puede devolver el recuento total de filas. Pero esta consulta resultó ser peor en rendimiento y un plan de consulta mucho más complicado:

HashSetOp Except (cost=0.00..1984.13 rows=8205 width=74) (actual time=10.382..11.171 rows=6802 loops=1)
-> Append (cost=0.00..1532.86 rows=16410 width=74) (actual time=0.021..5.867 rows=9608 loops=1)
" -> Subquery Scan on ""*SELECT* 1"" (cost=0.00..241.10 rows=8205 width=44) (actual time=0.021..2.300 rows=8205 loops=1)"
-> Seq Scan on weather (cost=0.00..159.05 rows=8205 width=40) (actual time=0.006..0.537 rows=8205 loops=1)
" -> Subquery Scan on ""*SELECT* 2"" (cost=261.61..1209.71 rows=8205 width=44) (actual time=1.796..2.788 rows=1403 loops=1)"
-> Hash Join (cost=261.61..1127.66 rows=8205 width=40) (actual time=1.795..2.583 rows=1403 loops=1)
Hash Cond: (fire_weather.id = weather_1.id)
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.008..0.413 rows=1403 loops=1)
-> Hash (cost=159.05..159.05 rows=8205 width=40) (actual time=1.768..1.768 rows=8205 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 765kB
-> Seq Scan on weather weather_1 (cost=0.00..159.05 rows=8205 width=40) (actual time=0.002..0.518 rows=8205 loops=1)
Planning Time: 0.127 ms
Execution Time: 11.975 ms

El Giro sorpresa

Luego pensé un poco más sobre la consulta que sugirió Paul y me di cuenta de que realmente no necesitábamos la combinación en el lado derecho de la cláusula except. Dado que fire_weather contiene todas las mismas columnas que weather, podemos usar las columnas que queramos y obtener la respuesta que esperábamos.

select id from weather exceptselect id from fire_weather;

Ahora esto tiene una sintaxis de conjunto agradable que lo hace realmente fácil de entender. Si realmente queríamos obtener el recuento como en las otras consultas, podemos envolver nuestra consulta en un CTE.

with count_me as (select id from weather exceptselect id from fire_weather)select count(*) from count_me;

Con este ticket dorado obtenemos tiempos de consulta de 6 ms y planes de consulta más limpios pero no más simples. Debo señalar que la limpieza y la simplicidad no son factores clave en la evaluación de un plan de consulta.

HashSetOp Except (cost=0.00..1624.68 rows=8205 width=8) (actual time=4.834..5.313 rows=6802 loops=1)
-> Append (cost=0.00..1527.52 rows=38861 width=8) (actual time=0.004..3.248 rows=9608 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..241.10 rows=8205 width=8) (actual time=0.004..1.903 rows=8205 loops=1)
-> Seq Scan on weather (cost=0.00..159.05 rows=8205 width=4) (actual time=0.003..1.001 rows=8205 loops=1)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..1092.12 rows=30656 width=8) (actual time=0.003..0.554 rows=1403 loops=1)
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.002..0.409 rows=1403 loops=1)
Planning Time: 0.246 ms
Execution Time: 5.897 ms

Mis conclusiones finales

Aquí están las lecciones finales que me gustaría dejarles de este pequeño ejercicio.

  1. Los tiempos de consulta del globo ocular nunca-todos eran de la misma velocidad para mi ojo. Explica que analyze es tu amigo.
  2. Escriba la consulta de la manera que tenga más sentido y luego realice los tiempos. Si no es bueno, busque una alternativa (probablemente se una)
  3. Hay varias formas de llegar a la misma respuesta en SQL: la respuesta «correcta» va a ser altamente dependiente de la situación. Algunas cosas que influirán en el resultado:
    1. Su tamaño de datos: una consulta puede dejar de ser «aceptable» a medida que su tamaño de datos crece
    2. Índices
    3. Su velocidad de disco
    4. Tamaño de memoria
    5. Velocidad del procesador
    6. Con qué frecuencia planea ejecutar la consulta
  4. Finalmente, el tiempo dedicado a mejorar sus conocimientos y habilidades de SQL dará sus frutos generosamente. Incluso si no escribes las consultas más eficientes, suelen ser más rápidas que escribir mucho código de procedimiento. A medida que aprendo más y más patrones SQL, más sorprendido estoy de todo el código que puedo reemplazar con unas pocas líneas de SQL (y generalmente obtengo un gran aumento de rendimiento).

Deja una respuesta

Tu dirección de correo electrónico no será publicada.