Re: slow IN() clause for many cases
От | Ilia Kantor |
---|---|
Тема | Re: slow IN() clause for many cases |
Дата | |
Msg-id | auto-000577747741@umail.ru обсуждение исходный текст |
Ответ на | Re: slow IN() clause for many cases (Josh Berkus <josh@agliodbs.com>) |
Ответы |
Re: slow IN() clause for many cases
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-hackers |
>> 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)
В списке pgsql-hackers по дате отправления: