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

On 9 October 2011 11:51, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2011/10/9 Thom Brown <thom@linux.com>:
>> On 9 October 2011 04:35, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>>> 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).
>>
>> 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.
>
> then I don't understand

Well here's some more reliable results since each has been run 3
times.  The row size in this sample is a 10th of previous ones (i.e.
1.5 million rows):

-- Narrow table (table with 2 int columns) --

Assertions = on

Index-only scan = 6.088, 5.885, 6.361
Index scan = 65.661, 63.441, 64.105
Bitmap scan = 68.448, 66.476, 64.626
Sequential scan = 244.129, 239.584, 242.680

Assertions = off

Index-only scan = 6.710, 6.709, 6.192
Index scan = 66.838, 67.534, 64.348
Bitmap scan = 75.662, 64.500, 69.080
Sequential scan = 232.065, 231.366, 231.547


-- Wide table (table as described in earlier post) --

Assertions = on

Index-only scan = 7.313, 7.299, 6.401
Index scan = 193.555, 186.564, 198.150
Bitmap scan = 199.082, 204.664, 207.902
Sequential scan = 643.765, 645.426, 621.150

Assertions = off

Index-only scan = 7.569, 6.477, 6.113
Index scan = 197.332, 197.286, 204.257
Bitmap scan = 207.838, 202.235, 203.322
Sequential scan = 653.739, 633.309, 619.081

Bitmap scans tend not to be faster than index scans in this scenario.
These numbers are produced by HEAD as of this morning.

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

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