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

Taken from your advice, I gave this a try:

SELECT *
FROM mdx_core.vw_provider AS p
WHERE provider_id = ANY array(
SELECT provider_id
FROM mdx_core.provider_alias
LIMIT 10
)

Well, it RIPS through the query in 15ms compared to 13.0 secs for the In
(SELECT...)

>> 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?
<<

This is PG v 8.3 - do you mean "modern" compared to that?

And if you mean UNION vs. UNION ALL, I think in this case UNION ALL would
do.

Carlo

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: August 12, 2010 6:48 PM
To: Carlo Stonebanks
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Very bad plan when using VIEW and IN (SELECT...*)

"Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)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