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

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?

It is mystic - I created 1.5GB long table, Everywhere I restarted
postgres and I did a reset of system cache and still I have index only
scan little bit slower than index scan.

I use

[root(at)nemesis pavel]# uname -a
Linux nemesis 2.6.35.14-97.fc14.i686 #1 SMP Sat Sep 17 00:34:02 UTC
2011 i686 i686 i386 GNU/Linux

Notebook Dell D830 - 2GB RAM, PostgreSQL used with default
configuration - 24MB Shared buffers

[pavel(at)nemesis ~]$ bonnie++
Writing a byte at a time...done
Writing intelligently...done
Rewriting...done
Reading a byte at a time...done
Reading intelligently...done
start 'em...done...done...done...done...done...
Create files in sequential order...done.
Stat files in sequential order...done.
Delete files in sequential order...done.
Create files in random order...done.
Stat files in random order...done.
Delete files in random order...done.
Version 1.96 ------Sequential Output------ --Sequential Input- --Random-
Concurrency 1 -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP
nemesis 4G 170 99 38853 10 17292 6 1283 94 41249 7 140.2 6
Latency 56039us 1374ms 1647ms 93214us 365ms 429ms
Version 1.96 ------Sequential Create------ --------Random Create--------
nemesis -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP
16 9777 47 +++++ +++ 20363 39 11331 57 +++++ +++ 23478 45
Latency 91217us 1079us 1147us 200us 1148us 199us
1.96,1.96,nemesis,1,1318123502,4G,,170,99,38853,10,17292,6,1283,94,41249,7,140.2,6,16,,,,,9777,47,+++++,+++,20363,39,11331,57,+++++,+++,23478,45,56039us,1374ms,1647ms,93214us,365ms,429ms,91217us,1079us,1147us,200us,1148us,199us

>
> --
> Thom Brown
> Twitter: @darkixion
> IRC (freenode): dark_ixion
> Registered Linux user: #516935
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>