Início / dba / Perguntas / 54388
Accepted
Iain Samuel McLean Elder
Asked: 2013-12-04 15:07:58 +0800 CST

Por que os planos são diferentes se as consultas são logicamente semelhantes?

  • 772

Escrevi duas funções para responder à primeira pergunta de dever de casa do dia 3 de Seven Databases in Seven Weeks .

Crie um procedimento armazenado onde você pode inserir o título de um filme ou o nome do ator de sua preferência, e ele retornará as cinco principais sugestões com base nos filmes em que o ator estrelou ou em filmes com gêneros semelhantes.

Minha primeira tentativa é correta, mas lenta. Pode levar até 2000 ms para retornar um resultado.

CREATE OR REPLACE FUNCTION suggest_movies(IN query text, IN result_limit integer DEFAULT 5)
  RETURNS TABLE(movie_id integer, title text) AS
$BODY$
WITH suggestions AS (

  SELECT
    actors.name AS entity_term,
    movies.movie_id AS suggestion_id,
    movies.title AS suggestion_title,
    1 AS rank
  FROM actors
  INNER JOIN movies_actors ON (actors.actor_id = movies_actors.actor_id)
  INNER JOIN movies ON (movies.movie_id = movies_actors.movie_id)

  UNION ALL

  SELECT
    searches.title AS entity_term,
    suggestions.movie_id AS suggestion_id,
    suggestions.title AS suggestion_title,
    RANK() OVER (PARTITION BY searches.movie_id ORDER BY cube_distance(searches.genre, suggestions.genre)) AS rank
  FROM movies AS searches
  INNER JOIN movies AS suggestions ON
    (searches.movie_id <> suggestions.movie_id) AND
    (cube_enlarge(searches.genre, 2, 18) @> suggestions.genre)
)
SELECT suggestion_id, suggestion_title
FROM suggestions
WHERE entity_term = query
ORDER BY rank, suggestion_id
LIMIT result_limit;
$BODY$
LANGUAGE sql;

Minha segunda tentativa é correta e rápida. Eu o otimizei empurrando o filtro do CTE para baixo em cada parte da união.

Eu removi esta linha da consulta externa:

WHERE entity_term = query

Eu adicionei esta linha à primeira consulta interna:

WHERE actors.name = query

Eu adicionei esta linha à segunda consulta interna:

WHERE movies.title = query

A segunda função leva cerca de 10ms para retornar o mesmo resultado.

Nada difere no banco de dados além das definições de função.

Por que o PostgreSQL produz planos tão diferentes para essas duas consultas logicamente equivalentes?

O EXPLAIN ANALYZEplano da primeira função fica assim:

                                                                                       Limit  (cost=7774.18..7774.19 rows=5 width=44) (actual time=1738.566..1738.567 rows=5 loops=1)
   CTE suggestions
     ->  Append  (cost=332.56..7337.19 rows=19350 width=285) (actual time=7.113..1577.823 rows=383024 loops=1)
           ->  Subquery Scan on "*SELECT* 1"  (cost=332.56..996.80 rows=11168 width=33) (actual time=7.113..22.258 rows=11168 loops=1)
                 ->  Hash Join  (cost=332.56..885.12 rows=11168 width=33) (actual time=7.110..19.850 rows=11168 loops=1)
                       Hash Cond: (movies_actors.movie_id = movies.movie_id)
                       ->  Hash Join  (cost=143.19..514.27 rows=11168 width=18) (actual time=4.326..11.938 rows=11168 loops=1)
                             Hash Cond: (movies_actors.actor_id = actors.actor_id)
                             ->  Seq Scan on movies_actors  (cost=0.00..161.68 rows=11168 width=8) (actual time=0.013..1.648 rows=11168 loops=1)
                             ->  Hash  (cost=80.86..80.86 rows=4986 width=18) (actual time=4.296..4.296 rows=4986 loops=1)
                                   Buckets: 1024  Batches: 1  Memory Usage: 252kB
                                   ->  Seq Scan on actors  (cost=0.00..80.86 rows=4986 width=18) (actual time=0.009..1.681 rows=4986 loops=1)
                       ->  Hash  (cost=153.61..153.61 rows=2861 width=19) (actual time=2.768..2.768 rows=2861 loops=1)
                             Buckets: 1024  Batches: 1  Memory Usage: 146kB
                             ->  Seq Scan on movies  (cost=0.00..153.61 rows=2861 width=19) (actual time=0.003..1.197 rows=2861 loops=1)
           ->  Subquery Scan on "*SELECT* 2"  (cost=6074.48..6340.40 rows=8182 width=630) (actual time=1231.324..1528.188 rows=371856 loops=1)
                 ->  WindowAgg  (cost=6074.48..6258.58 rows=8182 width=630) (actual time=1231.324..1492.106 rows=371856 loops=1)
                       ->  Sort  (cost=6074.48..6094.94 rows=8182 width=630) (actual time=1231.307..1282.550 rows=371856 loops=1)
                             Sort Key: searches.movie_id, (cube_distance(searches.genre, suggestions_1.genre))
                             Sort Method: external sort  Disk: 21584kB
                             ->  Nested Loop  (cost=0.27..3246.72 rows=8182 width=630) (actual time=0.047..909.096 rows=371856 loops=1)
                                   ->  Seq Scan on movies searches  (cost=0.00..153.61 rows=2861 width=315) (actual time=0.003..0.676 rows=2861 loops=1)
                                   ->  Index Scan using movies_genres_cube on movies suggestions_1  (cost=0.27..1.05 rows=3 width=315) (actual time=0.016..0.277 rows=130 loops=2861)
                                         Index Cond: (cube_enlarge(searches.genre, 2::double precision, 18) @> genre)
                                         Filter: (searches.movie_id <> movie_id)
                                         Rows Removed by Filter: 1
   ->  Sort  (cost=436.99..437.23 rows=97 width=44) (actual time=1738.565..1738.566 rows=5 loops=1)
         Sort Key: suggestions.rank, suggestions.suggestion_id
         Sort Method: top-N heapsort  Memory: 25kB
         ->  CTE Scan on suggestions  (cost=0.00..435.38 rows=97 width=44) (actual time=1281.905..1738.531 rows=43 loops=1)
               Filter: (entity_term = 'Die Hard'::text)
               Rows Removed by Filter: 382981
 Total runtime: 1746.623 ms

O EXPLAIN ANALYZEplano da segunda consulta fica assim:

 Limit  (cost=43.74..43.76 rows=5 width=44) (actual time=1.231..1.234 rows=5 loops=1)
   CTE suggestions
     ->  Append  (cost=4.86..43.58 rows=5 width=391) (actual time=1.029..1.141 rows=43 loops=1)
           ->  Subquery Scan on "*SELECT* 1"  (cost=4.86..20.18 rows=2 width=33) (actual time=0.047..0.047 rows=0 loops=1)
                 ->  Nested Loop  (cost=4.86..20.16 rows=2 width=33) (actual time=0.047..0.047 rows=0 loops=1)
                       ->  Nested Loop  (cost=4.58..19.45 rows=2 width=18) (actual time=0.045..0.045 rows=0 loops=1)
                             ->  Index Scan using actors_name on actors  (cost=0.28..8.30 rows=1 width=18) (actual time=0.045..0.045 rows=0 loops=1)
                                   Index Cond: (name = 'Die Hard'::text)
                             ->  Bitmap Heap Scan on movies_actors  (cost=4.30..11.13 rows=2 width=8) (never executed)
                                   Recheck Cond: (actor_id = actors.actor_id)
                                   ->  Bitmap Index Scan on movies_actors_actor_id  (cost=0.00..4.30 rows=2 width=0) (never executed)
                                         Index Cond: (actor_id = actors.actor_id)
                       ->  Index Scan using movies_pkey on movies  (cost=0.28..0.35 rows=1 width=19) (never executed)
                             Index Cond: (movie_id = movies_actors.movie_id)
           ->  Subquery Scan on "*SELECT* 2"  (cost=23.31..23.40 rows=3 width=630) (actual time=0.982..1.081 rows=43 loops=1)
                 ->  WindowAgg  (cost=23.31..23.37 rows=3 width=630) (actual time=0.982..1.064 rows=43 loops=1)
                       ->  Sort  (cost=23.31..23.31 rows=3 width=630) (actual time=0.963..0.971 rows=43 loops=1)
                             Sort Key: searches.movie_id, (cube_distance(searches.genre, suggestions_1.genre))
                             Sort Method: quicksort  Memory: 28kB
                             ->  Nested Loop  (cost=4.58..23.28 rows=3 width=630) (actual time=0.808..0.916 rows=43 loops=1)
                                   ->  Index Scan using movies_title on movies searches  (cost=0.28..8.30 rows=1 width=315) (actual time=0.025..0.027 rows=1 loops=1)
                                         Index Cond: (title = 'Die Hard'::text)
                                   ->  Bitmap Heap Scan on movies suggestions_1  (cost=4.30..14.95 rows=3 width=315) (actual time=0.775..0.844 rows=43 loops=1)
                                         Recheck Cond: (cube_enlarge(searches.genre, 2::double precision, 18) @> genre)
                                         Filter: (searches.movie_id <> movie_id)
                                         Rows Removed by Filter: 1
                                         ->  Bitmap Index Scan on movies_genres_cube  (cost=0.00..4.29 rows=3 width=0) (actual time=0.750..0.750 rows=44 loops=1)
                                               Index Cond: (cube_enlarge(searches.genre, 2::double precision, 18) @> genre)
   ->  Sort  (cost=0.16..0.17 rows=5 width=44) (actual time=1.230..1.231 rows=5 loops=1)
         Sort Key: suggestions.rank, suggestions.suggestion_id
         Sort Method: top-N heapsort  Memory: 25kB
         ->  CTE Scan on suggestions  (cost=0.00..0.10 rows=5 width=44) (actual time=1.034..1.187 rows=43 loops=1)
 Total runtime: 1.410 ms
postgresql optimization

2 respostas

  1. Best Answer

    Nenhum pushdown de predicado automático para CTEs

    O PostgreSQL 9.3 não faz pushdown de predicado para CTEs.

    Um otimizador que faz pushdown de predicado pode mover cláusulas where para consultas internas. O objetivo é filtrar dados irrelevantes o mais cedo possível. Contanto que a nova consulta seja logicamente equivalente, o mecanismo ainda busca todos os dados relevantes, produzindo o resultado correto, apenas mais rapidamente.

    O desenvolvedor do núcleo, Tom Lane, faz alusão à dificuldade de determinar a equivalência lógica na lista de discussão pgsql-performance .

    CTEs também são tratados como cercas de otimização; isso não é tanto uma limitação do otimizador quanto para manter a semântica sã quando o CTE contém uma consulta gravável.

    O otimizador não distingue CTEs somente leitura de graváveis, portanto, é excessivamente conservador ao considerar planos. O tratamento 'fence' impede que o otimizador mova a cláusula where dentro do CTE, embora possamos ver que é seguro fazê-lo.

    Podemos esperar que a equipe do PostgreSQL melhore a otimização do CTE, mas, por enquanto, para obter um bom desempenho, você precisa mudar seu estilo de escrita.

    Reescrever para desempenho

    A pergunta já mostra uma maneira de obter um plano melhor. A duplicação da condição do filtro basicamente codifica permanentemente o efeito do pushdown de predicado.

    Em ambos os planos, o mecanismo copia as linhas de resultado para uma tabela de trabalho para poder classificá-las. Quanto maior a tabela de trabalho, mais lenta a consulta.

    O primeiro plano copia todas as linhas nas tabelas base para a tabela de trabalho e verifica isso para encontrar o resultado. Para tornar as coisas ainda mais lentas, o mecanismo deve verificar toda a tabela de trabalho porque não possui índices.

    Isso é uma quantidade ridícula de trabalho desnecessário. Ele lê todos os dados nas tabelas base duas vezes para encontrar a resposta, quando há apenas 5 linhas correspondentes estimadas em 19.350 linhas estimadas nas tabelas base.

    O segundo plano usa os índices para localizar as linhas correspondentes e copia apenas essas para a tabela de trabalho. O índice filtrou efetivamente os dados para nós.

    Na página 85 de The Art of SQL, Stéphane Faroult nos lembra das expectativas do usuário.

    Em grande parte, os usuários finais ajustam sua paciência ao número de linhas que esperam: quando pedem uma agulha, prestam pouca atenção ao tamanho do palheiro.

    O segundo plano escala com a agulha, portanto, é mais provável que mantenha seus usuários satisfeitos.

    Reescrever para manutenção

    A nova consulta é mais difícil de manter porque você pode introduzir um defeito alterando uma expressão de filtro, mas não a outra.

    Não seria ótimo se pudéssemos escrever tudo apenas uma vez e ainda obter um bom desempenho?

    Podemos. O otimizador faz pushdown de predicado para subqeries.

    Um exemplo mais simples é mais fácil de explicar.

    CREATE TABLE a (c INT);

CREATE TABLE b (c INT);

CREATE INDEX a_c ON a(c);

CREATE INDEX b_c ON b(c);

INSERT INTO a SELECT 1 FROM generate_series(1, 1000000);

INSERT INTO b SELECT 2 FROM a;

INSERT INTO a SELECT 3;

    Isso cria duas tabelas, cada uma com uma coluna indexada. Juntos, eles contêm um milhão de 1s, um milhão de 2s e um 3.

    Você pode encontrar a agulha 3usando qualquer uma dessas consultas.

    -- CTE
EXPLAIN ANALYZE
WITH cte AS (
  SELECT c FROM a
  UNION ALL
  SELECT c FROM b
)
SELECT c FROM cte WHERE c = 3;

-- Subquery
EXPLAIN ANALYZE
SELECT c
FROM (
  SELECT c FROM a
  UNION ALL
  SELECT c FROM b
) AS subquery
WHERE c = 3;

    O plano para o CTE é lento. O mecanismo verifica três tabelas e lê cerca de quatro milhões de linhas. Demora cerca de 1000 milissegundos.

    CTE Scan on cte  (cost=33275.00..78275.00 rows=10000 width=4) (actual time=471.412..943.225 rows=1 loops=1)
  Filter: (c = 3)
  Rows Removed by Filter: 2000000
  CTE cte
    ->  Append  (cost=0.00..33275.00 rows=2000000 width=4) (actual time=0.011..409.573 rows=2000001 loops=1)
          ->  Seq Scan on a  (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.010..114.869 rows=1000001 loops=1)
          ->  Seq Scan on b  (cost=0.00..18850.00 rows=1000000 width=4) (actual time=5.530..104.674 rows=1000000 loops=1)
Total runtime: 948.594 ms

    O plano para a subconsulta é rápido. O mecanismo apenas procura cada índice. Leva menos de um milissegundo.

    Append  (cost=0.42..8.88 rows=2 width=4) (actual time=0.021..0.038 rows=1 loops=1)
  ->  Index Only Scan using a_c on a  (cost=0.42..4.44 rows=1 width=4) (actual time=0.020..0.021 rows=1 loops=1)
        Index Cond: (c = 3)
        Heap Fetches: 1
  ->  Index Only Scan using b_c on b  (cost=0.42..4.44 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=1)
        Index Cond: (c = 3)
        Heap Fetches: 0
Total runtime: 0.065 ms

    Veja SQLFiddle para uma versão interativa.

    • 26

  2. Os planos são os mesmos no Postgres 12

    A pergunta feita sobre o Postgres 9.3. Cinco anos depois, essa versão está obsoleta, mas o que mudou?

    O PostgreSQL 12 agora inline CTEs como estes.

    Consultas WITH embutidas (expressões de tabela comuns)

    Expressões de tabela comuns (também conhecidas como WITHconsultas) agora podem ser incorporadas automaticamente em uma consulta se a) não forem recursivas, b) não tiverem efeitos colaterais ec) forem referenciadas apenas uma vez em uma parte posterior de uma consulta. Isso remove uma "cerca de otimização" que existe desde a introdução da WITHcláusula no PostgreSQL 8.4

    Se necessário, você pode forçar uma consulta WITH a se materializar usando a cláusula MATERIALIZED, por exemplo

    WITH c AS MATERIALIZED ( SELECT * FROM a WHERE a.x % 4 = 0 ) SELECT * FROM c JOIN d ON d.y = a.x;
    • 5

relate perguntas