Re: Difference in indexes

Ok, I didn't look at it from that point of view. It makes it all clear!

Thanks for the explanation!

Yours,

Aarjan Langereis

----- Original Message -----
From: "Qingqing Zhou" <zhouqq(at)cs(dot)toronto(dot)edu>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, November 22, 2005 2:23 AM
Subject: Re: [GENERAL] 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
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>