Re: Have I b0rked something? Slow comparisons on "where x in (...)"

    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)