Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 testview1 produces this plan (via EXPLAIN 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 testview1 produces 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.

share|improve this question
add comment

2 Answers

You need an index on test1_id in table test2, that will change things.

Seq Scan on test1 t1  (cost=0.00..301450.63 rows=8761 width=12) (actual time=0.108..229.859 rows=8761 loops=1)
  SubPlan 1
    ->  Aggregate  (cost=11.45..11.46 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=8761)
          ->  Bitmap Heap Scan on test2 t2  (cost=3.27..11.45 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=8761)
                Recheck Cond: (test1_id = t1.id)
                Filter: ((category)::text = 'A'::text)
                ->  Bitmap Index Scan on idx_id  (cost=0.00..3.27 rows=3 width=0) (actual time=0.003..0.003 rows=3 loops=8761)
                      Index Cond: (test1_id = t1.id)
  SubPlan 2
    ->  Aggregate  (cost=11.45..11.46 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=8761)
          ->  Bitmap Heap Scan on test2 t2  (cost=3.27..11.45 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=8761)
                Recheck Cond: (test1_id = t1.id)
                Filter: ((category)::text = 'B'::text)
                ->  Bitmap Index Scan on idx_id  (cost=0.00..3.27 rows=3 width=0) (actual time=0.003..0.003 rows=3 loops=8761)
                      Index Cond: (test1_id = t1.id)
  SubPlan 3
    ->  Aggregate  (cost=11.46..11.47 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=8761)
          ->  Bitmap Heap Scan on test2 t2  (cost=3.27..11.45 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=8761)
                Recheck Cond: (test1_id = t1.id)
                Filter: ((category)::text = 'C'::text)
                ->  Bitmap Index Scan on idx_id  (cost=0.00..3.27 rows=3 width=0) (actual time=0.003..0.003 rows=3 loops=8761)
                      Index Cond: (test1_id = t1.id)
Total runtime: 232.419 ms
share|improve this answer
    
Thank you, this does improve the performance indeed, but my actual table already has that corresponding index in fact. The initial problem is still present: SELECT a, a, a, a, a FROM testview1 still takes 5 times longer than SELECT a FROM testview1. –  Bruno Dec 12 '11 at 1:37
add comment
up vote 5 down vote accepted

(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 testview1 in the question, but that uses a Common Table Expression:

CREATE VIEW testview2 AS
    WITH testcte 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 * FROM testcte;

(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 for SELECT a FROM testview2 now also computes b and c, which were ignored since unused in testview1:

Subquery Scan testview2  (cost=395272.42..395535.25 rows=8761 width=8) (actual time=0.256..607.941 rows=8761 loops=1)
   ->  CTE Scan on testcte  (cost=395272.42..395447.64 rows=8761 width=36) (actual time=0.255..604.106 rows=8761 loops=1)
         CTE testcte
           ->  Seq Scan on test1 t1  (cost=0.00..395272.42 rows=8761 width=12) (actual time=0.252..589.358 rows=8761 loops=1)
                 SubPlan 1
                   ->  Aggregate  (cost=15.02..15.03 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=8761)
                         ->  Bitmap Heap Scan on test2 t2  (cost=4.28..15.02 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=8761)
                               Recheck Cond: (test1_id = $0)
                               Filter: ((category)::text = 'A'::text)
                               ->  Bitmap Index Scan on test_if_idx  (cost=0.00..4.28 rows=3 width=0) (actual time=0.009..0.009 rows=3 loops=8761)
                                     Index Cond: (test1_id = $0)
                 SubPlan 2
                   ->  Aggregate  (cost=15.02..15.03 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=8761)
                         ->  Bitmap Heap Scan on test2 t2  (cost=4.28..15.02 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=8761)
                               Recheck Cond: (test1_id = $0)
                               Filter: ((category)::text = 'B'::text)
                               ->  Bitmap Index Scan on test_if_idx  (cost=0.00..4.28 rows=3 width=0) (actual time=0.007..0.007 rows=3 loops=8761)
                                     Index Cond: (test1_id = $0)
                 SubPlan 3
                   ->  Aggregate  (cost=15.02..15.04 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=8761)
                         ->  Bitmap Heap Scan on test2 t2  (cost=4.28..15.02 rows=1 width=8) (actual time=0.013..0.014 rows=0 loops=8761)
                               Recheck Cond: (test1_id = $0)
                               Filter: ((category)::text = 'C'::text)
                               ->  Bitmap Index Scan on test_if_idx  (cost=0.00..4.28 rows=3 width=0) (actual time=0.007..0.007 rows=3 loops=8761)
                                     Index Cond: (test1_id = $0)

However, it doesn't re-compute the results that are used multiple times within the same query (which was the objective).

Unlike testview1 with which SELECT a, a, a, a, a took 5 times longer than SELECT a, here SELECT a, a, a, a, a, b, c, a+b, a+c, b+c FROM testview2 takes just as long as SELECT a FROM testview2 or SELECT a, b, c FROM testview2. It only goes through a, b and c once:

 Subquery Scan testview2  (cost=395272.42..395600.96 rows=8761 width=24) (actual time=0.147..562.790 rows=8761 loops=1)
   ->  CTE Scan on testcte  (cost=395272.42..395447.64 rows=8761 width=36) (actual time=0.144..554.194 rows=8761 loops=1)
         CTE testcte
           ->  Seq Scan on test1 t1  (cost=0.00..395272.42 rows=8761 width=12) (actual time=0.140..542.657 rows=8761 loops=1)
                 SubPlan 1
                   ->  Aggregate  (cost=15.02..15.03 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=8761)
                         ->  Bitmap Heap Scan on test2 t2  (cost=4.28..15.02 rows=1 width=4) (actual time=0.012..0.013 rows=0 loops=8761)
                               Recheck Cond: (test1_id = $0)
                               Filter: ((category)::text = 'A'::text)
                               ->  Bitmap Index Scan on test_if_idx  (cost=0.00..4.28 rows=3 width=0) (actual time=0.007..0.007 rows=3 loops=8761)
                                     Index Cond: (test1_id = $0)
                 SubPlan 2
                   ->  Aggregate  (cost=15.02..15.03 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=8761)
                         ->  Bitmap Heap Scan on test2 t2  (cost=4.28..15.02 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=8761)
                               Recheck Cond: (test1_id = $0)
                               Filter: ((category)::text = 'B'::text)
                               ->  Bitmap Index Scan on test_if_idx  (cost=0.00..4.28 rows=3 width=0) (actual time=0.006..0.006 rows=3 loops=8761)
                                     Index Cond: (test1_id = $0)
                 SubPlan 3
                   ->  Aggregate  (cost=15.02..15.04 rows=1 width=8) (actual time=0.018..0.019 rows=1 loops=8761)
                         ->  Bitmap Heap Scan on test2 t2  (cost=4.28..15.02 rows=1 width=8) (actual time=0.012..0.012 rows=0 loops=8761)
                               Recheck Cond: (test1_id = $0)
                               Filter: ((category)::text = 'C'::text)
                               ->  Bitmap Index Scan on test_if_idx  (cost=0.00..4.28 rows=3 width=0) (actual time=0.007..0.007 rows=3 loops=8761)
                                     Index Cond: (test1_id = $0)
share|improve this answer
1  
No need to apologise! :-) In fact there is a badge Self Learner for answering your own questions. –  Gaius Dec 12 '11 at 10:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.