Re: Minmax indexes (timings)

From: "Erik Rijkers" <er(at)xs4all(dot)nl>
To: alvherre(at)2ndquadrant(dot)com
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Minmax indexes (timings)
Date: 2013-11-15 16:11:46
Message-ID: 1b33314e37c1f9e5b7cfb1f44664af7e.squirrel@webmail.xs4all.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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).

Attachment Content-Type Size
minmax_sizes_times.zip application/zip 6.8 KB
casanova_test.sh application/octet-stream 4.4 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Albe Laurenz 2013-11-15 16:15:38 Re: writable FDWs / update targets confusion
Previous Message Andres Freund 2013-11-15 16:05:29 Re: SSL renegotiation