Re: Difference in indexes

""A.j. Langereis"" <a(dot)j(dot)langereis(at)inter(dot)nl(dot)net> wrote
>
> "Bitmap Heap Scan on hosts (cost=2.07..11.34 rows=21 width=59) (actual
> time=0.175..0.287 rows=21 loops=1)"
> " Recheck Cond: ((hostname)::text = 'Fabian'::text)"
> " -> Bitmap Index Scan on hosts_hostname (cost=0.00..2.07 rows=21
> width=0) (actual time=0.145..0.145 rows=21 loops=1)"
> " Index Cond: ((hostname)::text = 'Fabian'::text)"
> "Total runtime: 0.510 ms"
>
> This result was achieved by setting enable_seqscan to off
> (postgresql.conf).
> Turning off enable_bitmapscan as well resulted in a index scan which was
> even more faster:
>
> "Index Scan using hosts_hostname on hosts (cost=0.00..37.28 rows=21
> width=59) (actual time=0.068..0.281 rows=21 loops=1)"
> " Index Cond: ((hostname)::text = 'Fabian'::text)"
> "Total runtime: 0.492 ms"
>

If you compare the difference among the *estimated* cost ("cost=0.00 .."):

seqscan: cost=0.00..10.25
Bitmap: cost=2.07..11.34
indexscan: cost=0.00..37.28

Then you will know why the optimizer prefers sequential scan. Yes, in your
case, the *real* cost("actual time = ...") is quite different from the
estimated cost. That's because the optimizer can't collect enough
information of the environment at execution. For example, the optimizer does
not know if a data page is in buffer or not(which will incurs IO cost) and
it always assumes not. There is a long story about the why the optimizer
does this. In short, since PG uses small buffer pool and the optimizer is
mainly useful for big tables, so this assumption is reasonable -- but for
small tables, may not that good.

Regards,
Qingqing