Re: Minmax indexes (timings)

On Mon, November 11, 2013 09:53, Erik Rijkers wrote:
> On Fri, November 8, 2013 21:11, Alvaro Herrera wrote:
>>
>> Here's a version 7 of the patch, which fixes these bugs and adds
>>
>> [minmax-7.patch]
[...]
> some hints about use-case would be expected?
>

I've been messing with minmax indexes some more so here are some results of that.

Perhaps someone finds these timings useful.


Centos 5.7, 32 GB memory, 2 quadcores.

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



Detail is in the attached files; the below is a grep through these.


-- rowcount (size_string):  10_000
     368,640 | size table
     245,760 | size btree index
      16,384 | size minmax index
 Total runtime: 0.167 ms   <-- btree (4x) ( last 2x disabled index-only )
 Total runtime: 0.046 ms
 Total runtime: 0.046 ms
 Total runtime: 0.049 ms
 Total runtime: 0.102 ms   <-- minmax  (4x)
 Total runtime: 0.047 ms
 Total runtime: 0.047 ms
 Total runtime: 0.047 ms
 Total runtime: 1.066 ms   <-- seqscan


-- rowcount (size_string):  100_000
    3,629,056 | size table
    2,260,992 | size btree index
       16,384 | size minmax index
 Total runtime: 0.090 ms   <-- btree (4x) ( last 2x disabled index-only )
 Total runtime: 0.046 ms
 Total runtime: 0.426 ms
 Total runtime: 0.287 ms
 Total runtime: 0.391 ms   <-- minmax (4x)
 Total runtime: 0.285 ms
 Total runtime: 0.285 ms
 Total runtime: 0.291 ms
 Total runtime: 14.065 ms  <-- seqscan


-- rowcount (size_string):  1_000_000
   36,249,600 | size table
   22,487,040 | size btree index
       57,344 | size minmax index
 Total runtime: 0.077 ms    <-- btree (4x) ( last 2x disabled index-only )
 Total runtime: 0.048 ms
 Total runtime: 0.044 ms
 Total runtime: 0.038 ms
 Total runtime: 2.284 ms    <-- minmax (4x)
 Total runtime: 1.812 ms
 Total runtime: 1.813 ms
 Total runtime: 1.809 ms
 Total runtime: 142.958 ms  <-- seqscan


-- rowcount (size_string):  100_000_000
 3,624,779,776 | size table
 2,246,197,248 | size btree index
     4,456,448 | size minmax index
 Total runtime: 0.091 ms      <-- btree (4x) ( last 2x disabled index-only )
 Total runtime: 0.047 ms
 Total runtime: 0.046 ms
 Total runtime: 0.038 ms
 Total runtime: 181.874 ms    <-- minmax (4x)
 Total runtime: 175.084 ms
 Total runtime: 175.104 ms
 Total runtime: 174.349 ms
 Total runtime: 14833.994 ms  <-- seqscan


-- rowcount (size_string):  1_000_000_000
 36,247,789,568 | size table
 22,461,628,416 | size btree index
     44,433,408 | size minmax index
 Total runtime: 14.735 ms     <-- btree (4x) ( last 2x disabled index-only )
 Total runtime: 0.046 ms
 Total runtime: 0.044 ms
 Total runtime: 0.041 ms
 Total runtime: 1790.591 ms   <-- minmax (4x)
 Total runtime: 1750.129 ms
 Total runtime: 1747.987 ms
 Total runtime: 1748.476 ms
 Total runtime: 169770.455 ms <-- seqscan


The messy "program" is attached too (although it still has Jaime's name, the mess is mine).

hth,

Erik Rijkers


PS.
The bug I reported earlier is (of course) still there; but I noticed that it only occurs on larger table sizes (e.g. +1M
rows).