Re: [9.2devel] why it doesn't do index scan only?

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