Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions - Mailing list pgsql-general

From David G. Johnston
Subject Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions
Date
Msg-id CAKFQuwZ5Ep+ojefGJPNKowh0jfjOM1jEVALR=Grw+HB9iNjQNQ@mail.gmail.com
Whole thread Raw
In response to [GENERAL] Perfomance of IN-clause with many elements and possible solutions  ("dilaz03 ." <dilaz03@gmail.com>)
Responses Re: [GENERAL] Perfomance of IN-clause with many elements and possiblesolutions  (Dmitry Lazurkin <dilaz03@gmail.com>)
List pgsql-general
On Sun, Jul 23, 2017 at 4:35 AM, dilaz03 . <dilaz03@gmail.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.

pgsql-general by date:

Previous
From: PT
Date:
Subject: Re: [GENERAL] Perfomance of IN-clause with many elements andpossible solutions
Next
From: Dmitry Lazurkin
Date:
Subject: Re: [GENERAL] Perfomance of IN-clause with many elements and possiblesolutions