Re: Minmax indexes (timings)

On Fri, November 15, 2013 17:33, Kevin Grittner wrote:
> Erik Rijkers <er@xs4all.nl> wrote:
>
>> Perhaps someone finds these timings useful.
>
>> '--enable-cassert'
>
> Assertions can really distort the timings, and not always equally
> for all code paths.  Any chance of re-running those tests without
> that?
>

Fair enough.  It seems it doesn't make all that much difference for this case, here are the results:

'--prefix=/var/data1/pg_stuff/pg_installations/pgsql.minmax' '--with-pgport=6444' '--enable-depend' '--with-perl'
'--with-openssl' '--with-libxml'

-- rowcount (size_string):  10_000
     368640 | size table        | 360 kB
     245760 | size btree index  | 240 kB
      16384 | size minmax index | 16 kB
 Total runtime: 0.121 ms
 Total runtime: 0.041 ms
 Total runtime: 0.039 ms
 Total runtime: 0.040 ms
 Total runtime: 0.043 ms
 Total runtime: 0.041 ms
 Total runtime: 0.040 ms
 Total runtime: 0.040 ms
 Total runtime: 0.948 ms

-- rowcount (size_string):  100_000
    3629056 | size table        | 3544 kB
    2260992 | size btree index  | 2208 kB
      16384 | size minmax index | 16 kB
 Total runtime: 0.082 ms
 Total runtime: 0.039 ms
 Total runtime: 0.396 ms
 Total runtime: 0.252 ms
 Total runtime: 0.339 ms
 Total runtime: 0.245 ms
 Total runtime: 0.240 ms
 Total runtime: 0.241 ms
 Total runtime: 13.268 ms

-- rowcount (size_string):  1_000_000
   36249600 | size table        | 35 MB
   22487040 | size btree index  | 21 MB
      57344 | size minmax index | 56 kB
 Total runtime: 0.096 ms
 Total runtime: 0.039 ms
 Total runtime: 0.039 ms
 Total runtime: 0.034 ms
 Total runtime: 1.975 ms
 Total runtime: 1.527 ms
 Total runtime: 1.523 ms
 Total runtime: 1.519 ms
 Total runtime: 145.125 ms

-- rowcount (size_string):  100_000_000
 3624779776 | size table        | 3457 MB
 2246197248 | size btree index  | 2142 MB
    4456448 | size minmax index | 4352 kB
 Total runtime: 0.074 ms
 Total runtime: 0.039 ms
 Total runtime: 0.040 ms
 Total runtime: 0.033 ms
 Total runtime: 150.450 ms
 Total runtime: 147.039 ms
 Total runtime: 145.410 ms
 Total runtime: 145.142 ms
 Total runtime: 15068.171 ms

-- rowcount (size_string):  1_000_000_000
 36247789568 | size table        | 34 GB
 22461628416 | size btree index  | 21 GB
    44433408 | size minmax index | 42 MB
 Total runtime: 15.454 ms      <-- 4x btree
 Total runtime: 0.040 ms
 Total runtime: 0.040 ms
 Total runtime: 0.034 ms
 Total runtime: 1502.353 ms    <-- 4x minmax
 Total runtime: 1482.322 ms
 Total runtime: 1489.522 ms
 Total runtime: 1481.424 ms
 Total runtime: 162213.392 ms  <-- seqscan



I'd say minmax indexes give spectacular gains for very small indexsize.


Erik Rijkers