Re: Have I b0rked something? Slow comparisons on "where x in (...)"
От | Listmail |
---|---|
Тема | Re: Have I b0rked something? Slow comparisons on "where x in (...)" |
Дата | |
Msg-id | op.trrgrqhdzcizji@apollo13 обсуждение исходный текст |
Ответ на | Re: Have I b0rked something? Slow comparisons on "where x in (...)" (Stephen Harris <lists@spuddy.org>) |
Ответы |
Re: Have I b0rked something? Slow comparisons on "where
x in (...)"
|
Список | pgsql-general |
Followup to my previous test, with an index this time EXPLAIN ANALYZE SELECT * FROM test WHERE value IN ( 1000 integers ) Bitmap Heap Scan on test (cost=3519.09..7156.83 rows=1000 width=4) (actual time=5.843..8.897 rows=999 loops=1) Recheck Cond: (value = ANY ('{0,...,999000}'::integer[])) -> Bitmap Index Scan on testindex (cost=0.00..3518.84 rows=1000 width=0) (actual time=5.594..5.594 rows=999 loops=1) Index Cond: (value = ANY ('{0,...,999000}'::integer[])) Total runtime: 9.157 ms EXPLAIN ANALYZE SELECT * FROM test WHERE value IN (VALUES (0),(1000),.......(999000)) Nested Loop (cost=15.00..1461.74 rows=200 width=4) (actual time=1.191..26.127 rows=999 loops=1) -> HashAggregate (cost=15.00..17.00 rows=200 width=4) (actual time=1.169..1.673 rows=1000 loops=1) -> Values Scan on "*VALUES*" (cost=0.00..12.50 rows=1000 width=4) (actual time=0.007..0.517 rows=1000 loops=1) -> Index Scan using testindex on test (cost=0.00..7.21 rows=1 width=4) (actual time=0.023..0.023 rows=1 loops=1000) Index Cond: (test.value = "*VALUES*".column1) Total runtime: 26.411 ms Mixing the two would be a win : - hashing the values - making a bitmap from them - grabbing the pages and using the hash in "Recheck Cond" ie. something like that : -> HashAggregate (cost=15.00..17.00 rows=200 width=4) (actual time=1.169..1.673 rows=1000 loops=1) -> Values Scan on "*VALUES*" (cost=0.00..12.50 rows=1000 width=4) (actual time=0.007..0.517 rows=1000 loops=1) Bitmap Heap Scan on test (cost=3519.09..7156.83 rows=1000 width=4) (actual time=5.843..8.897 rows=999 loops=1) Recheck Cond: (value in hash) -> Bitmap Index Scan on testindex (cost=0.00..3518.84 rows=1000 width=0) (actual time=5.594..5.594 rows=999 loops=1) Index Cond: (value in hash)
В списке pgsql-general по дате отправления: