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)
