Re: Minmax indexes (timings)

On Fri, November 15, 2013 17:33, Kevin Grittner wrote:
> Erik Rijkers <er(at)xs4all(dot)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