Início / dba / Perguntas / 250508
Accepted
Chessbrain
Asked: 2019-10-09 00:41:07 +0800 CST

Consulta com desempenho horrível, como corrigir

  • 772
select
    user_id,
    count(id) as unread_count
from
    notifications
where
    is_read = false
    and user_id in(select(unnest('{200 user IDs}' :: bigint[])))
group by
    user_id;

O problema é que essa consulta é executada por 1 minuto e às vezes um pouco mais do que isso. A tabela tem 32 GB e já existe um índice no campo user_id.

Aqui está um plano de execução

HashAggregate  (cost=123354.81..123629.64 rows=27483 width=16) (actual time=90823.880..90823.972 rows=188 loops=1)
  Group Key: user_id
  ->  Nested Loop  (cost=2.32..123217.40 rows=27483 width=16) (actual time=0.184..90752.136 rows=48571 loops=1)
        ->  HashAggregate  (cost=1.76..2.76 rows=100 width=8) (actual time=0.146..0.577 rows=200 loops=1)
              Group Key: unnest(200 user IDs)
              ->  Result  (cost=0.00..0.51 rows=100 width=8) (actual time=0.021..0.073 rows=200 loops=1)
        ->  Index Scan using ix_notification_user_id on notification  (cost=0.56..1229.40 rows=275 width=16) (actual time=119.659..453.533 rows=243 loops=200)
              Index Cond: (200 user IDs)
              Filter: (NOT is_read)
              Rows Removed by Filter: 368
Planning time: 0.189 ms
Execution time: 90824.196 ms

Eu tentei uma solução usando uma tabela temporária, inserindo os valores unnest na tabela temporária e comparando. Mas o desempenho não melhorou em nada.

Eu executei esta consulta para ver as estatísticas do índice:

    schemaname,
    tablename,
    reltuples::bigint,
    relpages::bigint,
    otta,
    round(case when otta = 0 then 0.0 else sml.relpages / otta::numeric end, 1) as tbloat,
    relpages::bigint - otta as wastedpages,
    bs*(sml.relpages-otta)::bigint as wastedbytes,
    pg_size_pretty((bs*(relpages-otta))::bigint) as wastedsize,
    iname,
    ituples::bigint,
    ipages::bigint,
    iotta,
    round(case when iotta = 0 or ipages = 0 then 0.0 else ipages / iotta::numeric end, 1) as ibloat,
    case
        when ipages < iotta then 0
        else ipages::bigint - iotta
    end as wastedipages,
    case
        when ipages < iotta then 0
        else bs*(ipages-iotta)
    end as wastedibytes
    --CASE WHEN ipages < iotta THEN pg_size_pretty(0) ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize

    from (
    select
        schemaname,
        tablename,
        cc.reltuples,
        cc.relpages,
        bs,
        ceil((cc.reltuples*((datahdr + ma- (case when datahdr % ma = 0 then ma else datahdr % ma end))+ nullhdr2 + 4))/(bs-20::float)) as otta,
        coalesce(c2.relname, '?') as iname,
        coalesce(c2.reltuples, 0) as ituples,
        coalesce(c2.relpages, 0) as ipages,
        coalesce(ceil((c2.reltuples*(datahdr-12))/(bs-20::float)), 0) as iotta
        -- very rough approximation, assumes all cols

        from (
        select
            ma,
            bs,
            schemaname,
            tablename,
            (datawidth +(hdr + ma-
            (
                case
                when hdr % ma = 0 then ma
                else hdr % ma
            end)))::numeric as datahdr,
            (maxfracsum*(nullhdr + ma-
            (
                case
                when nullhdr % ma = 0 then ma
                else nullhdr % ma
            end))) as nullhdr2
        from
            (
            select
                schemaname,
                tablename,
                hdr,
                ma,
                bs,
                sum((1-null_frac)* avg_width) as datawidth,
                max(null_frac) as maxfracsum,
                hdr +(
                select
                    1 + count(*)/ 8
                from
                    pg_stats s2
                where
                    null_frac <> 0
                    and s2.schemaname = s.schemaname
                    and s2.tablename = s.tablename ) as nullhdr
            from
                pg_stats s,
                (
                select
                    (
                    select
                        current_setting('block_size')::numeric) as bs,
                    case
                        when substring(v, 12, 3) in ('8.0',
                        '8.1',
                        '8.2') then 27
                        else 23
                    end as hdr,
                    case
                        when v ~ 'mingw32' then 8
                        else 4
                    end as ma
                from
                    (
                    select
                        version() as v) as foo ) as constants
            group by
                1,
                2,
                3,
                4,
                5 ) as foo ) as rs
    join pg_class cc on
        cc.relname = rs.tablename
    join pg_namespace nn on
        cc.relnamespace = nn.oid
        and nn.nspname = rs.schemaname
    left join pg_index i on
        indrelid = cc.oid
    left join pg_class c2 on
        c2.oid = i.indexrelid ) as sml
where
    sml.relpages - otta > 0
    or ipages - iotta > 10
order by
    wastedbytes desc,
    wastedibytes desc;

E tanto o índice PK quanto o índice user_id têm mais de 5GB wastedsizee mais de 500k+ wastedpages.

Minha pergunta é, qual a solução para isso? É puramente um problema de índice que precisa reindexou é outra coisa que estou perdendo?

Não tenho permissão para alterar a estrutura da tabela, simplesmente tenho que otimizá-la para, de alguma forma, passar de 1+ minutos para menos de 1s

Depois de adicionar o índice parcial em user_id onde is_read = false, o tempo de consulta foi reduzido em aproximadamente 10 a 15 segundos. Mas obviamente ainda está demorando muito.

EDIT: Há um total de 32,5 milhões de linhas nesta tabela. Executando esta consulta:

SELECT t.user_id, COALESCE(unread_count, 0) AS unread_count
FROM   unnest('{200 user_ids}'::bigint[]) t(user_id)
LEFT   JOIN LATERAL (
   SELECT count(*) AS unread_count
   FROM   notification n
   WHERE  n.user_id = t.user_id
   AND    n.is_read = false
   ) sub ON true
;

resulta neste plano de execução (engraçado, ontem isso durou mais de um minuto, hoje por ~ 30 segundos ou menos):

Nested Loop Left Join  (cost=1209.05..120908.50 rows=100 width=16) (actual time=333.088..27260.557 rows=200 loops=1)
  Buffers: shared hit=1981 read=20396 dirtied=7
  I/O Timings: read=27023.896
  ->  Function Scan on unnest t  (cost=0.00..1.00 rows=100 width=8) (actual time=0.022..0.360 rows=200 loops=1)
  ->  Aggregate  (cost=1209.04..1209.05 rows=1 width=8) (actual time=136.292..136.293 rows=1 loops=200)
        Buffers: shared hit=1981 read=20396 dirtied=7
        I/O Timings: read=27023.896
        ->  Index Only Scan using ix_test on notification n  (cost=0.44..1208.29 rows=300 width=0) (actual time=2.153..136.170 rows=105 loops=200)
              Index Cond: (user_id = t.user_id)
              Heap Fetches: 21088
              Buffers: shared hit=1981 read=20396 dirtied=7
              I/O Timings: read=27023.896
Planning time: 0.135 ms
Execution time: 27260.745 ms
postgresql optimization

2 respostas

  1. Best Answer

    Seu plano de explicação é um pouco confuso, pois parece que a varredura de índice está obtendo os dados de todos os 200 user_ids de uma só vez, mas fazendo isso 200 vezes. Mas fazendo o experimento, não é isso que está fazendo, cada iteração do loop aninhado está obtendo os dados para um user_id dessa lista, não a lista inteira. Portanto, é apenas um problema de apresentação na saída EXPLAIN.

    Se você set track_io_timing = one fizer EXPLAIN (ANALISAR, BUFFERS), tenho certeza que descobrirá que a maior parte do tempo é gasto na leitura de dados do disco. A leitura de 48.571 linhas espalhadas aleatoriamente em 32 GB não é rápida, a menos que todos os dados já estejam armazenados em cache na memória ou os dados estejam em um SSD PCIe incrivelmente rápido.

    Sua melhor aposta aqui, além de usar algum hardware sério, é fazer com que ele use uma varredura somente de índice. Para a consulta que você mostra, isso exigiria um índice como este:

    create index on notifications (user_id , is_read, id);

    Aspire a mesa antes de experimentá-la. Se funcionar, você precisará considerar como manter a mesa bem aspirada, pois a configuração de autovac padrão pode não ser adequada.

    Eu não me preocuparia com o inchaço relatado. Essa consulta (onde você conseguiu?) relata grandes quantidades de bytes desperdiçados nos índices, mesmo em uma tabela recém-reindexada. Além disso, o que ele relata para wastepages não são páginas totalmente vazias, mas sim wastebytes divididos pelo tamanho da página. O que me parece bem idiota.

    • 2

  2. Sua consulta elimina user_idda matriz passada. Normalmente, você deseja mostrar aqueles com uma contagem de 0.
    LEFT JOIN LATERAL .. ON true, seguido por COALESCEcuida disso. Se você realmente deseja que os eliminados mudem para CROSS JOINe soltem COALESCE, o mesmo desempenho:

    SELECT t.user_id, COALESCE(unread_count, 0) AS unread_count
FROM   unnest('{200 user IDs}'::bigint[]) t(user_id)
LEFT   JOIN LATERAL (
   SELECT count(*) AS unread_count
   FROM   notifications n
   WHERE  n.user_id = t.user_id
   AND    n.is_read = false
   ) sub ON true

    Ponto principal: eu vi esse estilo de consulta ser mais rápido que um grande INseguido por GROUP BYum over. Relacionado:

    Ponto menor: count(*) é um pouco mais rápido que count(id)- e equivalente nesta consulta, pois idé o PK e, portanto, NOT NULL. Ver:

    Um índice btree básico (user_id)é bom para isso. Como idé irrelevante, não o inclua. Um índice parcial pode ajudar, como sugerido por a_horse:

    CREATE INDEX ON notifications (user_id) WHERE is_read = false;

    E você relatou que corta 10-15 segundos. Isso pode significar uma de duas coisas, no entanto:

    1. Uma parte substancial de linhas com is_read = falseestá envolvida, o que torna o índice útil .

    2. O novo índice é uma vitória porque começa em perfeitas condições, sem inchaço. (Você mencionou muito inchaço nos índices existentes.) Mas se houver apenas algumas linhas relevantes com is_read = false, o ganho evaporará com o tempo e tudo o que resta é o custo de gravação adicionado e mais espaço ocupado.

    Simplesmente não há informações suficientes na pergunta para dizer. Este bit em sua EXPLAINsaída é inconclusivo:

    Rows Removed by Filter: 368

    O número de linhas na tabela é importante. A parcela de is_read = false. E outros itens sugeridos aqui .

    • 1

relate perguntas