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

On 9 October 2011 04:35, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2011/10/8 Thom Brown <thom(at)linux(dot)com>:
>> 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
>
> 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).

No, that's my point, I re-tested it on a table with just 2 int
columns, and the results are roughly the same. I added all the
columns to make it expensive to fetch the column being queried.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company