2011/10/8 Thom Brown <thom@xxxxxxxxx>: > On 8 October 2011 21:13, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote: >> 2011/10/8 Thom Brown <thom@xxxxxxxxx>: >>> On 8 October 2011 19:47, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote: >>>>>> I did it. It is strange, so your times are significantly slower than I >>>>>> have. Have you enabled asserts? >>>>> >>>>> The table contains 15 million rows with column values randomly >>>>> selected from the 1-350 range, with 60% within the 1-50 range, and >>>>> asserts are enabled. >>>>> >>>> >>>> Now I repeated tests on litlle bit wide table with 9 milion rows, but >>>> without success. >>>> >>>> Try to disable asserts. I am not sure, but maybe there significantlly >>>> change a speed. >>> >>> Okay, here you go. Results with debug_assertions = false: >>> >>> Index-only scan: 173.389 ms (78.442 ms) >>> Index scan: 184239.399 ms (previously 164882.666 ms) >>> Bitmap scan: 159354.261 ms (previously 154107.415 ms) >>> Sequential scan: 134552.263 ms (previously 121296.999 ms) >>> >>> So no particularly significant difference, except with the index-only >>> scan (which I repeated 3 times and it's about the same each time). >> >> what is size of table? > > 4884MB It has a sense - index only scan it is faster (and significantly faster) on wider tables - or tables with strings where TOAST is not active. Maybe there is a some issue because on thin tables is slower (and I expect a should be faster everywhere). Regards Pavel Stehule > > Here's how I set it up: > > create table stuff (id serial, a text, b int, c int, d text, e text, f > int, g int, h text, thing int); > > insert into stuff (a,b,c,d,e,f,g,h,thing) select 'nfewiufn weoifn > weiou fnwe fnoi weuoifn woeuifniunfeiwodnjnfdoewnfjienfjioe > wneinrienrf',53424524,26575757,'fhdsouhfuisdhfiudshif sdhiuf iu hfius > dhfiu sdiuf dsihfiusdhiufhdsiufhdsiu fhiuds fhiusd fiuds fidsf iuds > fiu dsiuf sdhiu','dfoisdhjfojdsoifjdiosjfoidsjiofjds',52944234,5246463,'fdfidsjoifjdsoifjoidsjfodsjfd',ceil(random()*350) > from generate_series(1,6000000); > > insert into stuff (a,b,c,d,e,f,g,h,thing) select 'nfewiufn weoifn > weiou fnwe fnoi weuoifn woeuifniunfeiwodnjnfdoewnfjienfjioe > wneinrienrf',53424524,26575757,'fhdsouhfuisdhfiudshif sdhiuf iu hfius > dhfiu sdiuf dsihfiusdhiufhdsiufhdsiu fhiuds fhiusd fiuds fidsf iuds > fiu dsiuf sdhiu','dfoisdhjfojdsoifjdiosjfoidsjiofjds',52944234,5246463,'fdfidsjoifjdsoifjoidsjfodsjfd',ceil(random()*50) > from generate_series(1,9000000); > > create index idx_stuff_thing on stuff (thing); > > vacuum analyse stuff; > > Testing without all the extra columns in the table yields a similar > result pattern. > > -- > Thom Brown > Twitter: @darkixion > IRC (freenode): dark_ixion > Registered Linux user: #516935 > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
