By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,660 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,660 IT Pros & Developers. It's quick & easy.

How to avoid inlining subquery result columns

P: n/a

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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.