From: Craig Ringer <craig@postnewspapers.com.au>
To: pgsql-performance@postgresql.org
Subject: Repeated execution of identical subqueries
Date: Thu, 13 Mar 2008 12:37:53 +0900
Message-ID: <47D8A191.6000303@postnewspapers.com.au> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig@postnewspapers.com.au
Subject: Re: Repeated execution of identical subqueries
In-Reply-To: <47D8A191.6000303@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox