Unfortunately I had double-posted this - originally in General. Tom Lane pointed out (in PG-GENERAL) that the planner will take any IN (SELECT...) statement and do a JOIN, which is what is causing the planner problem - even though the SELECT was just returning a constant. Obviously, the real query this was testing was something more real-world.
SO, I took my original query and turned it to this: SELECT * FROM mdx_core.vw_provider AS p WHERE provider_id = ANY array( SELECT provider_id FROM mdx_core.provider_alias ) BLISTERINGLY fast! PG version is 8.3 - as for configuration, I didn't want to throw too much info as my concern was actually whether views were as klunky as other DB platforms. Carlo -----Original Message----- From: Kevin Grittner [mailto:kevin.gritt...@wicourts.gov] Sent: August 13, 2010 9:29 AM To: pgsql-performance@postgresql.org; Carlo Stonebanks Subject: Re: [PERFORM] Very bad plan when using VIEW and IN (SELECT...*) "Carlo Stonebanks" <stonec.regis...@sympatico.ca> wrote: > SELECT * > FROM mdx_core.vw_provider AS p > WHERE provider_id IN (SELECT 13083101) > > I am using the simple IN (SELECT n) in QUERY 2 to simplify the > problem. I noticed the oddity of the behaviour when I used a > proper "IN (SELECT myId FROM myTable)" Did you try?: SELECT * FROM mdx_core.vw_provider AS p WHERE EXISTS (SELECT * FROM myTable WHERE myId = provider_id) For any follow-up you should probably mention what version of PostgreSQL this is and how it's configured. http://wiki.postgresql.org/wiki/SlowQueryQuestions -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
