Re: Very bad plan when using VIEW and IN (SELECT...*)

"Carlo Stonebanks" <stonec.register@sympatico.ca> writes:
> Ref these two queries against a view:
> -- QUERY 1, executes < 0.5 secs
> SELECT *
> FROM mdx_core.vw_provider AS p
> WHERE provider_id IN (13083101)

> -- QUERY 2, executes > 13.5 secs
> SELECT *
> FROM mdx_core.vw_provider AS p
> WHERE provider_id IN (SELECT 13083101)

> I am using the simple IN (SELECT n) to simplify the problem. I noticed the
> oddity of the behaviour when I used a proper SELECT myId FROM myTable.

foo IN (SELECT ...), in general, is a join.  There's no particular
reason to expect it to give the same result as foo IN (constant).

Having said that, modern versions of the planner seem to deal reasonably
well with this situation as long as they're being asked to push the
condition through a UNION ALL.  Do you really need a UNION in that view?
That's going to cost you plenty in a lot of cases not only this one.

            regards, tom lane