minha introdução a bancos de dados e PostgreSQL foi para desenvolvimento de aplicativos da web e análise estatística. Aprendi SQL suficiente para fazer com que as consultas retornassem as respostas certas. Por causa do meu trabalho com PostGIS (e FOSS4G) eu me tornei amigo de Paul Ramsey. Agora somos colegas de trabalho da Crunchy Data e ele está me ajudando no meu SQL-fu. Uma das primeiras lições que ele me ensinou foi ” tente usar junções em vez de subconsultas.”

o post de hoje vai funcionar com esse conselho, já que Paul e eu trabalhamos com algum SQL.

o que estamos tentando responder

estou tentando criar alguns novos materiais de ensino e fala sobre o uso de SQL na ciência de dados e eu estava trabalhando em alguma manipulação de dados de pré-análise. Eu tinha uma tabela, fire_weather, que é um subconjunto da tabela meteorológica, e quero encontrar todas as entradas no clima que não estão em fire_weather. Meu instinto inicial era escrever uma subconsulta, mas isso parecia uma consulta direta e fácil de seguir o conselho “use a join” de Paul.

consulta de junção errada

comecei com esta consulta de junção:

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

e não funcionou (caso contrário, eu não estaria escrevendo este post). Acontece que isso faz uma junção cruzada onde acabamos com todas as combinações pares de todas as linhas em ambas as tabelas.

a consulta de junção adequada

então, neste ponto, eu relaxo (ao contrário de tocar no telefone) Paul e começamos a discutir como fazer a junção adequada. Acontece que a sintaxe certa é:

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

basicamente, você faz uma junção externa esquerda, dando-lhe todas as linhas da tabela meteorológica e apenas as entradas fire_weather que correspondem. Em seguida, você filtra todos os registros onde há correspondências para fire_weather. Muito simples de entender, mas não muito definido como, como no uso da teoria dos conjuntos (que é a base das relações em sistemas de banco de dados relacionais). Então, novamente, agora temos uma consulta de junção de trabalho.

analise isso

como parte da minha jornada para uma maior compreensão do SQL no PostgreSQL, tornei-me um grande fã de explicar analisar por horários e olhar para o plano de consulta. Apenas por curiosidade, decido examinar o cronograma e o plano de consulta para a consulta join. Aqui é a saída e levou cerca de 7 milissegundos com um pouco complicado plano de consulta:

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

Agora a Subconsulta

E agora eu queria ver como a minha ideia original para uma subconsulta iria realizar. Aqui está a maneira da subconsulta de responder à mesma pergunta:

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

mais análise

você deve ver por que essa consulta me atraiu, é muito baseada em conjuntos e muito simples de escrever. Quando eu olho para esta consulta com explain analyze, recebo:

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

comparação de consultas

portanto, acabamos com um plano e horários muito simples que são quase iguais aos da junção. Paul e eu discutimos por que os horários podem ser tão semelhantes e descobrimos pelo menos dois motivos:

  1. o conjunto de dados tem muito poucas linhas (8k) para que o desempenho da subconsulta possa se degradar com um conjunto de dados maior.
  2. minha máquina tem unidades de disco NVMe dando acesso sequencial uma diferença de desempenho ainda maior.

outra subconsulta baseada em conjunto

finalmente Paul, surgiu com mais uma consulta baseada em conjunto para responder à mesma pergunta:

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

Este usa uma nova cláusula SQL, exceto, que faz parte dos combinadores de consulta de operação definida. Uma grande restrição nessas consultas é que as consultas de cada lado da cláusula except devem retornar as mesmas colunas e tipos de dados. Isso explica por que essa consulta não pode retornar a contagem total de linhas. Mas essa consulta acabou sendo pior em desempenho e um plano de consulta muito mais 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

the Surprise Twist

então pensei um pouco mais sobre a consulta que Paul sugeriu e percebi que realmente não precisávamos da junção no lado direito da cláusula exceto. Como fire_weather contém todas as mesmas colunas que weather, podemos apenas usar as colunas que queremos e obter a resposta que esperávamos.

select id from weather exceptselect id from fire_weather;

agora isso tem uma sintaxe de conjunto agradável, tornando-o realmente fácil de entender. Se quisermos realmente obter a contagem como nas outras consultas, podemos envolver nossa consulta em um CTE.

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

com este bilhete dourado, obtemos 6 tempos de consulta ms e planos de consulta mais limpos, mas não mais simples. Devo observar que a limpeza e a simplicidade não são fatores-chave na avaliação de um plano 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

meus Takeaways finais

Aqui estão as lições finais que eu gostaria de deixá-lo com este pequeno exercício.

  1. nunca os tempos de consulta do globo ocular – todos eram da mesma velocidade para o meu olho. Explique analisar é seu amigo.
  2. escreva a consulta da maneira que faz mais sentido e, em seguida, faça os horários. Se não for bom, procure uma alternativa (provavelmente junções)
  3. existem várias maneiras de chegar à mesma resposta em SQL – a resposta “certa” será altamente dependente da situação. Algumas coisas que vão influenciar o resultado:
    1. o Seu tamanho de dados: uma consulta pode deixar de ser “ok” como o seu tamanho de dados cresce
    2. Índices
    3. velocidade do Seu disco
    4. tamanho da Memória
    5. velocidade do Processador
    6. quantas vezes você pretende executar a consulta
  4. Finalmente, o tempo gasto melhorar o SQL conhecimentos e habilidades vai pagar generosamente. Mesmo se você não escrever as consultas mais eficientes, elas ainda são geralmente mais rápidas do que escrever muito código processual. À medida que aprendo mais e mais padrões SQL, mais espantado fico com todo o código que posso substituir por algumas linhas de SQL (e geralmente recebo um grande aumento de desempenho).

Deixe uma resposta

O seu endereço de email não será publicado.