Re: Optimising "in" queries

Alvaro Herrera <alvherre(at)commandprompt(dot)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