Re: [PATCHES] Bitmapscan changes

Heikki Linnakangas wrote:
> Joshua D. Drake wrote:
>> Heikki Linnakangas wrote:
>>> Joshua D. Drake wrote:
>>>> This URL is not working:
>>>>
>>>>
>>>> http://community.enterprisedb.com/git/git-perfunittests-20070222.tar.gz
>>> Sorry about that, typo in the filename. Fixed.
>>>
>>>
>> Here are my results on a modest 3800X2 2 Gig of ram, RAID 1 dual SATA
> 
heap_pages | normal_index_pages | clustered_index_pages
------------+--------------------+-----------------------    216217 |             109679 |                  1316

select_with_normal_index
--------------------------                  100000
(1 row)

Time: 1356524.743 msselect_with_normal_index
--------------------------                  100000
(1 row)

Time: 1144832.597 msselect_with_normal_index
--------------------------                  100000
(1 row)

Time: 1111445.236 ms


And now run the same tests with clustered index
Timing is on.select_with_clustered_index
-----------------------------                     100000
(1 row)

Time: 815622.768 msselect_with_clustered_index
-----------------------------                     100000
(1 row)

Time: 535749.457 msselect_with_clustered_index
-----------------------------                     100000
(1 row)


select relname,indexrelname,idx_blks_read,idx_blks_hit from
pg_statio_all_indexes where schemaname = 'public';  relname    |         indexrelname         | idx_blks_read |
idx_blks_hit
--------------+------------------------------+---------------+--------------narrowtable  | narrowtable_index
|       296973 |       904654narrowtable2 | narrowtable2_clustered_index |         44556 |       857269
 
(2 rows)
select relname,heap_blks_read,heap_blks_hit,idx_blks_read,idx_blks_hit
from pg_statio_user_tables  ;  relname    | heap_blks_read | heap_blks_hit | idx_blks_read |
idx_blks_hit
--------------+----------------+---------------+---------------+--------------narrowtable2 |         734312 |
40304136|         44556 |
 
857269narrowtable  |         952044 |      40002609 |        296973 |
904654


Seems like a clear win to me. Anyone else want to try?

Sincerely,

Joshua D. Drake




-- 
     === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997            http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/