IN queries have large number of int values. Sample below is query which has
only 10 values in IN list but takes more than one minute.
In real query IN list may contain up to 5000 integers.
There are indexes in both dok.dokumnr and bilkaib.dokumnr columns so it
should run fast.
How to speed up the sample query below and if its IN list contains 5000
integers ?
Larger list takes takes 700 seconds to run.
Should I use CREATE TEMP TABLE list ON COMMIT DROP to pass this list
instead using inline list or other idea ?
Shoult I create index on temp table also when creating temp table ?
Or should I require server upgrade ? Should I require upgrading PostgreSql,
adding RAM, disk speed or what ?
Andrus.
"PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC
i686-pc-linux-gnu-gcc (GCC) 3.4.6 (Gentoo 3.4.6-r1, ssp-3.4.5-1.0,
pie-8.7.9)"
explain analyze select count(*)::INTEGER as cnt
from dok
WHERE dokumnr IN
(869906,869907,869910,869911,869914,869915,869916,869917,869918,869921 )
and
dokumnr NOT IN (SELECT dokumnr FROM bilkaib WHERE
alusdok='LY')
"Aggregate (cost=186516.39..186516.40 rows=1 width=0) (actual
time=72370.224..72370.228 rows=1 loops=1)"
" -> Bitmap Heap Scan on dok (cost=154840.10..186516.37 rows=5 width=0)
(actual time=72370.195..72370.195 rows=0 loops=1)"
" Recheck Cond: ((dokumnr = 869906) OR (dokumnr = 869907) OR (dokumnr
= 869910) OR (dokumnr = 869911) OR (dokumnr = 869914) OR (dokumnr = 869915)
OR (dokumnr = 869916) OR (dokumnr = 869917) OR (dokumnr = 869918) OR
(dokumnr = 869921))"
" Filter: (NOT (subplan))"
" -> BitmapOr (cost=20.03..20.03 rows=10 width=0) (actual
time=173.116..173.116 rows=0 loops=1)"
" -> Bitmap Index Scan on dok_dokumnr_idx (cost=0.00..2.00
rows=1 width=0) (actual time=172.981..172.981 rows=1 loops=1)"
" Index Cond: (dokumnr = 869906)"
" -> Bitmap Index Scan on dok_dokumnr_idx (cost=0.00..2.00
rows=1 width=0) (actual time=0.021..0.021 rows=1 loops=1)"
" Index Cond: (dokumnr = 869907)"
" -> Bitmap Index Scan on dok_dokumnr_idx (cost=0.00..2.00
rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)"
" Index Cond: (dokumnr = 869910)"
" -> Bitmap Index Scan on dok_dokumnr_idx (cost=0.00..2.00
rows=1 width=0) (actual time=0.016..0.016 rows=1 loops=1)"
" Index Cond: (dokumnr = 869911)"
" -> Bitmap Index Scan on dok_dokumnr_idx (cost=0.00..2.00
rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)"
" Index Cond: (dokumnr = 869914)"
" -> Bitmap Index Scan on dok_dokumnr_idx (cost=0.00..2.00
rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)"
" Index Cond: (dokumnr = 869915)"
" -> Bitmap Index Scan on dok_dokumnr_idx (cost=0.00..2.00
rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=1)"
" Index Cond: (dokumnr = 869916)"
" -> Bitmap Index Scan on dok_dokumnr_idx (cost=0.00..2.00
rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=1)"
" Index Cond: (dokumnr = 869917)"
" -> Bitmap Index Scan on dok_dokumnr_idx (cost=0.00..2.00
rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=1)"
" Index Cond: (dokumnr = 869918)"
" -> Bitmap Index Scan on dok_dokumnr_idx (cost=0.00..2.00
rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=1)"
" Index Cond: (dokumnr = 869921)"
" SubPlan"
" -> Materialize (cost=154820.07..160183.25 rows=385618 width=4)
(actual time=0.216..4400.739 rows=384914 loops=10)"
" -> Seq Scan on bilkaib (cost=0.00..152927.45 rows=385618
width=4) (actual time=1.925..11707.045 rows=384930 loops=1)"
" Filter: (alusdok = 'LY'::bpchar)"
"Total runtime: 72374.562 ms"
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
