Re: bitmap scans, btree scans, and tid order

On 5/16/05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> regression=# explain analyze select * from tenk1 where unique1 between 100 and 1000;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on tenk1 (cost=9.58..381.53 rows=930 width=244) (actual time=6.185..18.034 rows=901 loops=1)
> Recheck Cond: ((unique1 >= 100) AND (unique1 <= 1000))
> -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..9.58 rows=930 width=0) (actual time=4.522..4.522 rows=901 loops=1)
> Index Cond: ((unique1 >= 100) AND (unique1 <= 1000))
> Total runtime: 23.784 ms
> (5 rows)
>
> regression=# explain analyze select * from tenk1 where unique2 between 100 and 1000;
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------
> Index Scan using tenk1_unique2 on tenk1 (cost=0.00..45.88 rows=805 width=244) (actual time=0.154..14.856 rows=901 loops=1)
> Index Cond: ((unique2 >= 100) AND (unique2 <= 1000))
> Total runtime: 20.331 ms
> (3 rows)
>

Tom (or anyone with some round tuits and CVS-tip savy), if you have a
chance at some point would you post the non-bitmap version of the
query for tenk2 from above? I'd be very interested to see if the
bitmap forced TID order fetch actually does help, or if it's
outweighed by the bitmap setup overhead.

TIA

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org