From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nick Hofstede <Nick.Hofstede@inventivegroup.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: optimizing queries using IN and EXISTS
Date: Wed, 18 Jul 2012 18:36:14 -0400
Message-ID: <3691.1342650974@sss.pgh.pa.us> (raw)
In-Reply-To: <BC885F9E3DB48248A4C9FC7F2C57215C2DAAFF71@Hoefnix.dc.intranet>
References: <BC885F9E3DB48248A4C9FC7F2C57215C2DAAFF71@Hoefnix.dc.intranet>
Nick Hofstede <Nick.Hofstede@inventivegroup.com> writes:
> I'm surprised at the difference in speed/execution plan between two logically equivalent queries, one using IN, the other using EXISTS. (At least I think they are logically equivalent)
> SELECT *
> FROM foo
> WHERE 'text6' IN (SELECT value
> FROM bar
> JOIN foo AS foo2
> ON bar.foo_ref = foo2.id
> WHERE foo2.id = foo.id)
Hm. convert_ANY_sublink_to_join() rejects subqueries that contain any
Vars of the parent query level, so the reference to foo.id prevents this
from being converted to a semijoin. However, it seems like that's
overly restrictive. I'm not sure that we could remove the test
altogether, but at least outer vars used in WHERE seem safe.
In the meantime, you can recast like this:
SELECT *
FROM foo
WHERE ('text6', id) IN (SELECT value, foo2.id
FROM bar
JOIN foo AS foo2
ON bar.foo_ref = foo2.id)
and still get a semijoin plan from an IN-style query.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, Nick.Hofstede@inventivegroup.com
Subject: Re: optimizing queries using IN and EXISTS
In-Reply-To: <3691.1342650974@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox