Início / dba / Perguntas / 199968
Accepted
Imanol Y.
Asked: 2018-03-13 00:43:44 +0800 CST

Cláusula ORDER BY elimina o desempenho da consulta

  • 772

Contexto:

PostgreSQL 10, com 3667438 registros na tabela de usuários, a tabela de usuários possui um JSONB chamado social, geralmente utilizamos uma estratégia de indexação de saídas de funções computadas, assim podemos agregar informações em um único índice. A saída da engagement(social)função é um tipo numérico de precisão dupla.

Problema:

A cláusula problemática é ORDER BY engagement(social) DESC NULLS LAST, também há um índice btree idx_in_social_engagement with DESC NULLS LASTanexado a esses dados.

Consulta rápida:

EXPLAIN ANALYZE
SELECT  "users".* FROM "users"
WHERE (follower_count(social) < 500000)
AND (engagement(social) > 0.03)
AND (engagement(social) < 0.25)
AND (peemv(social) < 533)
ORDER BY "users"."created_at" ASC
LIMIT 12 OFFSET 0;

Limit  (cost=0.43..52.25 rows=12 width=1333) (actual time=0.113..1.625 
rows=12 loops=1)
   ->  Index Scan using created_at_idx on users  (cost=0.43..7027711.55 rows=1627352 width=1333) (actual time=0.112..1.623 rows=12 loops=1)
         Filter: ((follower_count(social) < 500000) AND (engagement(social) > '0.03'::double precision) AND (engagement(social) <  '0.25'::double precision) AND (peemv(social) > '0'::double precision) AND (peemv(social) < '533'::double precision))
         Rows Removed by Filter: 8
 Planning time: 0.324 ms
 Execution time: 1.639 ms

Consulta lenta:

EXPLAIN ANALYZE 
SELECT  "users".* FROM "users" 
WHERE (follower_count(social) < 500000) 
AND (engagement(social) > 0.03) 
AND (engagement(social) < 0.25) 
AND (peemv(social) > 0.0) 
AND (peemv(social) < 533) 
ORDER BY engagement(social) DESC NULLS LAST, "users"."created_at" ASC 
LIMIT 12 OFFSET 0;

Limit  (cost=2884438.00..2884438.03 rows=12 width=1341) (actual time=68011.728..68011.730 rows=12 loops=1)
->  Sort  (cost=2884438.00..2888506.38 rows=1627352 width=1341) (actual time=68011.727..68011.728 rows=12 loops=1)
        Sort Key: (engagement(social)) DESC NULLS LAST, created_at
        Sort Method: top-N heapsort  Memory: 45kB
        ->  Index Scan using idx_in_social_engagement on users  (cost=0.43..2847131.26 rows=1627352 width=1341) (actual time=0.082..67019.102 rows=1360633 loops=1)
            Index Cond: ((engagement(social) > '0.03'::double precision) AND (engagement(social) < '0.25'::double precision))
            Filter: ((follower_count(social) < 500000) AND (peemv(social) > '0'::double precision) AND (peemv(social) < '533'::double precision))
            Rows Removed by Filter: 85580
Planning time: 0.312 ms
Execution time: 68011.752 ms

O select vai com * porque preciso de todos os dados armazenados em cada linha.

Atualizar:

CREATE INDEX idx_in_social_engagement on influencers USING BTREE ( engagement(social) DESC NULLS LAST)

Definição exata do índice

postgresql performance

2 respostas

  1. Best Answer

    Sua ORDER BYcláusula está em:

    engagement(social) DESC NULLS LAST, "users"."created_at" ASC

    Mas suspeito que seu índice esteja apenas em:

    engagement(social) DESC NULLS LAST

    Portanto, o índice não é capaz de suportar totalmente o ORDER BY.

    Você pode reproduzir o mesmo problema sem usar JSONBíndices de expressão ou. Você pode salvar a situação criando um índice de expressão composta em ambas as colunas em seu arquivo ORDER BY.

    Se o planejador do PostgreSQL fosse infinitamente sábio, poderia usar o índice existente de forma eficiente. Teria que marchar engagement(social) DESC NULLS LASTaté coletar 12 tuplas que atendessem a todos os demais requisitos do filtro. Em seguida, ele continuaria subindo esse índice até coletar todo o restante das tuplas que estavam vinculadas engagement(social)à 12ª tupla (e que atendessem aos outros critérios). Em seguida, ele teria que reordenar todas as tuplas coletadas no full ORDER BY, e aplicar o LIMIT 12a esse conjunto expandido e reordenado. Mas o planejador do PostgreSQL não é infinitamente sábio.

    • 7

  2. Eu suspeitaria que o culpado aqui é a falta de estatísticas nas colunas JSONB. O Postgres não mantém nenhuma estatística nas colunas JSONB, em vez disso, usa estimativas codificadas. Se essas estimativas estiverem muito distantes, o que é bastante provável de acontecer, isso pode levar a planos de consulta terríveis como o seu caso.

    Em seu bom plano, o Postgres primeiro ordena os dados e depois os filtra. Isso é extremamente rápido para consultas com cláusulas LIMIT e um índice na coluna classificada, se o filtro não remover muitas linhas. O índice é ordenado, portanto, colocar as linhas em ordem é extremamente barato. A cláusula limit significa que você só precisa buscar algumas linhas até ter o suficiente para satisfazê-la.

    Mas se o seu filtro excluir muitas linhas, por exemplo, se apenas 0,1% corresponder a ele, a classificação primeiro exigiria que você percorresse a maior parte de sua tabela para encontrar linhas suficientes, porque você filtra quase todas elas. Nesse caso, filtrar primeiro por índice e depois classificar é muito mais rápido. Isso é o que seu plano ruim faz e, obviamente, não é uma boa opção para seus dados.

    De longe, a melhor opção seria colocar os valores que você usa aqui em suas próprias colunas. O JSONB é extremamente útil para alguns usos, mas se você não precisa da flexibilidade que ele fornece, a velha forma relacional simples é muito melhor.

    Um índice funcional fornece estatísticas, o que deve ajudar sua consulta. Suspeito que no seu caso isso não funcione bem porque você está passando a coluna social inteira para a função e não pode construir uma boa estatística a partir disso. Você pode tentar um índice apenas na chave de engajamento na coluna social, que pode fornecer ao Postgres as estatísticas necessárias para um melhor plano de consulta. Veja minha própria pergunta sobre estatísticas JSONB para um exemplo de como fazer isso.

    • 1

relate perguntas