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 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?

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@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@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
>