Slide 1
Slide 1 text
Otimização de consultas
no PostgreSQL
Slide 2
Slide 2 text
Otimização de consultas no
PostgreSQL
● Troubleshooting
● Encontrando os vilões
● EXPLAIN (como as consultas são executadas)
● Índices
● SQL Patterns / Anti-patterns
Slide 3
Slide 3 text
Otimização de consultas no
PostgreSQL
Troubleshooting
Slide 4
Slide 4 text
Otimização de consultas
no PostgreSQL
Troubleshooting
Siga aquele cara…
(fique de olho no PID
em vermelho)
Slide 5
Slide 5 text
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
Slide 6
Slide 6 text
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
Slide 7
Slide 7 text
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)
Slide 8
Slide 8 text
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'
;
Slide 9
Slide 9 text
Otimização de consultas no
PostgreSQL
Encontrando os vilões
Slide 10
Slide 10 text
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'
;
Slide 11
Slide 11 text
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 | ...
Slide 12
Slide 12 text
Otimização de consultas
no PostgreSQL
Encontrando os vilões
pgBadger
Slide 13
Slide 13 text
Otimização de consultas no
PostgreSQL
EXPLAIN
Slide 14
Slide 14 text
Otimização de consultas
no PostgreSQL
EXPLAIN
Execução de uma consulta
https://www.postgresql.org/developer/backend
Slide 15
Slide 15 text
Otimização de consultas
no PostgreSQL
EXPLAIN
Execução de uma consulta
Slide 16
Slide 16 text
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
Slide 17
Slide 17 text
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)
Slide 18
Slide 18 text
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)
Slide 19
Slide 19 text
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)
Slide 20
Slide 20 text
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
Slide 21
Slide 21 text
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
Slide 22
Slide 22 text
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}
Slide 23
Slide 23 text
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
Slide 24
Slide 24 text
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)
Slide 25
Slide 25 text
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
Slide 26
Slide 26 text
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)
Slide 27
Slide 27 text
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);
Slide 28
Slide 28 text
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))
Slide 29
Slide 29 text
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
Slide 30
Slide 30 text
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)
Slide 31
Slide 31 text
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)
Slide 32
Slide 32 text
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)
Slide 33
Slide 33 text
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)
Slide 34
Slide 34 text
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)
Slide 35
Slide 35 text
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)
Slide 36
Slide 36 text
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)
Slide 37
Slide 37 text
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)
Slide 38
Slide 38 text
Otimização de consultas
no PostgreSQL
EXPLAIN
explain.depesz.com
Slide 39
Slide 39 text
Otimização de consultas
no PostgreSQL
EXPLAIN
tatiyants.com/pev
Slide 40
Slide 40 text
Otimização de consultas
no PostgreSQL
EXPLAIN
pgmustard.com
Slide 41
Slide 41 text
Otimização de consultas no
PostgreSQL
Índices
Slide 42
Slide 42 text
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
Slide 43
Slide 43 text
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
Slide 44
Slide 44 text
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
Slide 45
Slide 45 text
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';
Slide 46
Slide 46 text
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)
Slide 47
Slide 47 text
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)
Slide 48
Slide 48 text
Otimização de consultas no
PostgreSQL
SQL Patterns / Antipatterns
Slide 49
Slide 49 text
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))
Slide 50
Slide 50 text
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
Slide 51
Slide 51 text
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}
Slide 52
Slide 52 text
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
Slide 53
Slide 53 text
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
...
Slide 54
Slide 54 text
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
...
Slide 55
Slide 55 text
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
...
Slide 56
Slide 56 text
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 (...)
Slide 57
Slide 57 text
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 (...)
Slide 58
Slide 58 text
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 (...)
Slide 59
Slide 59 text
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
...
Slide 60
Slide 60 text
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
...
Slide 61
Slide 61 text
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',
...)
Slide 62
Slide 62 text
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',
...)
Slide 63
Slide 63 text
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;
Slide 64
Slide 64 text
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;
Slide 65
Slide 65 text
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;
Slide 66
Slide 66 text
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;
Slide 67
Slide 67 text
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
Slide 68
Slide 68 text
Dúvidas?
Contatos:
● Savepoint
● Slides: Speaker decker (Fábio Telles)
● Email: [email protected]
● LinkedIn: Telles