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