Using PostgreSQL (8.4), I'm creating a view that summarizes various results from a few tables (e.g creating columns a, b, c in the view), and then I need to combine some of these results together in the same query (e.g. a+b, a-b, (a+b)/c, …), so as to produce the final results. What I'm noticing is that the intermediate results are fully computed each time they are used, even if it's done within the same query.
Is there a way to optimize this so as to avoid those same results to be computed every time?
Here is a simplified example that reproduces the problem.
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;
Here is a view that performs the most time-consuming operations:
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 testview1produces this plan (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 testview1produces this plan: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
Here, selecting a, a takes twice as long as selecting a, whereas they could really be computed just once. For example, with SELECT a, a+b, a-b FROM testview1, it goes through the sub-plan for a 3 times and through b twice, whereas the execution time could be reduced to 2/5 of the total time (assuming + and – are negligible here).
It's a good thing it doesn't compute the unused columns (b and c) when they're not needed, but is there a way to make it compute the same used columns from the view only once?
EDIT:
@Frank Heikens correctly suggested to use an index, which was missing in the example above. While it does improve the speed for each sub-plan, it doesn't prevent the same sub-query to be computed multiple times. Sorry, I should have put this in the initial question to make it clear.
Best Answer
(Apologies for answering my own question, but after reading this unrelated question and answer, it occurred to me I should try using a CTE instead. It works.)
Here is another view, similar to
testview1in the question, but that uses a Common Table Expression:(This is just an example, I'm not suggesting that combining a view and a CTE are a necessarily a good idea: a CTE might be enough.)
Unlike
testview1, the query plan forSELECT a FROM testview2now also computesbandc, which were ignored since unused intestview1:However, it doesn't re-compute the results that are used multiple times within the same query (which was the objective).
Unlike
testview1with whichSELECT a, a, a, a, atook 5 times longer thanSELECT a, hereSELECT a, a, a, a, a, b, c, a+b, a+c, b+c FROM testview2takes just as long asSELECT a FROM testview2orSELECT a, b, c FROM testview2. It only goes througha,bandconce: