Usando o PostgreSQL (8.4), estou criando uma visão que resume vários resultados de algumas tabelas (por exemplo, criando colunas a, b, cna visão) e, em seguida, preciso combinar alguns desses resultados na mesma consulta (por exemplo a+b, a-b, , (a+b)/c, ...), de modo a produzir os resultados finais. O que estou percebendo é que os resultados intermediários são totalmente computados cada vez que são usados, mesmo que seja feito dentro da mesma consulta.
Existe uma maneira de otimizar isso para evitar que os mesmos resultados sejam computados todas as vezes?
Aqui está um exemplo simplificado que reproduz o problema.
CREATE TABLE test1 (
id SERIAL PRIMARY KEY,
log_timestamp TIMESTAMP NOT NULL
);
CREATE TABLE test2 (
test1_id INTEGER NOT NULL REFERENCES test1(id),
category VARCHAR(10) NOT NULL,
col1 INTEGER,
col2 INTEGER
);
CREATE INDEX test_category_idx ON test2(category);
-- Added after edit to this question
CREATE INDEX test_id_idx ON test2(test1_id);
-- Populating with test data.
INSERT INTO test1(log_timestamp)
SELECT * FROM generate_series('2011-01-01'::timestamp, '2012-01-01'::timestamp, '1 hour');
INSERT INTO test2
SELECT id, substr(upper(md5(random()::TEXT)), 1, 1),
(20000*random()-10000)::int, (3000*random()-200)::int FROM test1;
INSERT INTO test2
SELECT id, substr(upper(md5(random()::TEXT)), 1, 1),
(2000*random()-1000)::int, (3000*random()-200)::int FROM test1;
INSERT INTO test2
SELECT id, substr(upper(md5(random()::TEXT)), 1, 1),
(2000*random()-40)::int, (3000*random()-200)::int FROM test1;
Aqui está uma exibição que executa as operações mais demoradas:
CREATE VIEW testview1 AS
SELECT
t1.id,
t1.log_timestamp,
(SELECT SUM(t2.col1) FROM test2 t2 WHERE t2.test1_id=t1.id AND category='A') AS a,
(SELECT SUM(t2.col2) FROM test2 t2 WHERE t2.test1_id=t1.id AND category='B') AS b,
(SELECT SUM(t2.col1 - t2.col2) FROM test2 t2 WHERE t2.test1_id=t1.id AND category='C') AS c
FROM test1 t1;
SELECT a FROM testview1produz este plano (viaEXPLAIN ANALYZE):Seq Scan on test1 t1 (cost=0.00..1787086.55 rows=8761 width=4) (actual time=12.877..10517.575 rows=8761 loops=1) SubPlan 1 -> Aggregate (cost=203.96..203.97 rows=1 width=4) (actual time=1.193..1.193 rows=1 loops=8761) -> Bitmap Heap Scan on test2 t2 (cost=36.49..203.95 rows=1 width=4) (actual time=1.109..1.177 rows=0 loops=8761) Recheck Cond: ((category)::text = 'A'::text) Filter: (test1_id = $0) -> Bitmap Index Scan on test_category_idx (cost=0.00..36.49 rows=1631 width=0) (actual time=0.414..0.414 rows=1631 loops=8761) Index Cond: ((category)::text = 'A'::text) Total runtime: 10522.346 ms
SELECT a, a FROM testview1produz este plano :Seq Scan on test1 t1 (cost=0.00..3574037.50 rows=8761 width=4) (actual time=3.343..20550.817 rows=8761 loops=1) SubPlan 1 -> Aggregate (cost=203.96..203.97 rows=1 width=4) (actual time=1.183..1.183 rows=1 loops=8761) -> Bitmap Heap Scan on test2 t2 (cost=36.49..203.95 rows=1 width=4) (actual time=1.100..1.166 rows=0 loops=8761) Recheck Cond: ((category)::text = 'A'::text) Filter: (test1_id = $0) -> Bitmap Index Scan on test_category_idx (cost=0.00..36.49 rows=1631 width=0) (actual time=0.418..0.418 rows=1631 loops=8761) Index Cond: ((category)::text = 'A'::text) SubPlan 2 -> Aggregate (cost=203.96..203.97 rows=1 width=4) (actual time=1.154..1.154 rows=1 loops=8761) -> Bitmap Heap Scan on test2 t2 (cost=36.49..203.95 rows=1 width=4) (actual time=1.083..1.143 rows=0 loops=8761) Recheck Cond: ((category)::text = 'A'::text) Filter: (test1_id = $0) -> Bitmap Index Scan on test_category_idx (cost=0.00..36.49 rows=1631 width=0) (actual time=0.426..0.426 rows=1631 loops=8761) Index Cond: ((category)::text = 'A'::text) Total runtime: 20557.581 ms
Aqui, selecionar a, aleva o dobro do tempo que selecionar a, enquanto na verdade eles podem ser calculados apenas uma vez. Por exemplo, com SELECT a, a+b, a-b FROM testview1, ele percorre o subplano a3 vezes e bduas vezes, enquanto o tempo de execução pode ser reduzido para 2/5 do tempo total (assumindo que + e - são insignificantes aqui).
É bom que ele não calcule as colunas não utilizadas ( be c) quando elas não são necessárias, mas existe uma maneira de fazê-lo calcular as mesmas colunas usadas da exibição apenas uma vez?
EDIT: @Frank Heikens sugeriu corretamente o uso de um índice, que estava faltando no exemplo acima. Embora melhore a velocidade de cada subplano, não impede que a mesma subconsulta seja computada várias vezes. Desculpe, eu deveria ter colocado isso na pergunta inicial para deixar claro.
(Peço desculpas por responder à minha própria pergunta, mas depois de ler esta pergunta e resposta não relacionadas , ocorreu-me que deveria tentar usar um CTE. Funciona.)
Aqui está outra visão, semelhante à
testview1da pergunta, mas que usa uma expressão de tabela comum :(Este é apenas um exemplo, não estou sugerindo que combinar uma exibição e um CTE seja necessariamente uma boa ideia: um CTE pode ser suficiente.)
Ao contrário
testview1de , o plano de consulta porSELECT a FROM testview2enquanto também calculabec, que foram ignorados desde então emtestview1:No entanto, ele não recalcula os resultados que são usados várias vezes na mesma consulta (que era o objetivo).
Ao contrário
testview1de whichSELECT a, a, a, a, alevou 5 vezes mais do queSELECT a, aquiSELECT a, a, a, a, a, b, c, a+b, a+c, b+c FROM testview2leva tanto tempo quantoSELECT a FROM testview2ouSELECT a, b, c FROM testview2. Ele só passa pora,becuma vez:Você precisa de um índice em test1_id na tabela test2, isso mudará as coisas.