avoiding seq scan without duplicating

Поиск
Список
Период
Сортировка
От Andrus
Тема avoiding seq scan without duplicating
Дата
Msg-id gf1dft$10i3$1@news.hub.org
обсуждение исходный текст
Ответы Re: avoiding seq scan without duplicating  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Simple query is slow, performs seq scan while index exists:

explain  select count(*)::integer as cnt
from firma2.dok
where dokumnr in (888817,2) and
dokumnr not in (select dokumnr FROM firma2.bilkaib WHERE
alusdok='LF' -- and dokumnr in (888817,2)
 )

"Aggregate  (cost=152063.71..152063.73 rows=1 width=0)"
"  ->  Bitmap Heap Scan on dok  (cost=152055.67..152063.71 rows=1 width=0)"
"        Recheck Cond: ((dokumnr = 888817) OR (dokumnr = 2))"
"        Filter: (NOT (hashed subplan))"
"        ->  BitmapOr  (cost=4.01..4.01 rows=2 width=0)"
"              ->  Bitmap Index Scan on dok_dokumnr_idx  (cost=0.00..2.00
rows=1 width=0)"
"                    Index Cond: (dokumnr = 888817)"
"              ->  Bitmap Index Scan on dok_dokumnr_idx  (cost=0.00..2.00
rows=1 width=0)"
"                    Index Cond: (dokumnr = 2)"
"        SubPlan"
"          ->  Seq Scan on bilkaib  (cost=0.00..152034.41 rows=6902
width=4)"
"                Filter: (alusdok = 'LF'::bpchar)"

Index is used if join condition is duplicated in subquery:

explain  select count(*)::integer as cnt
from firma2.dok
where dokumnr in (888817,2) and
dokumnr not in (select dokumnr FROM firma2.bilkaib WHERE
alusdok='LF' and dokumnr in (888817,2)
 )

"Aggregate  (cost=870.45..870.46 rows=1 width=0)"
"  ->  Bitmap Heap Scan on dok  (cost=862.41..870.44 rows=1 width=0)"
"        Recheck Cond: ((dokumnr = 888817) OR (dokumnr = 2))"
"        Filter: (NOT (hashed subplan))"
"        ->  BitmapOr  (cost=4.01..4.01 rows=2 width=0)"
"              ->  Bitmap Index Scan on dok_dokumnr_idx  (cost=0.00..2.00
rows=1 width=0)"
"                    Index Cond: (dokumnr = 888817)"
"              ->  Bitmap Index Scan on dok_dokumnr_idx  (cost=0.00..2.00
rows=1 width=0)"
"                    Index Cond: (dokumnr = 2)"
"        SubPlan"
"          ->  Bitmap Heap Scan on bilkaib  (cost=4.77..858.39 rows=3
width=4)"
"                Recheck Cond: ((dokumnr = 888817) OR (dokumnr = 2))"
"                Filter: (alusdok = 'LF'::bpchar)"
"                ->  BitmapOr  (cost=4.77..4.77 rows=219 width=0)"
"                      ->  Bitmap Index Scan on bilkaib_dokumnr_idx
(cost=0.00..2.38 rows=110 width=0)"
"                            Index Cond: (dokumnr = 888817)"
"                      ->  Bitmap Index Scan on bilkaib_dokumnr_idx
(cost=0.00..2.38 rows=110 width=0)"
"                            Index Cond: (dokumnr = 2)"

how to make query fast without repeating join condition two times in query ?

Andurs.


В списке pgsql-general по дате отправления:

Предыдущее
От: Craig Ringer
Дата:
Сообщение: Re: Equivalent for AUTOINCREMENT?
Следующее
От: Tom Lane
Дата:
Сообщение: Re: avoiding seq scan without duplicating