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

"David G. Johnston" <david.g.johnston@xxxxxxxxx> · Mon, 24 Jul 2017 15:25:09 -0700

On Mon, Jul 24, 2017 at 3:22 PM, Dmitry Lazurkin <dilaz03@xxxxxxxxx> wrote:

  ALTER TABLE ids ALTER COLUMN id SET NOT NULL;

    EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM ids WHERE id IN
    :values_clause; 

     Aggregate  (cost=245006.46..245006.47 rows=1 width=8) (actual
    time=3824.095..3824.095 rows=1 loops=1)

       Buffers: shared hit=44248

       ->  Hash Join  (cost=7.50..235006.42 rows=4000019 width=0)
    (actual time=1.108..3327.112 rows=3998646 loops=1)

       ...

You haven't constrained the outer relation (i.e., :values_clause) to be non-null which is what I believe is required for the semi-join algorithm to be considered.

David J.