Thread: Re: [PATCHES] Bitmapscan changes
<p><font size="2">Wow, nice!<br /><br /> Can you tell us:<br /> - how big is the table<br /> - cardinality of the column<br/> - how big is the index in each case<br /> - how much memory on the machine<br /> - query and explain analyze<br/><br /> Thanks!<br /><br /> - Luke<br /><br /> Msg is shrt cuz m on ma treo<br /><br /> -----Original Message-----<br/> From: Grzegorz Jaskiewicz [<a href="mailto:gj@pointblue.com.pl">mailto:gj@pointblue.com.pl</a>]<br />Sent: Saturday, March 17, 2007 05:16 PM Eastern Standard Time<br /> To: Joshua D.Drake<br /> Cc: Heikki Linnakangas;PostgreSQL-development Hackers<br /> Subject: Re: [HACKERS] [PATCHES] Bitmapscan changes<br /><br /> Thisis on dual ultra 2 sparc. with ultrawide 320 scsi drives. 512MB <br /> ram.<br /> I had to drop size of DB, because theDB drive is 4GB (I do welecome <br /> bigger drives as donation, if someone asks - UWscsi 320).<br /><br /> here are myresults. With only 4.2 patch (no maintain cluster order <br /> v5 patch). If the v5 patch was needed, please tell me -I am going <br /> rerun it with.<br /><br /> hope it is usefull.<br /><br /> Repeat 3 times to ensure repeatable results.<br/> Timing is on.<br /> select_with_normal_index<br /> --------------------------<br /> 100000<br/> (1 row)<br /><br /> Time: 1727891.334 ms<br /> select_with_normal_index<br /> --------------------------<br /> 100000<br /> (1 row)<br /><br /> Time: 1325561.252 ms<br /> select_with_normal_index<br /> --------------------------<br/> 100000<br /> (1 row)<br /><br /> Time: 1348530.100 ms<br /> Timing isoff.<br /> And now run the same tests with clustered index<br /> Timing is on.<br /> select_with_clustered_index<br />-----------------------------<br /> 100000<br /> (1 row)<br /><br /> Time: 870246.856 ms<br /> select_with_clustered_index<br/> -----------------------------<br /> 100000<br /> (1 row)<br /><br/> Time: 477089.456 ms<br /> select_with_clustered_index<br /> -----------------------------<br /> 100000<br /> (1 row)<br /><br /> Time: 381880.965 ms<br /> Timing is off.<br /><br /><br /><br /><br/> ---------------------------(end of broadcast)---------------------------<br /> TIP 9: In versions below 8.0, theplanner will ignore your desire to<br /> choose an index scan if your joining column's datatypes do not<br /> match<br /><br /></font>
On Mar 17, 2007, at 10:33 PM, Luke Lonergan wrote: > Wow, nice! > > Can you tell us: > - how big is the table > - cardinality of the column > - how big is the index in each case > - how much memory on the machine > - query and explain analyze > All I changed, was the 400k to 150k 512MB of ram, as I said earlier. And it is running 64bit kernel, 32bit user-land on linux 2.6.20 query and explain is going to run for a while, so I'll leave it - as it is going to be the same on other machines (much faster ones). postgres=# select pg_size_pretty( pg_relation_size ( 'narrowtable_index' ) ); pg_size_pretty ---------------- 321 MB (1 row) postgres=# select pg_size_pretty( pg_relation_size ( 'narrowtable2_clustered_index' ) ); pg_size_pretty ---------------- 3960 kB (1 row) (so there's quite a difference). Judging from noises coming out of machine, there was pretty loads of I/O activity. and funny enough, one CPU was stucked on 'wait' up to 80% most of the time. the 'cardinality', as I guess, uniqueness is the same as intended in original test. Like I said, only table size was changed. select count(distinct key) from narrowtable; and select count(*) from narrowtable; are the same - 15000000 hth. -- Grzegorz Jaskiewicz C/C++ freelance for hire