Re: Query plan question, and a memory leak

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > Is there some way to force the optimizer not to substitute the subquery in the
> > where clause?
>
> You could try tinkering with the rules for invoking subquery_push_qual
> in src/backend/optimizer/path/allpaths.c. This might be a case that
> would fall under the note there wondering if pushing down can ever
> result in a worse plan. I'm not sure though that we can tell the
> difference reliably...

Indeed changing
select * from (select <subquery> as foo) where foo is not null
into
select * from (select <subquery> as foo) where (select foo) is not null

causes that code path to give up on inlining the subplan.

Thanks for the pointer to the part of the code involved.

Perhaps it should check not just whether the where clause involves a subplan
but also whether expression it's substituting involves a subplan? There may be
cases where it would be advantageous to inline a sub plan though, it just
seems like it wouldn't be the majority.

I guess in an ideal world the optimizer would consider both possibilities and
choose based on the cost. Does the optimizer only use the costs for choosing
join orders and methods and not for deciding whether to make other
transformations?

--
greg