Início / dba / Perguntas / 210136
Accepted
Imanol Y.
Asked: 2018-06-21 04:33:36 +0800 CST

Como otimizar uma consulta para < operador

  • 772

Tenho um SELECTque fica muito lento ao usar o <operador procuro correções ou soluções alternativas para realizar esta operação:

EXPLAIN (ANALYZE) 
SELECT * 
FROM "users" 
WHERE (engagement_level(social) < 1) 
    AND (social_peemv(social) < 33.333333333333336) 
    AND (array['United Kingdom'] <@ mixed_frequent_locations(location)) 
    AND (is_visible(social, flags) = TRUE) 
ORDER BY "users"."created_at" ASC 
LIMIT 12 OFFSET 0;
                                                                                                               QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    ---------------------------
 Limit  (cost=0.43..18572.10 rows=12 width=860) (actual time=5658.037..175582.743 rows=12 loops=1)
   ->  Index Scan using created_at_idx on users  (cost=0.43..6244724.16 rows=4035 width=860) (actual time=5658.035..175582.735 rows=12 loops=1)
         Filter: (is_visible(social, flags) AND (engagement_level(social) < 1) AND (social_peemv(social) < '33.3333333333333'::double precision) AND ('{"United Kingdom"}'::text[] <@ mixed_frequent_locations(location)))
         Rows Removed by Filter: 2816798
 Planning time: 1.573 ms
 Execution time: 175583.373 ms
(6 rows)

EXPLAIN (ANALYZE) 
SELECT * 
FROM "users" 
WHERE (engagement_level(social) < 1) 
  AND (social_peemv(social) = 33.3333) 
  AND (array['United Kingdom'] <@ mixed_frequent_locations(location)) 
  AND (is_visible(social, flags) = TRUE)
ORDER BY "users"."created_at" ASC
LIMIT 12 OFFSET 0;
                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=380.04..380.31 rows=1 width=863) (actual time=0.051..0.051 rows=0 loops=1)
   ->  Result  (cost=380.04..380.31 rows=1 width=863) (actual time=0.050..0.050 rows=0 loops=1)
         ->  Sort  (cost=380.04..380.05 rows=1 width=896) (actual time=0.049..0.049 rows=0 loops=1)
               Sort Key: created_at
               Sort Method: quicksort  Memory: 25kB
               ->  Index Scan using idx_in_social_peemv on users  (cost=0.43..380.03 rows=1 width=896) (actual time=0.044..0.044 rows=0 loops=1)
                     Index Cond: (social_peemv(social) = '33.3333'::double precision)
                     Filter: (is_visible(social, flags) AND (engagement_level(social) < 1) AND ('{"United Kingdom"}'::text[] <@ mixed_frequent_locations(location)))
 Planning time: 0.459 ms
 Execution time: 0.095 ms

No primeiro caso não Index Condé aplicado e o tempo de execução cresce para175583.373 ms

O índice:

 CREATE INDEX idx_in_social_peemv ON users USING BTREE ( social_peemv(social) ) ;
 CREATE INDEX mixed_frequent_locations_idx on users USING GIN ( mixed_frequent_locations(location) ) ;
 CREATE INDEX created_at_idx ON users USING btree (created_at)
 CREATE INDEX idx_in_social_follower_count_and_created_at ON users USING btree (social_follower_count(social) DESC, created_at)
 CREATE INDEX idx_in_egagagement_level_and_created_at ON users USING btree (engagement_level(social), creat
ed_at)

A mesa:

CREATE TABLE users (
    id SERIAL PRIMARY KEY NOT NULL,
    name TEXT,
    bio TEXT,
    social jsonb,
    flags array,
    location jsonb,
    search_field ts_vector,
    created_at TIMESTAMP WITHOUT TIMEZONE,
    udpated_at TIMESTAMP WITHOUT TIMEZONE
);

Versão do Postgres: 10

Toda a condição corresponde a 20 registros de um total de 3669284

Cada condição corresponde:

(engagement_level(social) < 1)= 801176

(social_peemv(social) < 33.333333333333336)= 1621516

(array['United Kingdom'] <@ mixed_frequent_locations(location))= 91625

(is_visible(social, flags) = TRUE)= 3333733

Como sugerido por @jjanes, tentei remover o LIMITe OFFSETe o plano de consulta mudou para um BitMap Heap Scan:

EXPLAIN (ANALYZE)
SELECT *
FROM "users"
WHERE (engagement_level(social) < 1)
     AND (social_peemv(social) < 33.333333333333336)
     AND (array['United Kingdom'] <@ mixed_frequent_locations(location))
     AND (is_visible(social, flags) = TRUE)
ORDER BY "users"."created_at" ASC;
                                                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=77641.99..77652.36 rows=4148 width=1393) (actual time=1195.544..1195.546 rows=20 loops=1)
   Sort Key: created_at
   Sort Method: quicksort  Memory: 59kB
   ->  Bitmap Heap Scan on users  (cost=18046.48..77392.73 rows=4148 width=1393) (actual time=227.471..1195.481 rows=20 loops=1)
         Recheck Cond: (('{"United Kingdom"}'::text[] <@ mixed_frequent_locations(location)) AND (engagement_level(social) < 1))
         Filter: (is_visible(social, flags) AND (social_peemv(social) < '33.3333333333333'::double precision))
         Rows Removed by Filter: 19444
         Heap Blocks: exact=19238
         ->  BitmapAnd  (cost=18046.48..18046.48 rows=28415 width=0) (actual time=218.484..218.484 rows=0 loops=1)
               ->  Bitmap Index Scan on mixed_frequent_locations_idx  (cost=0.00..1356.36 rows=128634 width=0) (actual time=44.794..44.794 rows=108076 loops=1)
                     Index Cond: ('{"United Kingdom"}'::text[] <@ mixed_frequent_locations(location))
               ->  Bitmap Index Scan on idx_in_egagagement_level_and_created_at  (cost=0.00..16687.80 rows=1156662 width=0) (actual time=163.368..163.368 rows=801189 loops=1)
                     Index Cond: (engagement_level(social) < 1)
 Planning time: 3.326 ms
 Execution time: 1197.242 ms

Todas as condições aplicadas, exceto as is_visible, são dadas pelo usuário

postgresql optimization

2 respostas

  1. Best Answer

    Você tem um problema clássico de dependência de coluna. O PostgreSQL acredita que haverá 4.035 linhas que atendem à cláusula WHERE com base em estimativas. Se eu pegar as seletividades reais que você relatou e multiplicá-las (o que é correto apenas se cada uma for independente das outras), obterei 8032 linhas. Mas o verdadeiro valor é 20. Aparentemente, as pessoas do Reino Unido são anormalmente anti-sociais, ou algo assim.

    Com o LIMIT 12, ele pensa que apenas percorrerá o índice na mesma ordem do ORDER BY e parará quando encontrar 12 linhas que passem pelos filtros. Ele acha que precisará andar apenas cerca de 12/4035 do índice. Mas ele realmente precisa andar cerca de 12/20 do índice (a menos que também haja uma correlação entre a data de criação e a socialidade, então mesmo essas estimativas estariam erradas). Então ele escolhe esses métodos porque acha que eles serão mais rápidos.

    Nas versões mais recentes do PostgreSQL, você pode coletar estatísticas sobre dependências entre colunas, mas não pode fazer isso em funções/expressões, então não funcionaria para você. Eu não acho que funcionaria para colunas de matriz, de qualquer maneira.

    Se seus cortes fossem codificados, você poderia criar um índice parcial como o abaixo, mas parece que isso também não funcionará para você, a menos que você esteja disposto a comprometer isso.

    CREATE INDEX mixed_frequent_locations_idx_2 on users 
  USING GIN ( mixed_frequent_locations(location) )
  WHERE (engagement_level(social) < 1)
   AND (social_peemv(social) < 33.333333333333336)
   AND (is_visible(social, flags) = TRUE) ;

    Muitas vezes, você pode resolver o problema criando um índice de várias colunas com apenas a ordem certa de colunas, o que é tão bom para a consulta que é usado mesmo se as estimativas de seletividade estiverem longe. Mas uma vez que todas as condições seletivas são baseadas em desigualdade ou associação de matriz, não é óbvio como você poderia chegar a esse índice. É possível que você possa usar um índice GiST para isso. btree_gistfornece classes de operadores GiST para '<' nos números, mas não conheço nada que forneça suporte <@ em matrizes de strings. Você pode tentar um índice, USING GIST ON (engagement_level(social), social_peemv(social))mas como a maior parte de sua seletividade vem, mixed_frequent_locations(location)eu não teria grandes esperanças de que um índice sem isso funcionasse bem o suficiente para parecer melhor do que o plano ORDER BY indexado existente.

    Coloque tudo junto, e eu não acho que você tenha muitas ótimas opções aqui. Procurar seletivamente as consultas de problemas e remover seu LIMIT é provavelmente a melhor opção, por mais desagradável que seja. Em vez de remover o LIMIT, você pode apenas especificar o ORDER BY assim:

    ORDER BY "users"."created_at" + '0 seconds'::interval ASC

    Isso impedirá que o índice falsamente atraente seja usado, sem alterar a saída das consultas.

    • 1

  2. Na consulta rápida, você está retornando 0 linhas. Isso é fácil. O PostgreSQL vê social_peemv(social)e tem uma estimativa estatística suculenta para ele e faz a varredura idx_in_social_peemv. Ele retorna 0 linhas e a consulta é feita.

    No outro você está dizendo < 33.333333333333336. Como isso significa quase metade da tabela, o PostgreSQL descobre que o valor não vale o custo de acordo com as estimativas de seletividade. Em vez disso, ele verifica um índice diferente e remove 2816798 linhas de seu conjunto de resultados.

    Ainda não sabemos o que suas funções estão fazendo e não temos a definição da tabela. Você também não nos deu todos os índices created_at_idx.

    • 0

relate perguntas