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"

Search Discussions

  • Isak Hansen at Nov 10, 2008 at 1:09 pm

    On Mon, Nov 10, 2008 at 12:35 PM, Andrus wrote:

    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')
    Looks to me like most of the time is spent doing "not in (select a ton
    of rows from bilkaib)".

    Try something like "not exists (select null from bilkaib b where
    b.dokumnr = dok.dokumnr and alusdok = 'LY')".


    HTH, Isak
  • Andrus at Nov 10, 2008 at 1:53 pm
    Isak,
    Looks to me like most of the time is spent doing "not in (select a ton
    of rows from bilkaib)".

    Try something like "not exists (select null from bilkaib b where
    b.dokumnr = dok.dokumnr and alusdok = 'LY')".
    Thank you very much.
    Query seems to return now immediately.

    dok.dokumnr is not null
    bilkaib.dokumnr can be null

    I hope that modified query produces same results as original.

    Andrus.
  • Andrus at Nov 10, 2008 at 3:35 pm

    Try something like "not exists (select null from bilkaib b where
    b.dokumnr = dok.dokumnr and alusdok = 'LY')".
    I tried to optimize another similar query but it is still slow.

    This query has same clause

    dok.dokumnr IN
    (869906,869907,869910,869911,869914,869915,869916,869917,869918 )

    duplicated in in two places.

    In real query this list contains much more items and set clause sets a lot
    of values using FROM tables
    (in this test SET contains only dummy setter) but it seems to take roughly
    the same time as test query below.

    All join columns are indexed. How to make this faster ?
    Log below shows that triggers are called.
    UPDATE doesnt update any fk columns. No idea why explain shows trigger
    calls.

    Andrus.

    EXPLAIN analyze UPDATE DOK set
    KALKLIIK = dok.KALKLIIK
    from ( SELECT
    dok.dokumnr,
    SUM(rid.hind) AS doksumma
    FROM dok JOIN rid USING(dokumnr)
    WHERE dok.dokumnr IN
    (869906,869907,869910,869911,869914,869915,869916,869917,869918 )
    group by 1
    ) doksumma right join dok x USING(dokumnr)
    left join bilkaib on bilkaib.dokumnr=doksumma.dokumnr and
    bilkaib.alusdok='LY'
    WHERE dok.dokumnr IN
    (869906,869907,869910,869911,869914,869915,869916,869917,869918)
    and
    dok.dokumnr=x.dokumnr

    "Hash Join (cost=540908.66..2312297.49 rows=650 width=1179) (actual
    time=66045.802..84717.094 rows=33 loops=1)"
    " Hash Cond: ("outer".dokumnr = "inner".dokumnr)"
    " -> Merge Right Join (cost=540854.51..1869873.52 rows=88472665 width=4)
    (actual time=66045.432..80246.663 rows=1222376 loops=1)"
    " Merge Cond: ("outer".dokumnr = "inner".dokumnr)"
    " -> Sort (cost=194834.23..195798.74 rows=385806 width=4) (actual
    time=7373.251..8003.360 rows=159000 loops=1)"
    " Sort Key: bilkaib.dokumnr"
    " -> Seq Scan on bilkaib (cost=0.00..153002.12 rows=385806
    width=4) (actual time=1.698..4192.896 rows=406443 loops=1)"
    " Filter: (alusdok = 'LY'::bpchar)"
    " -> Sort (cost=346020.28..349083.99 rows=1225481 width=8) (actual
    time=57462.007..62545.451 rows=1222376 loops=1)"
    " Sort Key: doksumma.dokumnr"
    " -> Hash Left Join (cost=860.23..189634.54 rows=1225481
    width=8) (actual time=68.117..50296.421 rows=1222352 loops=1)"
    " Hash Cond: ("outer".dokumnr = "inner".dokumnr)"
    " -> Seq Scan on dok x (cost=0.00..182646.81
    rows=1225481 width=4) (actual time=15.107..40573.750 rows=1222352 loops=1)"
    " -> Hash (cost=860.21..860.21 rows=9 width=4) (actual
    time=52.976..52.976 rows=9 loops=1)"
    " -> Subquery Scan doksumma (cost=860.01..860.21
    rows=9 width=4) (actual time=52.828..52.930 rows=9 loops=1)"
    " -> HashAggregate (cost=860.01..860.12
    rows=9 width=14) (actual time=52.819..52.857 rows=9 loops=1)"
    " -> Nested Loop (cost=18.03..859.89
    rows=24 width=14) (actual time=42.854..52.659 rows=22 loops=1)"
    " -> Bitmap Heap Scan on dok
    (cost=18.03..54.13 rows=9 width=4) (actual time=0.246..0.364 rows=9
    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))"
    " -> BitmapOr
    (cost=18.03..18.03 rows=9 width=0) (actual time=0.214..0.214 rows=0
    loops=1)"
    " -> Bitmap Index
    Scan on dok_dokumnr_idx (cost=0.00..2.00 rows=1 width=0) (actual
    time=0.102..0.102 rows=2 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.010..0.010 rows=2 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=2 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.014..0.014 rows=2 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.008..0.008 rows=2 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=2 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.008..0.008 rows=2 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.008..0.008 rows=2 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.008..0.008 rows=2 loops=1)"
    " Index Cond:
    (dokumnr = 869918)"
    " -> Index Scan using
    rid_dokumnr_idx on rid (cost=0.00..87.95 rows=126 width=14) (actual
    time=4.767..5.780 rows=2 loops=9)"
    " Index Cond:
    ("outer".dokumnr = rid.dokumnr)"
    " -> Hash (cost=54.13..54.13 rows=9 width=1179) (actual time=0.328..0.328
    rows=9 loops=1)"
    " -> Bitmap Heap Scan on dok (cost=18.03..54.13 rows=9 width=1179)
    (actual time=0.164..0.248 rows=9 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))"
    " -> BitmapOr (cost=18.03..18.03 rows=9 width=0) (actual
    time=0.132..0.132 rows=0 loops=1)"
    " -> Bitmap Index Scan on dok_dokumnr_idx
    (cost=0.00..2.00 rows=1 width=0) (actual time=0.025..0.025 rows=2 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.008..0.008 rows=2 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.007..0.007 rows=2 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.013..0.013 rows=2 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.008..0.008 rows=2 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=2 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.008..0.008 rows=2 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=2 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=2 loops=1)"
    " Index Cond: (dokumnr = 869918)"
    "Trigger for constraint dok_kalktoode_fkey: time=0.262 calls=9"
    "Trigger for constraint dok_krdokumnr_fkey: time=0.069 calls=9"
    "Trigger for constraint dok_liikmesrii_fkey: time=0.075 calls=9"
    "Trigger for constraint dok_maksetin_fkey: time=0.072 calls=9"
    "Trigger for constraint dok_pais5obj_fkey: time=0.077 calls=9"
    "Trigger for constraint dok_pais6obj_fkey: time=0.074 calls=9"
    "Trigger for constraint dok_pais7obj_fkey: time=0.074 calls=9"
    "Trigger for constraint dok_pais8obj_fkey: time=0.075 calls=9"
    "Trigger for constraint dok_pais9obj_fkey: time=0.076 calls=9"
    "Trigger for constraint dok_saaja_fkey: time=0.072 calls=9"
    "Trigger for constraint dok_statprots_fkey: time=0.086 calls=9"
    "Trigger for constraint dok_tarneklaus_fkey: time=0.087 calls=9"
    "Trigger for constraint dok_tehingulii_fkey: time=0.082 calls=9"
    "Trigger for constraint dok_username_fkey: time=0.073 calls=9"
    "Trigger for constraint dok_vmnr_fkey: time=0.080 calls=9"
    "Trigger for constraint dok_volitaisik_fkey: time=0.085 calls=9"
    "Total runtime: 84815.547 ms"

    "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)"

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedNov 10, '08 at 11:35a
activeNov 10, '08 at 3:35p
posts4
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Andrus: 3 posts Isak Hansen: 1 post

People

Translate

site design / logo © 2022 Grokbase