Início / dba / Perguntas / 25622
Accepted
Bruno
Asked: 2012-10-09 09:51:35 +0800 CST

Otimizando a consulta na exibição que mescla tabelas semelhantes com um discriminador claro

  • 772

Utilizando o PostgreSQL 8.4, tenho uma série de tabelas que possuem uma estrutura muito parecida, mas que pertencem a categorias diferentes:

CREATE TABLE table_a (
    id SERIAL PRIMARY KEY,
    event_time TIMESTAMP WITH TIME ZONE NOT NULL,
    value_a REAL
);

CREATE TABLE table_b (
    id SERIAL PRIMARY KEY,
    event_time TIMESTAMP WITH TIME ZONE NOT NULL,
    value_b REAL
);

CREATE TABLE table_c (
    id SERIAL PRIMARY KEY,
    event_time TIMESTAMP WITH TIME ZONE NOT NULL,
    value_c REAL
);

Preciso vincular esses valores a uma tabela central (usando joins ou sub-selects dependendo da consulta):

CREATE TABLE periods_table (
    id SERIAL PRIMARY KEY,
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    category TEXT NOT NULL
);

Aqui, categoryé um de 'Category A', 'Category B'ou 'Category C'.

Para abstrair as semelhanças entre as tabelas A, B e C, criei uma view:

CREATE VIEW table_values AS
    SELECT 'Category A' AS category, event_time, value_a AS value
        FROM table_a
    UNION
    SELECT 'Category B' AS category, event_time, value_b AS value
        FROM table_b
    UNION
    SELECT 'Category C' AS category, event_time, value_c AS value
        FROM table_c;

Uma consulta típica seria algo como:

SELECT p.start_time, p.end_time, p.category,
       (SELECT SUM(v.value) FROM table_values v
           WHERE v.category=p.category
             AND v.event_time >= t.start_time AND v.event_time < t.end_time)
    FROM periods_table p

O problema é que a categorycoluna que poderia ser usada para discriminar entre as tabelas separadas na exibição é usada apenas no final.

Mesmo um EXPLAIN ANALYZEon SELECT * FROM table_values WHERE category='Category A'mostra que todas as 3 tabelas são subconsultadas quando as linhas que correspondem a esse critério vêm apenas de table_a:

                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan table_values  (cost=176.02..295.50 rows=27 width=44) (actual time=0.135..0.135 rows=0 loops=1)
   Filter: (table_values.category = 'Category A'::text)
   ->  HashAggregate  (cost=176.02..229.12 rows=5310 width=12) (actual time=0.119..0.119 rows=0 loops=1)
         ->  Append  (cost=0.00..136.20 rows=5310 width=12) (actual time=0.089..0.089 rows=0 loops=1)
               ->  Subquery Scan "*SELECT* 1"  (cost=0.00..45.40 rows=1770 width=12) (actual time=0.025..0.025 rows=0 loops=1)
                     ->  Seq Scan on table_a  (cost=0.00..27.70 rows=1770 width=12) (actual time=0.010..0.010 rows=0 loops=1)
               ->  Subquery Scan "*SELECT* 2"  (cost=0.00..45.40 rows=1770 width=12) (actual time=0.020..0.020 rows=0 loops=1)
                     ->  Seq Scan on table_b  (cost=0.00..27.70 rows=1770 width=12) (actual time=0.006..0.006 rows=0 loops=1)
               ->  Subquery Scan "*SELECT* 3"  (cost=0.00..45.40 rows=1770 width=12) (actual time=0.020..0.020 rows=0 loops=1)
                     ->  Seq Scan on table_c  (cost=0.00..27.70 rows=1770 width=12) (actual time=0.006..0.006 rows=0 loops=1)
 Total runtime: 0.437 ms
(11 rows)

(Não há dados nesta tabela fictícia, mas as tabelas reais têm cerca de 300.000 linhas. SELECT * FROM table_values WHERE category='Category A'Leva cerca de 15 vezes mais do que SELECT * FROM table_a, embora sejam mais ou menos iguais.)

Existem índices event_timeem cada tabela, mas como não pode haver um índice na exibição category, isso não ajuda. Também tentei substituir a exibição por um CTE (já que às vezes leva a um caminho de consulta diferente), mas não ajudou.

Considerando que não posso realmente alterar as tabelas existentes, existe uma maneira de "mesclar" algumas tabelas como essas que levariam a consultas mais rápidas?

EDIT: consulta semelhante em dados reais. (Na verdade, existem 5 tabelas semelhantes aqui.)

Curiosamente, embora eu esteja consultando "Categoria E" aqui, há uma chave de classificação em "Categoria A" que não vem de nenhum lugar especificamente na consulta (acho que deve vir da primeira seleção na exibição, ou talvez apenas usa o valor da primeira seleção para indicar o nome da coluna).

EXPLAIN ANALYZE SELECT * FROM table_values WHERE category='Category E':

Subquery Scan table_values  (cost=1573543.53..1755714.30 rows=40482 width=44) (actual time=221030.235..221234.162 rows=317676 loops=1)
  Filter: (table_values.category = 'Category E'::text)
  ->  Unique  (cost=1573543.53..1654508.32 rows=8096479 width=12) (actual time=212999.276..220240.297 rows=8097555 loops=1)
        ->  Sort  (cost=1573543.53..1593784.72 rows=8096479 width=12) (actual time=212999.275..218561.085 rows=8097555 loops=1)
              Sort Key: ('Category A'::text), "*SELECT* 1".event_time, "*SELECT* 1".value
              Sort Method:  external merge  Disk: 300792kB"
              ->  Append  (cost=0.00..229411.58 rows=8096479 width=12) (actual time=0.014..4683.734 rows=8097555 loops=1)
                    ->  Subquery Scan "*SELECT* 1"  (cost=0.00..80689.62 rows=2847831 width=12) (actual time=0.014..954.326 rows=2847951 loops=1)
                          ->  Seq Scan on table_a  (cost=0.00..52211.31 rows=2847831 width=12) (actual time=0.010..607.528 rows=2847951 loops=1)
                    ->  Subquery Scan "*SELECT* 2"  (cost=0.00..29304.52 rows=1033976 width=12) (actual time=9.738..576.803 rows=1034928 loops=1)
                          ->  Seq Scan on table_b  (cost=0.00..18964.76 rows=1033976 width=12) (actual time=9.737..450.619 rows=1034928 loops=1)
                    ->  Subquery Scan "*SELECT* 3"  (cost=0.00..30463.22 rows=1075161 width=12) (actual time=15.100..720.983 rows=1075157 loops=1)
                          ->  Seq Scan on table_c  (cost=0.00..19711.61 rows=1075161 width=12) (actual time=15.099..592.070 rows=1075157 loops=1)
                    ->  Subquery Scan "*SELECT* 4"  (cost=0.00..79952.70 rows=2821835 width=12) (actual time=20.098..1794.739 rows=2821843 loops=1)
                          ->  Seq Scan on table_d  (cost=0.00..51734.35 rows=2821835 width=12) (actual time=20.097..1441.719 rows=2821843 loops=1)
                    ->  Subquery Scan "*SELECT* 5"  (cost=0.00..9001.52 rows=317676 width=12) (actual time=0.016..108.768 rows=317676 loops=1)
                          ->  Seq Scan on table_e  (cost=0.00..5824.76 rows=317676 width=12) (actual time=0.016..69.732 rows=317676 loops=1)
Total runtime: 221299.573 ms

EXPLAIN ANALYZE SELECT * FROM table_e:

Seq Scan on table_e  (cost=0.00..5824.76 rows=317676 width=12) (actual time=0.025..54.143 rows=317676 loops=1)
Total runtime: 67.624 ms
postgresql optimization

1 respostas

  1. Best Answer

    Há alguns problemas com sua consulta. É bastante claro que sua visualização 'wrapper' - embora pareça uma solução elegante a princípio - mata o desempenho envolvendo 7,7 milhões de linhas completamente desnecessárias. Isso ocorre porque UNIONexige que todos esses dados sejam classificados e, como eles não cabem na memória (você pode ver isso em Sort Method: external merge Disk: 300792kB), eles são 'trocados' para o disco e classificados lá, o que é um processo muito lento.

    Como primeira tentativa, tente recriar a visualização 'wrapper' com UNION ALLem vez de simples UNION(você pode encontrar a diferença aqui - observe que, para tornar as linhas distintas, o Postgres precisa classificá-las primeiro); desta forma você pode evitar o tipo. Se o resultado não for bom o suficiente, tente juntar as cinco tabelas na consulta 'principal' uma a uma e UNION ALLos resultados.

    • 3

relate perguntas