Thread: Re: [PERFORM] GIST versus GIN indexes for intarrays

Rusty Conover <rconover@infogears.com> writes:
> The gist__int_ops is the default operator class for integer[] arrays,
> as shown at:
> http://www.postgresql.org/docs/current/static/intarray.html

Ah, so you have contrib/intarray installed.

[ pokes at it... ]  Seems like what we have here is another iteration
of this ancient bug:
http://archives.postgresql.org/pgsql-committers/2004-01/msg00073.php
to wit, contrib/intarray is defining its own @> and <@ operators that
conflict with those since added to the core.  In the case Rusty is
showing, the @> gets resolved as intarray's @> (because that's an
exact match, where the core provides anyarray @> anyarray) and then
this operator is NOT a member of the core-provided GIN opclass for
integer arrays.

The short-term workaround for Rusty is probably to create his GIN
index using the intarray-provided gin__int_ops opclass.  But it
seems to me that we ought to get rid of intarray's @> and <@ operators
and have the module depend on the core anyarray operators, just as we
have already done for = and <>.  Comments?

            regards, tom lane

> The short-term workaround for Rusty is probably to create his GIN
> index using the intarray-provided gin__int_ops opclass.  But it
Right
> seems to me that we ought to get rid of intarray's @> and <@ operators
> and have the module depend on the core anyarray operators, just as we
> have already done for = and <>.  Comments?
Agree, will do. Although built-in anyarray operators have ~N^2 behaviour while 
intarray's version - only N*log(N)
-- 
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
  WWW: http://www.sigaev.ru/
 

On Fri, Feb 13, 2009 at 04:12:53PM +0300, Teodor Sigaev wrote:
>> The short-term workaround for Rusty is probably to create his GIN
>> index using the intarray-provided gin__int_ops opclass.  But it
> Right
>> seems to me that we ought to get rid of intarray's @> and <@ operators
>> and have the module depend on the core anyarray operators, just as we
>> have already done for = and <>.  Comments?
> Agree, will do. Although built-in anyarray operators have ~N^2 behaviour
> while intarray's version - only N*log(N)
Is there a way to have the buily-in anyarray opeators be N*log(N)?

Ken

Teodor Sigaev <teodor@sigaev.ru> writes:
>> seems to me that we ought to get rid of intarray's @> and <@ operators
>> and have the module depend on the core anyarray operators, just as we
>> have already done for = and <>.  Comments?

> Agree, will do. Although built-in anyarray operators have ~N^2 behaviour while
> intarray's version - only N*log(N)

Really?  isort() looks like a bubble sort to me.

But in any case, a pre-sort is probably actually *slower* for small
numbers of array elements.  I wonder where the crossover is.  In
principle we could make the core implementation do a sort when working
with a sortable datatype, but I'm unsure it's worth the trouble.

            regards, tom lane