On 8 October 2011 21:13, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2011/10/8 Thom Brown <thom@linux.com>:
>> On 8 October 2011 19:47, Pavel Stehule <pavel.stehule@gmail.com> 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
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
