avoiding seq scan without duplicating

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: pgsql-general(at)postgresql(dot)org
Subject: avoiding seq scan without duplicating
Date: 2008-11-07 12:49:01
Message-ID: gf1dft$10i3$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2008-11-07 13:26:10 Re: avoiding seq scan without duplicating
Previous Message Craig Ringer 2008-11-07 12:46:01 Re: Equivalent for AUTOINCREMENT?