Re: Perfomance of IN-clause with many elements and possible solutions

On Sun, Jul 23, 2017 at 4:35 AM, dilaz03 . <dilaz03(at)gmail(dot)com> wrote:

> - IN-VALUES clause adds new node to plan. Has additional node big
> overhead? How about filter by two or more IN-VALUES clause?
>

​IN-VALUES is just another word for "TABLE" which is another word for
"RELATION". Writing relational database queries that use explicit
relations is generally going to give you the best performance.

Basically you want to write something like:

SELECT *
FROM ids
JOIN ( :values_clause ) vc (vid) ON (vc.vid = ids.id)​

or

WITH vc AS (SELECT vid FROM .... ORDER BY ... LIMIT )
SELECT *
FROM ids
JOIN vc ON (vid = ids.id)

"IN ('l1','l2','l3')" is nice and all but as demonstrated the mechanics of
executing that are different, and slower, than processing relations and
tuples. For a small number of items the difference is generally not
meaningful and so the convenience of writing (IN (...)) is worth taking.

David J.