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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions
Дата
Msg-id CAKFQuwaC=kQbGCMkedErDM+BmcC9vb==S7m6J3TayCWnE39D5Q@mail.gmail.com
обсуждение исходный текст
Ответ на [GENERAL] Perfomance of IN-clause with many elements and possible solutions  ("dilaz03 ." <dilaz03@gmail.com>)
Ответы Re: [GENERAL] Perfomance of IN-clause with many elements and possiblesolutions
Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions
Список pgsql-general
Дерево обсуждения
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 по дате отправления:

Предыдущее
От: Dmitry Lazurkin
Дата:
Сообщение: Re: [GENERAL] Perfomance of IN-clause with many elements and possiblesolutions
Следующее
От: Dmitry Lazurkin
Дата:
Сообщение: Re: [GENERAL] Perfomance of IN-clause with many elements and possiblesolutions