Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 42F4F6336CA
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 18 Mar 2009 13:12:35 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 14012-06
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 18 Mar 2009 13:12:32 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp105.biz.mail.mud.yahoo.com (smtp105.biz.mail.mud.yahoo.com
	[68.142.200.253])
	by mail.postgresql.org (Postfix) with SMTP id 2257E6335CF
	for <pgsql-performance@postgreSQL.org>;
	Wed, 18 Mar 2009 13:12:32 -0300 (ADT)
Received: (qmail 64566 invoked from network); 18 Mar 2009 16:12:30 -0000
Received: from unknown (HELO ?192.168.5.110?) (rm_pg@75.37.22.10 with plain)
	by smtp105.biz.mail.mud.yahoo.com with SMTP; 18 Mar 2009 16:12:30 -0000
X-Yahoo-SMTP: acjdBE2swBAAX9ja0RY4hQi0ggqg.Ml3.omUEQJ1xZsuGIb6CRE-
X-YMail-OSG: 
 x2ssk4IVM1n4eB5TVHzw6NLt8lPicOp9_.d73zaAvmx8GT1OdCGkGW.IcOU9p9sfycM5x0IzJzuzoVnfnXW3laC_aSD3cznJKLcOBrrFIzFC2uGaxFyB1_5ef2H56dgacRWvm.WmI9YGrhY4voMtjLl9kUmqfBXoTz2imfP_BvED365WDGVkHl4LQu3YO1ljkMzquq84MtAmVEK9iPrJ8aYHWQ_y62rh3YIzhd4-
X-Yahoo-Newman-Property: ymail-3
Message-ID: <49C11D6D.8020604@cheapcomplexdevices.com>
Date: Wed, 18 Mar 2009 09:12:29 -0700
From: Ron Mayer <rm_pg@cheapcomplexdevices.com>
User-Agent: Thunderbird 2.0.0.19 (X11/20090105)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Ron Mayer <rm_pg@cheapcomplexdevices.com>,
	Oleg Bartunov <oleg@sai.msu.su>,
	Teodor Sigaev <teodor@sigaev.ru>, pgsql-performance@postgreSQL.org
Subject: Re: Extremely slow intarray index creation and inserts.
References: <49BFD950.5040905@cheapcomplexdevices.com>
	<49BFEBD3.4000006@cheapcomplexdevices.com>
	<1804.1237346699@sss.pgh.pa.us>
In-Reply-To: <1804.1237346699@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200903/253
X-Sequence-Number: 33175

Tom Lane wrote:
> Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
>> vm=# create index "gist70000" on tmp_intarray_test using GIST (my_int_array gist__int_ops);
>> CREATE INDEX
>> Time: 2069836.856 ms
> 
>> Is that expected, or does it sound like a bug to take over
>> half an hour to index 70000 rows of mostly 5 and 6-element
>> integer arrays?
> 
> I poked at this example with oprofile.  It's entirely CPU-bound AFAICT,

Oleg pointed out to me (off-list I now see) that it's not totally
unexpected behavior and I should have been using gist__intbig_ops,
since the "big" refers to the cardinality of the entire set (which
was large, in my case) and not the length of the arrays.

Oleg Bartunov wrote:
OB:> it's not about short or long arrays, it's about small or big
OB:> cardinality of the whole set (the number of unique elements)

I'm re-reading the docs and still wasn't obvious to me.   A
potential docs patch is attached below.

> and the CPU utilization is approximately
> 
> 	55%	g_int_compress
> 	35%	memmove/memcpy (difficult to distinguish these)
> 	 1%	pg_qsort
> 	<1%	anything else
> 
> Probably need to look at reducing the number of calls to g_int_compress
> ... it must be getting called a whole lot more than once per new index
> entry, and I wonder why that should need to be.

Perhaps that's a separate issue, but we're working
fine with gist__intbig_ops for the time being.



Here's a proposed docs patch that makes this more obvious.

*** a/doc/src/sgml/intarray.sgml
--- b/doc/src/sgml/intarray.sgml
***************
*** 239,245 ****
     <literal>gist__int_ops</> (used by default) is suitable for
     small and medium-size arrays, while
     <literal>gist__intbig_ops</> uses a larger signature and is more
!    suitable for indexing large arrays.
    </para>

    <para>
--- 239,247 ----
     <literal>gist__int_ops</> (used by default) is suitable for
     small and medium-size arrays, while
     <literal>gist__intbig_ops</> uses a larger signature and is more
!    suitable for indexing high-cardinality data sets - where there
!    are a large number of unique elements across all rows being
!    indexed.
    </para>

    <para>