Date: Sun, 26 Jun 2005 12:25:40 -0600
From: Michael Fuhr <mike@fuhr.org>
To: PFC <lists@boutiquenumerique.com>
Cc: pgsql-sql@postgresql.org, teodor@sigaev.ru, oleg@sai.msu.su
Subject: Re: assorted problems with intarray and other GiST contribs.
Message-ID: <20050626182540.GA87986@winnie.fuhr.org>
References: <a053b85772b71eaf158e0354551e72d2@biglumber.com>
	<op.sszkzgnith1vuj@localhost>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <op.sszkzgnith1vuj@localhost>

On Sun, Jun 26, 2005 at 06:48:42PM +0200, PFC wrote:
> 
> SELECT int_array_aggregate(id) FROM (SELECT id FROM shop.products LIMIT X)  
> as foo;
> 
> 	This one works fine if X <= 512 and crashes postgres if X > 512.
> 	ie. if the aggregate accumulates more than 512 values it crashes.

I don't get a crash in 8.0.3.  I do see some items regarding
contrib/intagg in the 8.0.3 and 8.0.2 Release Notes, so maybe this
problem has already been fixed.  Do you have an 8.0.3 installation
you could test?

> SELECT int_array_aggregate(product_id),
> 	int_array_aggregate(category_id)
> FROM (SELECT * FROM shop.products_to_categories LIMIT N) as foo;
> 
> 	OK if N <= 8, crashes if N > 9

Works for me in 8.0.3.

> 	int[] & int[]   - returns intersection of arrays
> 
> 	This is a useful function but it is very slow.
> 	Computing the intersection of two int[] of length 100 takes about 40 
> 	ms.

How are you timing the operation?  What kind of hardware are you
using?  I have a 500MHz Pentium III that computes the intersection
of two 100-element arrays in about 0.5ms; in 40ms it can do arrays
of about 1100 elements.  I'm measuring this with a PL/pgSQL function
that does the following:

    t1 := timeofday();
    c := a & b;
    t2 := timeofday();
    dt := t2 - t1;

> 	It would be nice to have functions to :
> 	- know the length of a ltree (ie. number of elements)

Does nlevel() not do what you want?

> 	- access it like an array (ie. get element N).

Does subpath() or subltree() not do what you want?

> 	Also a function returning the comon prefix between two ltrees would 
> 	be  really useful.

Does lca() not do what you want?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/