Início / dba / Perguntas / 331323
Accepted
Milan Ilic
Asked: 2023-09-18 23:53:14 +0800 CST

Por que o número de linhas ativas buscadas pelas varreduras de índice caiu após a tabela ANALYZE?

  • 772

Usamos PostgreSQL 12 e temos uma tabela simples, event_participantarmazenando 100 GB de dados. event_participantpossui todos os índices necessários, então todas as linhas são buscadas usando-os, ou seja, nenhuma linha é buscada usando varreduras sequenciais.

Normalmente, ele busca 65 linhas/segundo, mas um dia, às 10h, executamos uma campanha planejada em que o número de linhas buscadas usando varreduras de índice saltou para 5,4 milhões de linhas/segundo. No entanto, o número de varreduras de índice permaneceu o mesmo, 200 por segundo. O conteúdo da tabela começou a mudar lentamente, mas não o suficiente para acionar a análise automática porque autovacuum_analyze_scale_factoré 0,01 ou 1% do tamanho da tabela.

Vale ressaltar que configuramos plan_cache_modeo TO force_custom_planneste banco de dados porque nosso aplicativo usa Demonstrativos Preparados e queremos evitar planos genéricos por causa de campanhas ativas.

Após 3 horas de grande carga de CPU e varreduras de índice, realizamos manualmente um ANALYZEdos event_participant, e o número de linhas ativas buscadas pelas varreduras de índice caiu imediatamente de 5,4 milhões de linhas/s para 450 linhas/s .

Estou tentando descobrir como o ANALYZEcomando afetou o número de linhas ativas obtidas pelas varreduras de índice, enquanto o número de varreduras de índice permaneceu o mesmo.

Atualização - incluindo mais detalhes sobre a estrutura e índices da tabela.

> \d+ event_participant
                            Table "public.event_participant"
  Column  |       Type       | Collation | Nullable | Default | Storage  | Stats target | Description 
----------+------------------+-----------+----------+---------+----------+--------------+-------------
 event_id | text             |           | not null |         | extended |              | 
 user_id  | bigint           |           | not null |         | plain    |              | 
 progress | text             |           | not null |         | extended |              | 
 level    | integer          |           | not null | 0       | plain    |              | 
 quality  | double precision |           |          |         | plain    |              | 
Indexes:
    "event_participant_pkey" PRIMARY KEY, btree (user_id, event_id)
    "event_participant_event_id_idx" btree (event_id)
Access method: heap

Assim, às 10h, começou a campanha com um novo evento (new event_id), e a event_participanttabela começou a crescer. A cada login do usuário, o aplicativo backend, sabendo quais eventos estão ativos, seleciona todas as entradas por user_id e event_id: SELECT * from event_participant WHERE user_id=? AND event_id=?;para acompanhar o progresso do usuário.

postgresql

1 respostas

  1. Best Answer

    Mais uma vez, desde o início do evento, a event_participanttabela começou a crescer, mas não o suficiente para acionar autovacuum_analyze,o que atualizaria o plano de consulta.

    Antes do evento começar às 10h, o evento com event_id=tour2023não existia na tabela, então durante o último autovacuum_analyze, que aconteceu horas antes, o plano de consulta não tinha conhecimento tour2023, então sugeriu usar o índice event_participant_event_id_idx.. Testei a hipótese executando um EXPLAIN SELECT com event_id inexistente; ele usa o índice criado nele e filtra as linhas por user_id:

    explain select * from event_participant where user_id = 1 and event_id = 'bla';
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using event_participant_event_id_idx on event_participant  (cost=0.56..1.61 rows=1 width=1409)
   Index Cond: (event_id = 'bla'::text)
   Filter: (user_id = 1)

    o que significa que após tour2023o início do evento durante a execução da consulta, SELECT * from event_participant WHERE user_id=? AND event_id=?;o PostgreSQL costumava event_participant_event_id_idxbuscar todas as linhas event_id=tour2023e, em seguida, filtrar a linha desejada em user_idvez de usar o índice composto "event_participant_pkey" PRIMARY KEY, btree (user_id, event_id). Isso levou ao aumento do número de linhas buscadas pelas varreduras de índice, bem como ao enorme uso da CPU.

    Após a execução ANALYZEmanual, o plano de consulta foi atualizado e o banco de dados decidiu utilizar um índice composto. Conseqüentemente, o número de linhas buscadas nas varreduras de índice caiu para 450 linhas/s.

    Saída EXPLAIN ao usar event_id existente:

    explain select * from event_participant where user_id = 1 and event_id = 'tour2023';
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using event_participant_pkey on event_participant  (cost=0.56..2.58 rows=1 width=1409)
   Index Cond: ((user_id = 1) AND (event_id = 'tour2023'::text))

    Portanto, a resposta é que o plano de consulta estava obsoleto e o PostgreSQL decidiu usar um índice abaixo do ideal.

    Ainda estou perdendo parte do motivo pelo qual o PostgreSQL usou apenas o índice (event_id), pois esperava que o planejador de consultas favorecesse o índice composto quando user_id e event_id são especificados na consulta.

    • 2

relate perguntas