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:t...@sss.pgh.pa.us] Sent: August 12, 2010 6:48 PM To: Carlo Stonebanks Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Very bad plan when using VIEW and IN (SELECT...*) "Carlo Stonebanks" <stonec.regis...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
