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.
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? |