Estou tentando otimizar uma consulta, que nunca é concluída no Postgres 12.7. Demora horas, até dias, faz a CPU ir 100% e nunca mais retorna:
SELECT "id", "counter", "item_id", "item_name", "type", "updated_time"
FROM "changes"
WHERE (type = 1 OR type = 3) AND user_id = 'kJ6GYJNPM4wdDY5dUV1b8PqDRJj6RRgW'
OR type = 2 AND item_id IN (SELECT item_id FROM user_items WHERE user_id = 'kJ6GYJNPM4wdDY5dUV1b8PqDRJj6RRgW')
ORDER BY "counter" ASC LIMIT 100;
Tentei reescrevê-lo aleatoriamente usando UNION e acredito que seja equivalente. Basicamente, há duas partes na consulta, uma para tipo = 1 ou 3 e outra para tipo = 2.
(
SELECT "id", "counter", "item_id", "item_name", "type", "updated_time"
FROM "changes"
WHERE (type = 1 OR type = 3) AND user_id = 'kJ6GYJNPM4wdDY5dUV1b8PqDRJj6RRgW'
) UNION (
SELECT "id", "counter", "item_id", "item_name", "type", "updated_time"
FROM "changes"
WHERE type = 2 AND item_id IN (SELECT item_id FROM user_items WHERE user_id = 'kJ6GYJNPM4wdDY5dUV1b8PqDRJj6RRgW')
) ORDER BY "counter" ASC LIMIT 100;
Essa consulta retorna em 10 segundos, em vez de nunca retornar após vários dias para a outra. Alguma ideia do que está causando essa enorme diferença?
Planos de consulta
Para a consulta original:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1001.01..1697110.80 rows=100 width=119)
-> Gather Merge (cost=1001.01..8625312957.40 rows=508535 width=119)
Workers Planned: 2
-> Parallel Index Scan using changes_pkey on changes (cost=0.98..8625253259.82 rows=211890 width=119)
Filter: ((((type = 1) OR (type = 3)) AND ((user_id)::text = 'kJ6GYJNPM4wdDY5dUV1b8PqDRJj6RRgW'::text)) OR ((type = 2) AND (SubPlan 1)))
SubPlan 1
-> Materialize (cost=0.55..18641.22 rows=143863 width=33)
-> Index Only Scan using user_items_user_id_item_id_unique on user_items (cost=0.55..16797.90 rows=143863 width=33)
Index Cond: (user_id = 'kJ6GYJNPM4wdDY5dUV1b8PqDRJj6RRgW'::text)
E para a consulta UNION:
Limit (cost=272866.63..272866.88 rows=100 width=212) (actual time=10564.742..10566.964 rows=100 loops=1)
-> Sort (cost=272866.63..273371.95 rows=202128 width=212) (actual time=10564.739..10566.950 rows=100 loops=1)
Sort Key: changes.counter
Sort Method: top-N heapsort Memory: 69kB
-> Unique (cost=261604.20..265141.44 rows=202128 width=212) (actual time=9530.376..10493.030 rows=147261 loops=1)
-> Sort (cost=261604.20..262109.52 rows=202128 width=212) (actual time=9530.374..10375.845 rows=147261 loops=1)
Sort Key: changes.id, changes.counter, changes.item_id, changes.item_name, changes.type, changes.updated_time
Sort Method: external merge Disk: 19960kB
-> Gather (cost=1000.00..223064.76 rows=202128 width=212) (actual time=2439.116..7356.233 rows=147261 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Append (cost=0.00..201851.96 rows=202128 width=212) (actual time=2421.400..7815.315 rows=49087 loops=3)
-> Parallel Hash Join (cost=12010.60..103627.94 rows=47904 width=119) (actual time=907.286..3118.898 rows=24 loops=3)
Hash Cond: ((changes.item_id)::text = (user_items.item_id)::text)
-> Parallel Seq Scan on changes (cost=0.00..90658.65 rows=365215 width=119) (actual time=1.466..2919.855 rows=295810 loops=3)
Filter: (type = 2)
Rows Removed by Filter: 428042
-> Parallel Hash (cost=11290.21..11290.21 rows=57631 width=33) (actual time=78.190..78.191 rows=48997 loops=3)
Buckets: 262144 Batches: 1 Memory Usage: 12416kB
-> Parallel Index Only Scan using user_items_user_id_item_id_unique on user_items (cost=0.55..11290.21 rows=57631 width=33) (actual time=0.056..107.247 rows=146991 loops=1)
Index Cond: (user_id = 'kJ6GYJNPM4wdDY5dUV1b8PqDRJj6RRgW'::text)
Heap Fetches: 11817
-> Parallel Seq Scan on changes changes_1 (cost=0.00..95192.10 rows=36316 width=119) (actual time=2410.556..7026.664 rows=73595 loops=2)
Filter: (((user_id)::text = 'kJ6GYJNPM4wdDY5dUV1b8PqDRJj6RRgW'::text) AND ((type = 1) OR (type = 3)))
Rows Removed by Filter: 1012184
Planning Time: 65.846 ms
Execution Time: 10575.679 ms
(27 rows)
Definições
Table "public.changes"
Column | Type | Collation | Nullable | Default
---------------+-----------------------+-----------+----------+------------------------------------------
counter | integer | | not null | nextval('changes_counter_seq'::regclass)
id | character varying(32) | | not null |
item_type | integer | | not null |
item_id | character varying(32) | | not null |
item_name | text | | not null | ''::text
type | integer | | not null |
updated_time | bigint | | not null |
created_time | bigint | | not null |
previous_item | text | | not null | ''::text
user_id | character varying(32) | | not null | ''::character varying
Indexes:
"changes_pkey" PRIMARY KEY, btree (counter)
"changes_id_unique" UNIQUE CONSTRAINT, btree (id)
"changes_id_index" btree (id)
"changes_item_id_index" btree (item_id)
"changes_user_id_index" btree (user_id)
Table "public.user_items"
Column | Type | Collation | Nullable | Default
--------------+-----------------------+-----------+----------+----------------------------------------
id | integer | | not null | nextval('user_items_id_seq'::regclass)
user_id | character varying(32) | | not null |
item_id | character varying(32) | | not null |
updated_time | bigint | | not null |
created_time | bigint | | not null |
Indexes:
"user_items_pkey" PRIMARY KEY, btree (id)
"user_items_user_id_item_id_unique" UNIQUE CONSTRAINT, btree (user_id, item_id)
"user_items_item_id_index" btree (item_id)
"user_items_user_id_index" btree (user_id)
Contagem de tipos
postgres=> select count(*) from changes where type = 1;
count
---------
1201839
(1 row)
postgres=> select count(*) from changes where type = 2;
count
--------
888269
(1 row)
postgres=> select count(*) from changes where type = 3;
count
-------
83849
(1 row)
Quantos item_id por user_id
postgres=> SELECT min(ct), max(ct), avg(ct), sum(ct) FROM (SELECT count(*) AS ct FROM user_items GROUP BY user_id) x;
min | max | avg | sum
-----+--------+-----------------------+---------
6 | 146991 | 2253.0381526104417671 | 1122013
(1 row)
Normalmente, é uma boa ideia dividir isso em
OR
umaUNION
consulta. Ver:A primeira
SELECT
daUNION
consulta deve se reduzir a milissegundos com este índice parcial de várias colunas:E depois de adicionar
ORDER BY counter LIMIT 100
. Como a consulta externa tem o mesmo, nunca precisamos de mais de 100 linhas desta parte:Você não forneceu números reais, portanto, a julgar pelo alto número de itens por usuário (
rows=146991
no plano de consulta), tente este como 2ndSELECT
:Em combinação com este índice:
Para cardinalidades substancialmente diferentes, uma diferente
SELECT
pode ser (muito) melhor. Em particular, isso será um tiro pela culatra para usuários com poucos ou nenhum item.A consulta completa então:
Sim, são 3x no
ORDER BY counter LIMIT 100
total.Apartes
O plano de consulta mostra
(never executed)
paraSubPlan 1
, o que parece implicar que nenhuma linha comtype = 2
foi encontrada. O que é estranho. (Veja a resposta adicionada de jjanes para uma possível explicação.)Você está operando com
varchar(32)
IDs grandes. Se você realmente precisa de identificadores exclusivos globalmente, considere issouuid
. Muito menor e mais rápido. Caso contrário, um simplesbigint
(ou mesmointeger
) pode cobrir facilmente seus 2 milhões de linhas. Torna as tabelas e os índices menores e mais rápidos. Mais rápidoUNION
também. Ver:Caso contrário, você pode pelo menos adicionar
COLLATE "C"
às suasvarchar(32)
colunas para melhorar oUNION
desempenho (e todos os tipos e operações relacionadas). A menos que você execute o banco de dados deCOLLATE "C"
qualquer maneira, o que parece improvável. Ver:Seu design de mesa atual é um desperdício. Considere reescrever assim:
Deve tornar a tabela ~ 15 MB menor (comparando tabelas pristine sem bloat) e tudo um pouco mais rápido. Ver:
O tolerável com o OR teve sorte, porque encontrou 100 linhas correspondentes com tipos 1 ou 3, antes de encontrar qualquer um do tipo 2 que precisava ser verificado na outra tabela. O intolerável aparentemente teve que fazer a verificação na outra tabela, e o faz de maneira muito lenta, percorrendo todas as linhas dela. Agora ele deve usar um subplano com hash, em vez de um subplano regular. A única razão pela qual ele não usaria o subplano com hash que eu posso pensar é que sua configuração work_mem é bastante baixa, então ele não acha que pode caber a tabela com hash na memória, então ele volta para um método completamente horrível.
Um "subplano com hash" não tem como vazar para o disco, portanto, se o planejador achar que usará muita memória, ele simplesmente não agendará um. No lado da união, uma junção de hash pode se espalhar para o disco, por isso está mais disposto a usar isso.
Se você aumentar seu work_mem, o plano OR deve ficar muito mais rápido. Não deve demorar muito, nas minhas mãos 10 MB é suficiente (mas ainda é muito pequeno para um servidor moderno, eu provavelmente o configuraria para pelo menos 100 MB, a menos que você tenha um bom motivo para não)