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 ms
select_with_normal_index
--------------------------
100000
(1 row)

Time: 1144832.597 ms
select_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 ms
select_with_clustered_index
-----------------------------
100000
(1 row)

Time: 535749.457 ms
select_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 | 904654
narrowtable2 | 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 |
857269
narrowtable | 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/