Re: [9.2devel] why it doesn't do index scan only? - Mailing list pgsql-general

2011/10/8 Thom Brown <thom@linux.com>:
> 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

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
>