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.02rows=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.02rows=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.02rows=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.02rows=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.02rows=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.02rows=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.02rows=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.02rows=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.02rows=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.00rows=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.00rows=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.00rows=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.00rows=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.00rows=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.00rows=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.00rows=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.00rows=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.00rows=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.00rows=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.00rows=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.00rows=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.00rows=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.00rows=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.00rows=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.00rows=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.00rows=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.00rows=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.00rows=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.00rows=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)