Re: I'd like to learn a bit more about how indexes work

Mike Christensen <mike(at)kitchenpc(dot)com> writes:
> Thanks! One thing that still confuses me is the difference between IN
> and OR. With this query:

> explain analyze
> select *
> from foobar
> where d in (500, 750);

> It scans the d index only once:

> 'Bitmap Heap Scan on foobar (cost=10.03..400.63 rows=196 width=16)
> (actual time=0.128..0.489 rows=200 loops=1)'
> ' Recheck Cond: (d = ANY ('{500,750}'::integer[]))'
> ' -> Bitmap Index Scan on d_idx (cost=0.00..9.98 rows=196 width=0)
> (actual time=0.086..0.086 rows=200 loops=1)'
> ' Index Cond: (d = ANY ('{500,750}'::integer[]))'
> 'Total runtime: 0.592 ms'

Actually, that's two indexscans --- the btree code is aware that it has
to perform an indexscan for each element of the =ANY array, and add all
the resulting bits to the output bitmap. (The bitmap takes care of
eliminating any duplicate hits, which is why this type of index
condition is only supported for bitmap scans and not plain indexscans.)

So this isn't really any different from the OR case in terms of what
happens while probing the index. It's marginally more efficient in
that we save an explicit BitmapOr step, but only marginally.

As for why these cases are treated differently, yeah that's historical
to some extent, but it's also true that trying to convert one notation
to the other would be expensive and often fruitless.

regards, tom lane