Message-ID: <47D8A191.6000303@postnewspapers.com.au>
Date: Thu, 13 Mar 2008 12:37:53 +0900
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Repeated execution of identical subqueries
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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