[PostgreSQL] Optimizing IN queries

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

Isak,
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.

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