From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Stephen Davies <scldad@sdc.com.au>
Cc: Michael Glaesemann <grzm@seespotcode.net>
Cc: Russell Smith <mr-russ@pws.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimising "in" queries
Date: Thu, 23 Aug 2007 22:19:40 -0400
Message-ID: <16318.1187921980@sss.pgh.pa.us> (raw)
In-Reply-To: <20070823194642.GD31461@alvh.no-ip.org>
References: <200708221210.36770.scldad@sdc.com.au>
<46CC16F3.7060606@pws.com.au>
<AAF53AEE-B98C-45BD-B80F-8FA528BFB98B@seespotcode.net>
<200708230900.13878.scldad@sdc.com.au>
<20070823194642.GD31461@alvh.no-ip.org>
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Stephen Davies wrote:
>> While superficially equivalent, I have always believed that IN (a,b,c)
>> executed faster than =a or =b or =c. Am I wrong for PostgreSQL?
> Older versions of Postgres translated IN (a, b, c) into an OR'ed list of
> equalities. Nowadays it is treated as an array; I think it's translated
> to = ANY ({a,b,c}), as you can see in the message you posted at the
> start of this thread.
If you're dealing with tables large enough that the index search work is
the dominant cost, all these variants ought to be exactly the same.
However, for smaller tables the planning time and executor startup time
are interesting, and on those measures the = ANY(array) formulation
should win because there's less "stuff" for the system to look at.
With "x=a OR x=b OR x=c" the planner actually has to deduce three times
that an indexscan on x is possible; with "x = ANY(ARRAY[a,b,c])" it
does that only once. That's why I changed IN to expand to an array
construct instead of an OR tree. I have to confess not having tried to
measure the consequences carefully, though. I suspect it's not all
that interesting at only three items ... it's lists of hundreds or
thousands of items where this becomes a big deal.
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, alvherre@commandprompt.com, scldad@sdc.com.au, grzm@seespotcode.net, mr-russ@pws.com.au
Subject: Re: Optimising "in" queries
In-Reply-To: <16318.1187921980@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