Skip site navigation (1) Skip section navigation (2)

Re: optimizing queries using IN and EXISTS

From: Nick Hofstede <Nick(dot)Hofstede(at)inventivegroup(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: optimizing queries using IN and EXISTS
Date: 2012-07-19 11:53:43
Message-ID: BC885F9E3DB48248A4C9FC7F2C57215C2DAB0587@Hoefnix.dc.intranet (view raw or flat)
Thread:
Lists: pgsql-performance
Interesting.
Thanks for the work-around.

Regards,

Nick

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Sent: donderdag 19 juli 2012 0:36
To: Nick Hofstede
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] optimizing queries using IN and EXISTS

Nick Hofstede <Nick(dot)Hofstede(at)inventivegroup(dot)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

--
Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

--
This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.


________________________________

Inventive Designers' Email Disclaimer:
http://www.inventivedesigners.com/email-disclaimer

In response to

pgsql-performance by date

Next:From: Ioannis AnagnostopoulosDate: 2012-07-19 12:24:34
Subject: Re: Index slow down insertions...
Previous:From: Felix ScheicherDate: 2012-07-19 11:33:28
Subject: queries are fast after dump->restore but slow again after some days dispite vacuum

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group