Skip site navigation (1) Skip section navigation (2)

Repeated execution of identical subqueries

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: pgsql-performance(at)postgresql(dot)org
Subject: Repeated execution of identical subqueries
Date: 2008-03-13 03:37:53
Message-ID: 47D8A191.6000303@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-performance
Hi

I've been wondering about postgresql's handling of repeated subqueries 
in statements for a while, and thought I'd ask here.

If the exact same subquery appears in multiple places in a complex 
query, it seems to be executed separately each time it appears. I'm 
wondering if there's any way, without moving over to PL/PgSQL, to 
"collapse" such multiple executions down to a single one.

Consider this simplistic example, which while useless demonstrates the 
issue in a self contained way:

explain analyze select (select count(id) from booking) as x
where (select count(id) from booking) > 100;

                                                           QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
  Result  (cost=37023.85..37023.86 rows=1 width=0)
          (actual time=668.922..668.922 rows=1 loops=1)
    One-Time Filter: ($1 > 100)
    InitPlan
      ->  Aggregate  (cost=18511.92..18511.92 rows=1 width=4)
                     (actual time=335.150..335.150 rows=1 loops=1)
            ->  Seq Scan on booking  (cost=0.00..17627.13 rows=353913
                width=4) (actual time=0.128..200.147 rows=353913 loops=1)
      ->  Aggregate  (cost=18511.92..18511.92 rows=1 width=4)
                     (actual time=333.756..333.756 rows=1 loops=1)
            ->  Seq Scan on booking  (cost=0.00..17627.13 rows=353913
                width=4) (actual time=0.133..198.261 rows=353913 loops=1)
  Total runtime: 668.993 ms
(8 rows)


The query:

(select count(id) from booking)

is executed twice, even though it's guaranteed by MVCC that the result 
will be the same in both subqueries.

Ideally you'd be able to say something like:

select (select count(id) from booking) as x
where x > 100;

I realize that in this case the query can be rewritten as:

select x.c from (select count(id) AS c from booking) as x
where x.c > 100;


but in more complex queries introducing an additional FROM clause for a 
  single value can be undesirable and/or ugly.

Is there any way to get postgresql to detect such repeated query parts 
and evaluate them only once?

--
Craig Ringer

Responses

pgsql-performance by date

Next:From: Craig JamesDate: 2008-03-13 04:55:18
Subject: Benchmark: Dell/Perc 6, 8 disk RAID 10
Previous:From: Merlin MoncureDate: 2008-03-12 20:55:26
Subject: Re: Are piped columns indexable

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group