How to avoid inlining subquery result columns

From: Greg Stark <gsstark(at)mit(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: How to avoid inlining subquery result columns
Date: 2003-09-11 16:24:20
Message-ID: 87isnzxphn.fsf@stark.dyndns.tv
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message darren 2003-09-11 16:34:25 Re: help with TCL function
Previous Message Bruce Momjian 2003-09-11 15:54:09 Re: A Question About Insertions -- Performance