Otimização de consultas no PostgreSQL

Palestra realizada no DevCon 2024 em Curitiba/PR 2024-05-31

- Troubleshooting
- Encontrando os vilões
- Explain
- Índices
- SQL Patterns / Anti-patterns

Transcript

1. Otimização de consultas no PostgreSQL
2. Otimização de consultas no PostgreSQL • Troubleshooting • Encontrando os vilões • EXPLAIN (como as consultas são executadas) • Índices • SQL Patterns / Anti-patterns
3. Otimização de consultas no PostgreSQL Troubleshooting
4. Otimização de consultas no PostgreSQL Troubleshooting Siga aquele cara… (fique de olho no PID em vermelho)
5. Otimização de consultas no PostgreSQL Troubleshooting top -ci (no Linux) top - 05:55:43 up 16 days, 1:05, 1 user, load average: 4,15, 4,91, 5,10 Tasks: 268 total, 5 running, 263 sleeping, 0 stopped, 0 zombie %Cpu(s): 29,4 us, 2,2 sy, 0,0 ni, 60,9 id, 7,0 wa, 0,0 hi, 0,2 si, 0,3 st KiB Mem : 49458404 total, 283300 free, 731524 used, 48443580 buff/cache KiB Swap: 2097148 total, 1933544 free, 163604 used. 41707168 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 28020 postgres 20 0 6685304 6,156g 6,150g R 99,7 13,1 7:37.10 postgres: user_x db_z1 192.168.193.49(41822) SELECT 29777 postgres 20 0 6686492 5,927g 5,920g R 99,7 12,6 1:39.24 postgres: postgres db_z1 [local] DELETE 29804 postgres 20 0 6702516 5,622g 5,605g R 95,7 11,9 0:41.25 postgres: postgres db_z1 [local] CREATE TABLE AS 25214 postgres 20 0 6696060 6,171g 6,160g R 24,6 13,1 104:59.68 postgres: user_x db_z1 192.168.163.81(58832) SELECT 28205 postgres 20 0 6685260 6,137g 6,131g S 23,6 13,0 6:10.22 postgres: user_x db_z1 192.168.193.49(41828) idle 28268 postgres 20 0 6685308 6,149g 6,143g S 13,6 13,0 7:11.18 postgres: user_x db_z1 192.168.193.49(41830) idle 13819 postgres 20 0 6697552 6,165g 6,147g S 12,0 13,1 36:54.04 postgres: user_x db_z1 192.168.149.241(37806) idle 27643 postgres 20 0 7729100 6,167g 6,144g S 6,3 13,1 5:02.79 postgres: autovacuum worker process db_zzz 6149 postgres 20 0 6679408 6,119g 6,119g S 1,0 13,0 151:08.36 postgres: writer process 22375 postgres 20 0 6687356 6,167g 6,160g S 1,0 13,1 39:53.23 postgres: user_x db_zzz 192.168.163.81(59232) idle 8398 postgres 20 0 6696236 6,172g 6,160g S 0,7 13,1 55:45.06 postgres: user_x db_zzz 192.168.163.81(59194) idle
6. Otimização de consultas no PostgreSQL Troubleshooting iotop (no Linux) Total DISK READ : 16.97 M/s | Total DISK WRITE : 12.51 M/s Actual DISK READ: 16.92 M/s | Actual DISK WRITE: 8.57 M/s TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND 28205 be/4 postgres 4.39 M/s 0.00 B/s 0.00 % 17.95 % postgres: user_x db_x 192.168.193.49(41828) idle 17811 be/4 postgres 3.22 M/s 54.53 K/s 0.00 % 10.81 % postgres: user_x db_x 192.168.149.241(38098) idle 9896 be/4 postgres 0.00 B/s 1744.80 K/s 0.00 % 6.78 % postgres: user_x db_x 192.168.163.81(59200) idle 27643 be/4 postgres 9.36 M/s 4.17 M/s 0.00 % 3.33 % postgres: autovacuum worker process db_zzz 25214 be/4 postgres 0.00 B/s 2.89 M/s 0.00 % 1.44 % postgres: user_x db_x 192.168.163.81(58832) idle 6150 be/4 postgres 0.00 B/s 1028.19 K/s 0.00 % 0.79 % postgres: wal writer process 22375 be/4 postgres 0.00 B/s 179.15 K/s 0.00 % 0.24 % postgres: user_x db_x 192.168.163.81(59232) idle 30021 be/4 postgres 0.00 B/s 327.15 K/s 0.00 % 0.00 % postgres: postgres db_x [local] CREATE TABLE AS 6149 be/4 postgres 0.00 B/s 2.08 M/s 0.00 % 0.00 % postgres: writer process 27115 be/4 postgres 0.00 B/s 15.58 K/s 0.00 % 0.00 % postgres: user_x db_x 192.168.193.49(41806) SELECT
7. Otimização de consultas no PostgreSQL Troubleshooting pg_stat_activity SELECT pid, usename, client_addr, state, application_name, query_start, xact_start, wait_event_type, query FROM pg_stat_activity WHERE state NOT LIKE 'idle%' AND pid != pg_backend_pid() ORDER BY usename, client_addr desc, xact_start desc, backend_start desc; pid | usename | client_addr | state | app_name | query_st | xact_st | query -------+----------+----------------+--------+--------------+----------+----------+--------------- 30339 | postgres | | active | psql | 06:11:06 | 06:10:02 | DELETE FROM ... 27643 | postgres | | active | | 04:41:32 | 04:41:32 | autovacuum: ... 28473 | user_x | 192.168.193.49 | active | AppTool | 06:11:44 | 06:11:43 | SELECT id FROM ... (3 registros)
8. Otimização de consultas no PostgreSQL Troubleshooting Kill bad sessions! -- Kill specific session SELECT pg_terminate_backend( 30339); -- Kill active sessions running for more than 1 hour SELECT pg_terminate_backend( pid) FROM pg_stat_activity WHERE state = 'active' AND backend_type = 'client backend' AND pid != pg_backend_pid() AND query_start < current_timestamp - INTERVAL '1 hour' ; -- Kill locked sessions SELECT pg_terminate_backend( pid) FROM pg_stat_activity WHERE state = 'active' AND backend_type = 'client backend' AND pid != pg_backend_pid() AND wait_event = 'Lock' ;
9. Otimização de consultas no PostgreSQL Encontrando os vilões
10. Otimização de consultas no PostgreSQL Encontrando os vilões Kill bad sessions! -- Kill specific session SELECT pg_terminate_backend( 30339); -- Kill active sessions running for more than 1 hour SELECT pg_terminate_backend( pid) FROM pg_stat_activity WHERE state = 'active' AND backend_type = 'client backend' AND pid != pg_backend_pid() AND query_start < current_timestamp - INTERVAL '1 hour' ; -- Kill locked sessions SELECT pg_terminate_backend( pid) FROM pg_stat_activity WHERE state = 'active' AND backend_type = 'client backend' AND pid != pg_backend_pid() AND wait_event = 'Lock' ;
11. Otimização de consultas no PostgreSQL Encontrando os vilões pg_stat_statements \x SELECT query, calls, rows, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5; -[ RECORD 1 ]-------+---------------------------------------------------- query | WITH totalgeral AS ( SELECT COALESCE(SUM(item.valortotal + item.valoracrescimo + calls | 9874 rows | 186052 total_exec_time | 1418685132 -[ RECORD 2 ]----- query | ...
12. Otimização de consultas no PostgreSQL Encontrando os vilões pgBadger
13. Otimização de consultas no PostgreSQL EXPLAIN
14. Otimização de consultas no PostgreSQL EXPLAIN Execução de uma consulta https://www.postgresql.org/developer/backend
15. Otimização de consultas no PostgreSQL EXPLAIN Execução de uma consulta
16. Otimização de consultas no PostgreSQL EXPLAIN Sintaxe do comando EXPLAIN [ ( option [, ...] ) ] statement EXPLAIN [ ANALYZE ] [ VERBOSE ] statement where option can be one of: ANALYZE [ boolean ] VERBOSE [ boolean ] COSTS [ boolean ] SETTINGS [ boolean ] GENERIC_PLAN [ boolean ] BUFFERS [ boolean ] WAL [ boolean ] TIMING [ boolean ] SUMMARY [ boolean ] FORMAT { TEXT | XML | JSON | YAML } https://www.postgresql.org/docs/current/sql-explain.html
17. Otimização de consultas no PostgreSQL EXPLAIN Exemplo simples postgres=# EXPLAIN SELECT 1; QUERY PLAN ------------------------------------------ Result (cost=0.00..0.01 rows=1 width=0) (1 row) postgres=# EXPLAIN SELECT 1 + 1; QUERY PLAN ------------------------------------------ Result (cost=0.00..0.01 rows=1 width=0) (1 row)
18. Otimização de consultas no PostgreSQL EXPLAIN Consulta simples CREATE TABLE t (id SERIAL, texto TEXT); INSERT INTO t (texto) SELECT 'DevCon' FROM generate_series(1,1000); EXPLAIN SELECT * FROM t; QUERY PLAN ----------------------------------------------------- - Seq Scan on t (cost=0.00.. 22.70 rows=1270 width=36) (1 row)
19. Otimização de consultas no PostgreSQL EXPLAIN Consulta simples com a opção ANALYZE EXPLAIN ANALYZE SELECT * FROM t; QUERY PLAN ----------------------------------------------------------------------------------------- Seq Scan on t ( cost=0.00..22.70 rows= 1270 width=36) ( actual time=0.010..0.080 rows= 1000 loops=1) Planning time: 0.040 ms Execution time: 0.127 ms (3 rows)
20. Otimização de consultas no PostgreSQL EXPLAIN Atualizando as estatísticas antes do EXPLAIN ANALYZE t; EXPLAIN ANALYZE SELECT * FROM t; ----------------------------------------------------------------------------------------- Seq Scan on t (cost=0.00..17.00 rows= 1000 width=23) (actual time=0.007..0.078 rows= 1000 loops=1) Planning time: 0.050 ms Execution time: 0.114 ms
21. Otimização de consultas no PostgreSQL EXPLAIN pg_stats SELECT null_frac, avg_width, n_distinct, most_common_vals, most_common_freqs FROM pg_stats WHERE tablename = 't' AND attname = 'id'; -[ RECORD 1 ]----------+--------------------------------------------- null_frac | 0 avg_width | 4 n_distinct | -1 most_common_vals | most_common_freqs | https://www.postgresql.org/docs/current/view-pg-stats.html
22. Otimização de consultas no PostgreSQL EXPLAIN pg_stats SELECT null_frac, avg_width, n_distinct, most_common_vals, most_common_freqs FROM pg_stats WHERE tablename = 't' AND attname = 'texto'; -[ RECORD 1 ]----------+--------------------------------------------- null_frac | 0 avg_width | 6 n_distinct | 1 most_common_vals | {"DevCon"} most_common_freqs | {1}
23. Otimização de consultas no PostgreSQL EXPLAIN Escolhas do otimizador de consultas • Tipo de acesso ◦ Seq Scan ◦ Index Scan ◦ Index only scan ◦ Bitmap Index Scan • Tipos de junção de tabelas ◦ Nested loop ◦ Hash join ◦ Merge join • Ordem das junções
24. Otimização de consultas no PostgreSQL EXPLAIN Index scan CREATE UNIQUE INDEX ON t (id); LOG: statement: CREATE UNIQUE INDEX ON t (id); CREATE INDEX EXPLAIN SELECT * FROM t WHERE id = 42; ------------------------------------------------------------------- Index Scan using t_id_idx on t (cost=0.28..2.49 rows=1 width=23) Index Cond: (id = 42)
25. Otimização de consultas no PostgreSQL EXPLAIN Influenciando o otimizador EXPLAIN SELECT * FROM t WHERE id > 42; ----------------------------------------------------- Seq Scan on t (cost=0.00.. 19.50 rows=958 width=23) Filter: (id > 42) SET enable_seqscan = FALSE; EXPLAIN SELECT * FROM t WHERE id > 42; ---------------------------------------------------------------------- Index Scan using t_id_idx on t (cost=0.28.. 29.64 rows=958 width=23) Index Cond: (id > 42) https://www.postgresql.org/docs/current/runtime-config-query.html#RUNTIME-CONFIG-QUERY-ENABLE
26. Otimização de consultas no PostgreSQL EXPLAIN Index Only Scan EXPLAIN SELECT id FROM t WHERE id > 42; ----------------------------------------------------- Index Only Scan using t1_i_idx on t1 (cost=0.43..3.33 rows=40 width=4) Index Cond: ( i < 42)
27. Otimização de consultas no PostgreSQL EXPLAIN Bitmap Index Scan CREATE TABLE ttt (id serial PRIMARY KEY, i integer); INSERT INTO ttt (i) SELECT random() * 1000000000 AS i FROM generate_series(1,100000); CREATE INDEX ON ttt (i);
28. Otimização de consultas no PostgreSQL EXPLAIN Bitmap Index Scan EXPLAIN SELECT * FROM ttt WHERE i < 5000000; QUERY PLAN -------------------------------------------------------------------------- Bitmap Heap Scan on ttt (cost=6.30..333.35 rows=491 width=8) Recheck Cond: (i < 5000000) -> Bitmap Index Scan on ttt_i_idx (cost=0.00..6.18 rows=491 width=0) Index Cond: ( i < 5000000) EXPLAIN SELECT * FROM ttt WHERE i < 500000 OR i > 995000000; QUERY PLAN -------------------------------------------------------------------------------- Bitmap Heap Scan on ttt (cost=8.55..373.10 rows=582 width=8) Recheck Cond: ((i < 500000) OR (i > 995000000)) -> BitmapOr (cost=8.55..8.55 rows=582 width=0) -> Bitmap Index Scan on ttt_i_idx (cost=0.00..1.76 rows=49 width=0) Index Cond: ( i < 500000) -> Bitmap Index Scan on ttt_i_idx (cost=0.00..6.50 rows=534 width=0) Index Cond: ( i > 995000000) EXPLAIN SELECT * FROM ttt WHERE i BETWEEN 10000 AND 5000000; QUERY PLAN -------------------------------------------------------------------------- Bitmap Heap Scan on ttt (cost=7.53..335.80 rows=491 width=8) Recheck Cond: ((i >= 10000) AND (i <= 5000000)) -> Bitmap Index Scan on ttt_i_idx (cost=0.00..7.40 rows=491 width=0) Index Cond: ( (i >= 10000) AND (i <= 5000000))
29. Otimização de consultas no PostgreSQL EXPLAIN Tid Scan SELECT ctid, id FROM ttt WHERE id = 5000; ctid | id ---------+------ (22,28) | 5000 EXPLAIN SELECT * FROM ttt WHERE ctid = '(22,28)'::tid; QUERY PLAN --------------------------------------------------- Tid Scan on ttt (cost=0.00..1.11 rows=1 width=8) TID Cond: (ctid = '(22,28)'::tid) https://www.postgresql.org/docs/current/ddl-system-columns.html
30. Otimização de consultas no PostgreSQL EXPLAIN Tid Scan UPDATE ttt SET i = 12345 WHERE id = 5000; SELECT ctid, * FROM ttt WHERE id = 5000; ctid | id | i -----------+------+------- (442,109) | 5000 | 12345 EXPLAIN ANALYZE SELECT * FROM ttt WHERE ctid = '(22,28)'::tid; QUERY PLAN -------------------------------------------------------------------------------------------- Tid Scan on ttt (cost=0.00..1.11 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=1) TID Cond: ( ctid = '(22,28)'::tid)
31. Otimização de consultas no PostgreSQL EXPLAIN Function Scan EXPLAIN SELECT * FROM generate_Series(1,100) i ; QUERY PLAN ---------------------------------------------------------------------- - Function Scan on generate_series i (cost=0.00..1.00 rows=100 width=4)
32. Otimização de consultas no PostgreSQL EXPLAIN ORDER BY EXPLAIN SELECT * FROM t ORDER BY id; ----------------------------------------------------------------------- Index Scan using t_id_idx on t (cost=0.28..27.88 rows=1000 width=23) EXPLAIN SELECT * FROM t ORDER BY id DESC; -------------------------------------------------------------------------------- Index Scan Backward using t_id_idx on t (cost=0.28..27.88 rows=1000 width=23) EXPLAIN SELECT * FROM t ORDER BY texto; ------------------------------------------------------------ Sort (cost=66.83..69.33 rows=1000 width=23) Sort Key: texto -> Seq Scan on t (cost=0.00..17.00 rows=1000 width=23)
33. Otimização de consultas no PostgreSQL EXPLAIN ORDER BY EXPLAIN SELECT * FROM t ORDER BY texto DESC; ------------------------------------------------------------ Sort (cost=66.83..69.33 rows=1000 width=23) Sort Key: texto DESC -> Seq Scan on t (cost=0.00..17.00 rows=1000 width=23) EXPLAIN SELECT * FROM t WHERE id < 42 ORDER BY texto DESC; -------------------------------------------------------------------------- Sort (cost=4.34..4.45 rows=42 width=23) Sort Key: texto DESC -> Index Scan using t_id_idx on t (cost=0.28..3.21 rows=42 width=23) Index Cond: ( id < 42)
34. Otimização de consultas no PostgreSQL EXPLAIN LIMIT EXPLAIN SELECT * FROM t ORDER BY texto LIMIT 10; ------------------------------------------------------------------ Limit (cost=38.61.. 38.63 rows=10 width=23) -> Sort (cost=38.61..41.11 rows=1000 width=23) Sort Key: texto -> Seq Scan on t (cost=0.00..17.00 rows= 1000 width=23) EXPLAIN SELECT * FROM t WHERE id < 10 ORDER BY texto; -------------------------------------------------------------------------- Sort (cost=2.82..2.84 rows=10 width=23) Sort Key: texto -> Index Scan using t_id_idx on t (cost=0.28..2.65 rows=10 width=23) Index Cond: (id < 10)
35. Otimização de consultas no PostgreSQL EXPLAIN LIMIT / OFFSET EXPLAIN SELECT * FROM t ORDER BY texto LIMIT 10 OFFSET 10; ----------------------------------------------------------------- Limit (cost=43.63..43.66 rows=10 width=23) -> Sort (cost=43.61..46.11 rows=1000 width=23) Sort Key: texto -> Seq Scan on t (cost=0.00..17.00 rows=1000 width=23) EXPLAIN SELECT * FROM t ORDER BY texto LIMIT 10 OFFSET 990; ----------------------------------------------------------------- Limit (cost=69.30..69.33 rows=10 width=23) -> Sort (cost=66.83..69.33 rows=1000 width=23) Sort Key: texto -> Seq Scan on t (cost=0.00..17.00 rows=1000 width=23)
36. Otimização de consultas no PostgreSQL EXPLAIN Nested Loop EXPLAIN SELECT a.* FROM pg_class c join pg_attribute a ON c.oid = a.attrelid WHERE c.relname IN ( 'pg_class', 'pg_namespace' ); ---------------------------------------------------------------------- -- Nested Loop (cost=8.84..54.73 rows=15 width=205) -> Bitmap Heap Scan on pg_class c (cost=8.56..14.03 rows=2 width=4) Recheck Cond: (relname = ANY ('{pg_class,pg_namespace}' -> Bitmap Index Scan on pg_class_relname_nsp_index (cost=0.00 Index Cond: (relname = ANY ('{pg_class,pg_namespace}' -> Index Scan using pg_attribute_relid_attnum_index on pg_attribute Index Cond: (attrelid = c.oid)
37. Otimização de consultas no PostgreSQL EXPLAIN Hash Join EXPLAIN SELECT * FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid; QUERY PLAN ---------------------------------------------------------------------------- Hash Join (cost=1.09..21.45 rows=413 width=398) Hash Cond: (c.relnamespace = n.oid) -> Seq Scan on pg_class c (cost=0.00..18.13 rows=413 width=273) -> Hash (cost=1.04..1.04 rows=4 width=125) -> Seq Scan on pg_namespace n (cost=0.00..1.04 rows=4 width=125)
38. Otimização de consultas no PostgreSQL EXPLAIN explain.depesz.com
39. Otimização de consultas no PostgreSQL EXPLAIN tatiyants.com/pev
40. Otimização de consultas no PostgreSQL EXPLAIN pgmustard.com
41. Otimização de consultas no PostgreSQL Índices
42. Otimização de consultas no PostgreSQL Índices Tipos de índices Operadores Aplicação Observação B-Tree <, <=, =, >=, > Uso geral Não funciona com buscas do tipo LIKE '%valor%' Hash = Comparação apenas com igualdades GiST <<, &<, &>, >>, <<|, &<|, |&>, |>>, @>, <@, ~=, && Conjunto de estratégias para diferentes operadores geométricos SP-GiST <<, >>, ~=, <@, <<|, |>> operadores em dados geométricos não balanceados GIN <@, @>, =, && Busca textual, Arrays, Json Ocupa muito espaço e tem alto custo de atualização. BRIN <, <=, =, >=, > Grandes volumes de dados com pouca variação Não indicado colunas que sofrem constantes atualizações
43. Otimização de consultas no PostgreSQL Índices Colunas em índices • Um índice pode ter 1 ou mais colunas • A ordem das colunas no índice muda o seu comportamento • Exemplos CREATE INDEX ON t (a, b, c); ◦ Usam o índice: SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3; SELECT * FROM t WHERE a = 1 AND b = 2 SELECT * FROM t WHERE a = 1 ◦ Não usam o índice: SELECT * FROM t WHERE b = 2 AND c = 3; SELECT * FROM t WHERE c = 3; SELECT * FROM t WHERE b = 2
44. Otimização de consultas no PostgreSQL Índices Colunas em índices • Um índice pode ter 1 ou mais colunas • A ordem das colunas no índice muda o seu comportamento • Exemplos CREATE INDEX ON t (a, b, c); ◦ Usam o índice: SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3; SELECT * FROM t WHERE a = 1 AND b = 2 SELECT * FROM t WHERE a = 1 ◦ Não usam o índice: SELECT * FROM t WHERE b = 2 AND c = 3; SELECT * FROM t WHERE c = 3; SELECT * FROM t WHERE b = 2
45. Otimização de consultas no PostgreSQL Índices UNIQUE • Garante que não haverá duplicidade de linhas com os mesmos valores • Somente pode ser utilizado com índices B-Tree: CREATE UNIQUE INDEX ON t (a, b); Índice parcial • Se aplica apenas a uma parte das linhas de uma tabela. • Utiliza um filtro com uma cláusula WHERE: CREATE INDEX ON t (a, b) WHERE status = 'active';
46. Otimização de consultas no PostgreSQL Índices Índices com expressões CREATE INDEX ON film (description); EXPLAIN SELECT title FROM film WHERE lower(description) = 'database'; QUERY PLAN ------------------------------------------------------ Seq Scan on film (cost=0.00..79.00 rows=5 width=15) Filter: (lower(description) = 'database'::text) CREATE INDEX ON film (lower(description)); EXPLAIN SELECT title FROM film WHERE lower(description) = 'database'; QUERY PLAN ----------------------------------------------------------------------------- Bitmap Heap Scan on film (cost=1.41..6.85 rows=5 width=15) Recheck Cond: (lower(description) = 'database'::text) -> Bitmap Index Scan on film_lower_idx (cost=0.00..1.41 rows=5 width=0) Index Cond: (lower(description) = 'database'::text)
47. Otimização de consultas no PostgreSQL Índices Índices com expressões CREATE INDEX ON staff ( first_name, last_name); EXPLAIN SELECT * FROM staff WHERE first_name = 'Louie' AND last_name = 'Walter'; QUERY PLAN ---------------------------------------------------------------------------------------------- Index Scan using staff_first_name_last_name_idx on staff (cost=0.28..2.50 rows=1 width=153) Index Cond: (( first_name = 'Louie'::text) AND (last_name = 'Walter'::text)) CREATE INDEX ON staff ( (first_name || ' ' || last_name)); EXPLAIN SELECT * FROM staff WHERE first_name || ' ' || last_name = 'Louie Walter'; QUERY PLAN --------------------------------------------------------------------------------------- Bitmap Heap Scan on staff (cost=1.44..9.97 rows=8 width=153) Recheck Cond: (((first_name || ' '::text) || last_name) = 'Louie Walter'::text) -> Bitmap Index Scan on staff_expr_idx (cost=0.00..1.44 rows=8 width=0) Index Cond: ((( first_name || ' '::text) || last_name) = 'Louie Walter'::text)
48. Otimização de consultas no PostgreSQL SQL Patterns / Antipatterns
49. Otimização de consultas no PostgreSQL Patterns / Antipatterns Um índice para cada coluna CREATE INDEX ON staff ( first_name); CREATE INDEX ON staff ( last_name); EXPLAIN SELECT * FROM staff WHERE first_name = 'Louie' AND last_name = 'Walter'; QUERY PLAN ------------------------------------------------------------------------------------ Index Scan using staff_first_name_idx on staff (cost=0.28..2.50 rows=1 width=153) Index Cond: ( first_name = 'Louie'::text) Filter: (last_name = 'Walter'::text) CREATE INDEX ON staff ( first_name, last_name); EXPLAIN SELECT * FROM staff WHERE first_name = 'Louie' AND last_name = 'Walter'; QUERY PLAN ------------------------------------------------------------------------------------------ -- Index Scan using staff_first_name_last_name_idx on staff (cost=0.28..2.50 rows=1 width=153) Index Cond: ( (first_name = 'Louie'::text) AND (last_name = 'Walter'::text))
50. Otimização de consultas no PostgreSQL Patterns / Antipatterns Um índice para cada coluna • Não crie índices aleatoriamente • Os índices são criados a partir de consultas reais • Um índices composto é mais eficiente do que dois índices individuais, quando a consulta filtra por mais de uma coluna na mesma tabela
51. Otimização de consultas no PostgreSQL Patterns / Antipatterns Índices seletivos CREATE INDEX ON film( language_id); EXPLAIN SELECT * FROM film WHERE language_id = 1; QUERY PLAN --------------------------------------------------------- Seq Scan on film (cost=0.00..76.50 rows=585 width=390) Filter: (language_id = 1) EXPLAIN SELECT * FROM film WHERE language_id = 3; QUERY PLAN ------------------------------------------------------------------------------------- Index Scan using film_language_id_idx on film (cost=0.15..46.69 rows=72 width=390) Index Cond: (language_id = 3) SELECT null_frac, avg_width, n_distinct, most_common_vals, most_common_freqs FROM pg_stats WHERE tablename = 'film' AND attname = 'language_id'; null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs -----------+-----------+------------+------------------+-------------------------------------- 0 | 4 | 6 | { 1,4,2,6,5,3} | {0.585,0.09,0.087,0.087,0.079,0.072}
52. Otimização de consultas no PostgreSQL Patterns / Antipatterns Índices seletivos • Índices são úteis para retornar um pequeno conjunto de linhas • Nas estatísticas vemos que para a coluna language_id temos: ◦ language_id = 1 : 58,5% ◦ language_id = 3: 7,2% • Logo o índice não será utilizado no segundo caso
53. Otimização de consultas no PostgreSQL Patterns / Antipatterns LEFT JOINs desnecessários SELECT count(s.id) FROM s JOIN st ON st.st_id = s.id LEFT JOIN g ON g.id = s.g_id LEFT JOIN sc ON sc.id = s.sc_id LEFT JOIN st ON st.st_id = s.id WHERE ...
54. Otimização de consultas no PostgreSQL Patterns / Antipatterns LEFT JOINs desnecessários SELECT count(s.id) FROM s JOIN st ON st.st_id = s.id LEFT JOIN g ON g.id = s.g_id LEFT JOIN sc ON sc.id = s.sc_id LEFT JOIN st ON st.st_id = s.id WHERE ...
55. Otimização de consultas no PostgreSQL Patterns / Antipatterns LEFT JOINs desnecessários SELECT count(s.id) FROM s JOIN st ON st.st_id = s.id WHERE ...
56. Otimização de consultas no PostgreSQL Patterns / Antipatterns Trocando um OR por um UNION SELECT s.* FROM s JOIN st ON st.st_id = s.id WHERE s.sc_id IN ( SELECT sc.id FROM sc JOIN su ON sc.id = su.sc_id WHERE su.su_id = ?) OR s.id IN (...)
57. Otimização de consultas no PostgreSQL Patterns / Antipatterns Trocando um OR por um UNION SELECT s.* FROM s JOIN st ON st.st_id = s.id WHERE s.sc IN ( SELECT sc.id FROM sc JOIN su ON sc.id = su.sc_id WHERE su.su_id = ? UNION SELECT s.* FROM s JOIN st ON st.st_id = s.id WHERE s.id IN (...)
58. Otimização de consultas no PostgreSQL Patterns / Antipatterns Trocando um OR por um UNION SELECT s.* FROM s JOIN st ON st.st_id = s.id JOIN sc ON sc.id = s.sc_id JOIN su ON sc.id = su.su_id WHERE su.su_id = ? UNION SELECT s.* FROM s JOIN st ON st.st_id = s.id WHERE s.id IN (...)
59. Otimização de consultas no PostgreSQL Patterns / Antipatterns Trocando um LEFT JOIN por um UNION SELECT ... FROM s LEFT JOIN st ON st.st_id = s.id WHERE ...
60. Otimização de consultas no PostgreSQL Patterns / Antipatterns Trocando um LEFT JOIN por um UNION SELECT ... FROM s JOIN st ON st.st_id = s.id WHERE ... UNION SELECT ... FROM s WHERE ...
61. Otimização de consultas no PostgreSQL Patterns / Antipatterns Incluindo um novo subselect SELECT count(s.id) FROM s JOIN st ON st.student_id = s.id WHERE s.id IN ( '1355f7f6-4711-4cb0-b566-a5ca1f78e1c4', '38f28c28-98ab-4b9a-bba8-3eb6c0ba42f5', '0bf5249e-1d90-4ce3-8d16-5bd12ac03f81', '897e94a1-6e4e-4da9-b147-46e2d61088a5', '541b4332-0de9-4e45-8121-aafff8ab2540', '4c8fb608-f82a-44f5-909c-971b79f26a4c', ...)
62. Otimização de consultas no PostgreSQL Patterns / Antipatterns Incluindo um novo subselect SELECT s.id FROM s JOIN ss ON s.id = ss.s_id JOIN sc ON sc.sc_id = sc.id JOIN su ON sc.id = su.sc_id WHERE su.su_id = $1 AND sc.is_deleted = FALSE; SELECT count(s.id) FROM s JOIN st ON st.st_id = s.id WHERE s.id IN ( '1355f7f6-4711-4cb0-b566-a5ca1f78e1c4', '38f28c28-98ab-4b9a-bba8-3eb6c0ba42f5', '0bf5249e-1d90-4ce3-8d16-5bd12ac03f81', '897e94a1-6e4e-4da9-b147-46e2d61088a5', '541b4332-0de9-4e45-8121-aafff8ab2540', '4c8fb608-f82a-44f5-909c-971b79f26a4c', ...)
63. Otimização de consultas no PostgreSQL Patterns / Antipatterns Incluindo um novo subselect SELECT count(s.id) FROM s JOIN st ON st.s_id = s.id JOIN ss ON s.id = ss.s_id JOIN sc ON sc.sc_id = sc.id JOIN su ON sc.id = su.sc_id WHERE su.su_id = $1 AND sc.is_deleted = FALSE;
64. Otimização de consultas no PostgreSQL Patterns / Antipatterns Retornando grandes volumes SELECT ... FROM t WHERE t.status = 1 AND t.xpto > 100 ORDER BY t.x LIMIT 1000 OFFSET 0; SELECT ... FROM t WHERE t.status = 1 AND t.xpto > 100 ORDER BY t.x LIMIT 1000 OFFSET 1000;
65. Otimização de consultas no PostgreSQL Patterns / Antipatterns Retornando grandes volumes SELECT ... FROM t WHERE t.status = 1 AND t.xpto > 100 ORDER BY t.x LIMIT 1000 OFFSET 2000; SELECT ... FROM t WHERE t.status = 1 AND t.xpto > 100 ORDER BY t.x LIMIT 1000 OFFSET 3000;
66. Otimização de consultas no PostgreSQL Patterns / Antipatterns Paginação em grandes volumes BEGIN; DECLARE cursor_t CURSOR FOR SELECT ... FROM t WHERE t.status = 1 AND t.xpto > 100 ORDER BY t.x; FECTH 1000 FROM cursor_t; FECTH 1000 FROM cursor_t; FECTH 1000 FROM cursor_t; FECTH 1000 FROM cursor_t; CLOSE cursor_t; COMMIT;
67. Save the date! 07 e 08 de novembro: PGConf.Brasil2024 PUC Belo Horizonte - MG • PGConf.Brasil Instagram • PGConf.Brasil Telegram • PostgreSQL BR Telegram • PostgreSQL (English) Telegram
68. Dúvidas? Contatos: • Savepoint • Slides: Speaker decker (Fábio Telles) • Email: fabio@savepoint.inf.br • LinkedIn: Telles