469,111 Members | 1,987 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,111 developers. It's quick & easy.

How to avoid inlining subquery result columns


Postgresql 7.4b2 (approximately, compiled out of CVS)

When I have a subquery that has a complex subquery as one of the result
columns, and then that result column is used multiple times in the parent
query, the subquery is inlined for each one. This means multiple redundant
executions of the subquery.

I recall there was a way to defeat this optimization involving introducing an
extra subquery layer somewhere. But I'm failing to be able to reproduce that
now.

What do I have to do to avoid executing the subquery multiple times?
db=> explain
select n,n
from (
select (select count(*) from foo where foo_id = bar.foo_id) as n
from bar
where bar_id = 1
) as x
;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Index Scan using bar_pkey on bar (cost=0.00..9.62 rows=1 width=4) (actual time=0.41..0.42 rows=1 loops=1)
Index Cond: (bar_id = 1)
SubPlan
-> Aggregate (cost=3.21..3.21 rows=1 width=0) (actual time=0.10..0.10 rows=1 loops=1)
-> Index Scan using foo_pkey on foo (cost=0.00..3.21 rows=1 width=0) (actual time=0.06..0.07 rows=1 loops=1)
Index Cond: (foo_id = $0)
-> Aggregate (cost=3.21..3.21 rows=1 width=0) (actual time=0.14..0.14 rows=1 loops=1)
-> Index Scan using foo_pkey on foo (cost=0.00..3.21 rows=1 width=0) (actual time=0.06..0.08 rows=1 loops=1)
Index Cond: (foo_id = $0)
Total runtime: 1.31 msec
(10 rows)

--
greg
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #1
2 2644
Greg Stark <gs*****@mit.edu> writes:
I recall there was a way to defeat this optimization involving introducing an
extra subquery layer somewhere.


I think adding "OFFSET 0" to a subquery is the simplest way to prevent
it from being flattened.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #2
Greg Stark <gs*****@mit.edu> writes:
I recall there was a way to defeat this optimization involving introducing an
extra subquery layer somewhere.


I think adding "OFFSET 0" to a subquery is the simplest way to prevent
it from being flattened.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Raphael | last post: by
5 posts views Thread by steven.fafel | last post: by
15 posts views Thread by Lloyd Dupont | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.