Re: assorted problems with intarray and other GiST contribs. - Mailing list pgsql-sql

From Michael Fuhr
Subject Re: assorted problems with intarray and other GiST contribs.
Date
Msg-id 20050626182540.GA87986@winnie.fuhr.org
Whole thread Raw
In response to assorted problems with intarray and other GiST contribs.  (PFC <lists@boutiquenumerique.com>)
List pgsql-sql
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/


pgsql-sql by date:

Previous
From: PFC
Date:
Subject: Re: assorted problems with intarray and other GiST contribs.
Next
From: davide
Date:
Subject: Re: multiple PK with a non UNIQUE field