Re: slow IN() clause for many cases

>> It is bitmap-OR on multiple index(PK) lookups.

> Describing it doesn't help. We need an *actual* EXPLAIN ANALYZE.
Sure, why not..

6ms for

Bitmap Heap Scan on objects_hier (cost=60.29..179.57 rows=80 width=600)
(actual time=0.835..1.115 rows=138 loops=1)
Recheck Cond: ((id = 1) OR (id = 2) OR (id = 3) OR (id = 4) OR (id = 5)
OR (id = 6) OR (id = 7) OR (id = 8) OR (id = 9)
OR (id = 10) OR (id = 11) OR (id = 12) OR (id = 13) OR (id = 14) OR (id =
15) OR (id = 16) OR (id = 17) OR (id = 18) OR (
id = 19) OR (id = 20) OR (id = 21) OR (id = 22) OR (id = 23) OR (id = 24) OR
(id = 25) OR (id = 26) OR (id = 27) OR (id =
28) OR (id = 29) OR (id = 30))
-> BitmapOr (cost=60.29..60.29 rows=82 width=0) (actual
time=0.553..0.553 rows=0 loops=1)
-> Bitmap Index Scan on lkjk (cost=0.00..2.02 rows=6 width=0)
(actual time=0.036..0.036 rows=6 loops=1)
Index Cond: (id = 1)
-> Bitmap Index Scan on lkjk (cost=0.00..2.02 rows=6 width=0)
(actual time=0.044..0.044 rows=6 loops=1)
Index Cond: (id = 2)
-> Bitmap Index Scan on lkjk (cost=0.00..2.02 rows=6 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 3)
-> Bitmap Index Scan on lkjk (cost=0.00..2.02 rows=6 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 4)
-> Bitmap Index Scan on lkjk (cost=0.00..2.02 rows=6 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 5)
-> Bitmap Index Scan on lkjk (cost=0.00..2.02 rows=6 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 6)
-> Bitmap Index Scan on lkjk (cost=0.00..2.02 rows=6 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 7)
-> Bitmap Index Scan on lkjk (cost=0.00..2.02 rows=6 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 8)
-> Bitmap Index Scan on lkjk (cost=0.00..2.02 rows=6 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 9)
-> Bitmap Index Scan on lkjk (cost=0.00..2.02 rows=6 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 10)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 11)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 12)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 13)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 14)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 15)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 16)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 17)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 18)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 19)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 20)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 21)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 22)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=6 loops=1)
Index Cond: (id = 23)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=0 loops=1)
Index Cond: (id = 24)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=0 loops=1)
Index Cond: (id = 25)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=0 loops=1)
Index Cond: (id = 26)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=0 loops=1)
Index Cond: (id = 27)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=0 loops=1)
Index Cond: (id = 28)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=0 loops=1)
Index Cond: (id = 29)
-> Bitmap Index Scan on lkjk (cost=0.00..2.00 rows=1 width=0)
(actual time=0.002..0.002 rows=0 loops=1)
Index Cond: (id = 30)

4ms for

explain analyze select * from objects_hier join (select array2table as id
from
array2table(array[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,2
3,24,25,26,27,28,29,30])) a using(id);
QUERY PLAN

----------------------------------------------------------------------------
------------------------------------------------------
Merge Join (cost=62.33..576.80 rows=1117 width=600) (actual
time=0.542..2.898 rows=138 loops=1)
Merge Cond: ("outer".id = "inner".array2table)
-> Index Scan using lkjk on objects_hier (cost=0.00..493.52 rows=1680
width=600) (actual time=0.025..1.248 rows=139 loops=1)
-> Sort (cost=62.33..64.83 rows=1000 width=4) (actual time=0.510..0.799
rows=145 loops=1)
Sort Key: array2table.array2table
-> Function Scan on array2table (cost=0.00..12.50 rows=1000
width=4) (actual time=0.081..0.141 rows=30 loops=1)