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

Dmitry Lazurkin <dilaz03@xxxxxxxxx> · Tue, 25 Jul 2017 01:12:04 +0300



    On 25.07.2017 00:31, David G. Johnston wrote:

    
            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)
            

    This query uses JOIN plan node as IN (VALUES ...).

    
    And I have one question. I don't understand why IN-VALUES doesn't
    use Semi-Join? PostgreSQL has Hash Semi-Join...  For which task the
    database has node of this type?