Início / dba / Perguntas / 299737
Accepted
laurent
Asked: 2021-09-17 15:49:33 +0800 CST

Por que essa consulta SQL com UNION é significativamente mais rápida do que a mesma consulta sem?

  • 772

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)
postgresql postgresql-performance

2 respostas

  1. Normalmente, é uma boa ideia dividir isso emOR uma UNIONconsulta. Ver:

    A primeira SELECTdaUNION consulta deve se reduzir a milissegundos com este índice parcial de várias colunas:

    CREATE INDEX ON changes (user_id, counter)
WHERE  type IN (1, 3);

    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:

    (  -- now parentheses are required
SELECT id, counter, item_id, item_name, type, updated_time
FROM   changes
WHERE  type IN (1, 3)
AND    user_id = 'kJ6GYJNPM4wdDY5dUV1b8PqDRJj6RRgW'
ORDER  BY counter
LIMIT  100
)

    Você não forneceu números reais, portanto, a julgar pelo alto número de itens por usuário ( rows=146991no plano de consulta), tente este como 2nd SELECT:

    (
SELECT id, counter, item_id, item_name, type, updated_time
FROM   changes c
WHERE  type = 2
AND    EXISTS (
   SELECT FROM user_items u
   WHERE  u.user_id = 'kJ6GYJNPM4wdDY5dUV1b8PqDRJj6RRgW'   
   AND    c.item_id = u.item_id
   )
ORDER  BY counter
LIMIT  100
);

    Em combinação com este índice:

    CREATE INDEX ON changes (counter, item_id) WHERE  type = 2;

    Para cardinalidades substancialmente diferentes, uma diferente SELECTpode ser (muito) melhor. Em particular, isso será um tiro pela culatra para usuários com poucos ou nenhum item.

    A consulta completa então:

    (<query 1>)
UNION
(<query 2>)
ORDER  BY counter
LIMIT  100;

    Sim, são 3x no ORDER BY counter LIMIT 100total.

    Apartes

    O plano de consulta mostra (never executed)para SubPlan 1, o que parece implicar que nenhuma linha com type = 2foi 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 isso uuid. Muito menor e mais rápido. Caso contrário, um simples bigint(ou mesmo integer) pode cobrir facilmente seus 2 milhões de linhas. Torna as tabelas e os índices menores e mais rápidos. Mais rápido UNIONtambém. Ver:

    Caso contrário, você pode pelo menos adicionar COLLATE "C"às suas varchar(32)colunas para melhorar o UNIONdesempenho (e todos os tipos e operações relacionadas). A menos que você execute o banco de dados de COLLATE "C"qualquer maneira, o que parece improvável. Ver:

    Seu design de mesa atual é um desperdício. Considere reescrever assim:

                                             Table "public.changes"
    Column     |         Type          | Collation | Nullable |                 Default
---------------+-----------------------+-----------+----------+------------------------------------------
 counter       | integer               |           | not null | nextval('changes_counter_seq'::regclass)
 type          | integer               |           | not null |
 item_type     | integer               |           | not null |
 item_id       | character varying(32) |           | not null |
 item_name     | text                  |           | not null | ''::text
 id            | character varying(32) |           | not null |
 previous_item | text                  |           | not null | ''::text
 user_id       | character varying(32) |           | not null | ''::character varying
 updated_time  | bigint                |           | not null |
 created_time  | bigint                |           | not null |

    Deve tornar a tabela ~ 15 MB menor (comparando tabelas pristine sem bloat) e tudo um pouco mais rápido. Ver:

    • 4
  2. Best Answer

    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)

    • 3

relate perguntas