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/ ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly