Tom Lane <tgl@sss.pgh.pa.us> writes:
> Greg Stark <gsstark@mit.edu> writes:
> > However I tested those queries with some data and things really do seem to be
> > behaving oddly. It takes nearly twice as long to run the version with the
> > where clause and duplicate subplan.
>
> Yes, they will both be executed --- there's no
> duplicate-subexpression-elimination logic...
But, but, there was no common subexpression in the original query. The only
common subexpression was introduced by the optimizer itself at some point.
The query was of the form
"select * from (select <subquery> as foo) where foo is not null".
Is there some way to force the optimizer not to substitute the subquery in the
where clause? I mean, I can't do a materialize, I don't have the temporary
space for all the records, but is there some way to get it to assemble the
data for the single record and then apply the where clause on that?
Thanks again for all the help. I'm sorry if I'm becoming a pest but this query
may be really important to my project.
--
greg