pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
assorted problems with intarray and other GiST contribs. 4+ messages / 3 participants [nested] [flat]
* assorted problems with intarray and other GiST contribs. @ 2005-06-26 16:48 PFC <lists@boutiquenumerique.com> 2005-06-26 17:41 ` Re: assorted problems with intarray and other GiST contribs. Oleg Bartunov <oleg@sai.msu.su> 2005-06-26 18:25 ` Re: assorted problems with intarray and other GiST contribs. Michael Fuhr <mike@fuhr.org> 0 siblings, 2 replies; 4+ messages in thread From: PFC @ 2005-06-26 16:48 UTC (permalink / raw) To: pgsql-sql; +Cc: teodor@sigaev.ru; oleg@sai.msu.su Hello ! I'm using postgresql 8.0.1-r2 on gentoo linux. So, here are the problems : ************************************ * int_array_aggregate crashes 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. On another example query : 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 ************************************ * integer[] intersection is slow 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. in Python for instance, computing a similar intersection takes about 0.1 millisecond, including building both arrays, converting them to sets, and computing the set intersection. Maybe some information can be extracted from the Python souce code. ************************************ * ltree First of all congratulations for this extremely useful datatype and assorted indexes, it really rocks and makes SQL tree handling from nightmare to fun. It would be nice to have functions to : - know the length of a ltree (ie. number of elements) - access it like an array (ie. get element N). This is to be able to iterate over the elements and fetch each of them to get the list of rows which make up a path upto a given object in my tree. Also a function returning the comon prefix between two ltrees would be really useful. Thanks and sorry to come bother you ! Regards, PF Caillaud ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: assorted problems with intarray and other GiST contribs. 2005-06-26 16:48 assorted problems with intarray and other GiST contribs. PFC <lists@boutiquenumerique.com> @ 2005-06-26 17:41 ` Oleg Bartunov <oleg@sai.msu.su> 2005-06-26 17:56 ` Re: assorted problems with intarray and other GiST contribs. PFC <lists@boutiquenumerique.com> 1 sibling, 1 reply; 4+ messages in thread From: Oleg Bartunov @ 2005-06-26 17:41 UTC (permalink / raw) To: PFC <lists@boutiquenumerique.com>; +Cc: pgsql-sql; teodor@sigaev.ru On Sun, 26 Jun 2005, PFC wrote: > > Hello ! > > I'm using postgresql 8.0.1-r2 on gentoo linux. > So, here are the problems : > > ************************************ > * int_array_aggregate crashes I don't remember this function :) > * ltree > > First of all congratulations for this extremely useful datatype and > assorted indexes, it really rocks and makes SQL tree handling from nightmare > to fun. > > It would be nice to have functions to : > - know the length of a ltree (ie. number of elements) int4 nlevel(ltree) - returns level of the node. > - access it like an array (ie. get element N). subltree, subpath > This is to be able to iterate over the elements and fetch each of > them to get the list of rows which make up a path upto a given object in my > tree. > > Also a function returning the comon prefix between two ltrees would > be really useful. ltree lca(ltree,ltree,...) (up to 8 arguments) ltree lca(ltree[]) Returns Lowest Common Ancestor (lca) Have you read README.ltree ? > > > Thanks and sorry to come bother you ! > Regards, > PF Caillaud > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: assorted problems with intarray and other GiST contribs. 2005-06-26 16:48 assorted problems with intarray and other GiST contribs. PFC <lists@boutiquenumerique.com> 2005-06-26 17:41 ` Re: assorted problems with intarray and other GiST contribs. Oleg Bartunov <oleg@sai.msu.su> @ 2005-06-26 17:56 ` PFC <lists@boutiquenumerique.com> 0 siblings, 0 replies; 4+ messages in thread From: PFC @ 2005-06-26 17:56 UTC (permalink / raw) To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: pgsql-sql; teodor@sigaev.ru Thanks for the quick response ! >> * int_array_aggregate crashes > > I don't remember this function :) You mean it's not part of intarray contrib ? I thought so ! Sorry ! Then who's responsible for maintenance of it ? > int4 nlevel(ltree) - returns level of the node. I had missed this one. The summer heat has burnt by brain it seems ;) > >> - access it like an array (ie. get element N). > > subltree, subpath I know, but I was hoping for something like : myltree[pos] instead of the usable, but a bit huge, and probably slower : ltree2text(subltree(myltree, pos-1,pos )); >> Also a function returning the comon prefix between two ltrees would be >> really useful. > > ltree lca(ltree,ltree,...) (up to 8 arguments) > ltree lca(ltree[]) > Returns Lowest Common Ancestor (lca) I know, too, but : test=> SELECT lca( '1.2.3'::ltree, '1.2.4'::ltree ); lca ----- 1.2 (1 ligne) test=> SELECT lca( '1.2.3'::ltree, '1.2'::ltree ); lca ----- 1 (1 ligne) In the case of the 'longest common prefix' I'd need the second SELECT to return the same as the first. This is to determine a 'parent' path that includes a bunch of other paths, in that case, if the parent is part of the original set, I get the parent's parent instead of what I want. > Have you read README.ltree ? Yes ! But I had missed nlevel() to my shame. thanks ! ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: assorted problems with intarray and other GiST contribs. 2005-06-26 16:48 assorted problems with intarray and other GiST contribs. PFC <lists@boutiquenumerique.com> @ 2005-06-26 18:25 ` Michael Fuhr <mike@fuhr.org> 1 sibling, 0 replies; 4+ messages in thread From: Michael Fuhr @ 2005-06-26 18:25 UTC (permalink / raw) To: PFC <lists@boutiquenumerique.com>; +Cc: pgsql-sql; teodor@sigaev.ru; oleg@sai.msu.su 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/ ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-06-26 18:25 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-06-26 16:48 assorted problems with intarray and other GiST contribs. PFC <lists@boutiquenumerique.com> 2005-06-26 17:41 ` Oleg Bartunov <oleg@sai.msu.su> 2005-06-26 17:56 ` PFC <lists@boutiquenumerique.com> 2005-06-26 18:25 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox