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/
