Postgres Pro
Downloads
Home   >   mailing lists

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 CAKFQuwaC=kQbGCMkedErDM+BmcC9vb==S7m6J3TayCWnE39D5Q@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
Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions
List pgsql-general
Tree view
On Mon, Jul 24, 2017 at 3:22 PM, Dmitry Lazurkin <dilaz03@gmail.com> 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.

pgsql-general by date:

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