Re: [PERFORM] Array performance
Tom Lane wrote: Ruben Rubio Rey [EMAIL PROTECTED] writes: SELECT (array[20]+array[21]+ ... +array[50]+array[51]) as total FROM table WHERE (array[20]+array[21]+ ... +array[50]+array[51])5000 AND array[20]0 AND array[21]0 ... AND array[50]0 AND array[51])0 Any ideas to make this query faster? What's the array datatype? Integer or float would probably go a lot faster than NUMERIC, if that's what you're using now. Already its integer[] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Array performance
Hi, I have a select like SELECT (array[20]+array[21]+ ... +array[50]+array[51]) as total FROM table WHERE (array[20]+array[21]+ ... +array[50]+array[51])5000 AND array[20]0 AND array[21]0 ... AND array[50]0 AND array[51])0 Any ideas to make this query faster? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Array performance
On Fri, Mar 24, 2006 at 01:41:50PM +0100, Ruben Rubio Rey wrote: Hi, I have a select like SELECT (array[20]+array[21]+ ... +array[50]+array[51]) as total FROM table WHERE (array[20]+array[21]+ ... +array[50]+array[51])5000 http://www.varlena.com/GeneralBits/109.php might provide some useful insights. I also recall seeing something about sum operators for arrays, but I can't recall where. AND array[20]0 AND array[21]0 ... AND array[50]0 AND array[51])0 Uhm... please don't tell me that you're using 0 in place of NULL... You might be able to greatly simplify that by use of ANY; you'd need to ditch elements 1-19 though: ... WHERE NOT ANY(array) = 0 See http://www.postgresql.org/docs/8.1/interactive/arrays.html -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Array performance
Jim C. Nasby wrote: On Fri, Mar 24, 2006 at 01:41:50PM +0100, Ruben Rubio Rey wrote: Hi, I have a select like SELECT (array[20]+array[21]+ ... +array[50]+array[51]) as total FROM table WHERE (array[20]+array[21]+ ... +array[50]+array[51])5000 http://www.varlena.com/GeneralBits/109.php might provide some useful insights. I also recall seeing something about sum operators for arrays, but I can't recall where. I ll check it out, seems to be very useful Is faster create a function to sum the array? AND array[20]0 AND array[21]0 ... AND array[50]0 AND array[51])0 Uhm... please don't tell me that you're using 0 in place of NULL... mmm ... i have read in postgres documentation that null values on arrays are not supported ... You might be able to greatly simplify that by use of ANY; you'd need to ditch elements 1-19 though: ... WHERE NOT ANY(array) = 0 Yep this is much better. See http://www.postgresql.org/docs/8.1/interactive/arrays.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Array performance
On Fri, Mar 24, 2006 at 02:01:29PM +0100, Ruben Rubio Rey wrote: http://www.varlena.com/GeneralBits/109.php might provide some useful insights. I also recall seeing something about sum operators for arrays, but I can't recall where. I ll check it out, seems to be very useful Is faster create a function to sum the array? There's been talk of having one, but I don't think any such thing currently exists. AND array[20]0 AND array[21]0 ... AND array[50]0 AND array[51])0 Uhm... please don't tell me that you're using 0 in place of NULL... mmm ... i have read in postgres documentation that null values on arrays are not supported ... Damn, you're right. Another reason I tend to stay away from them... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Array performance
On Fri, Mar 24, 2006 at 07:06:19AM -0600, Jim C. Nasby wrote:
On Fri, Mar 24, 2006 at 02:01:29PM +0100, Ruben Rubio Rey wrote:
mmm ... i have read in postgres documentation that null values on arrays
are not supported ...
Damn, you're right. Another reason I tend to stay away from them...
8.2 will support NULL array elements.
http://archives.postgresql.org/pgsql-committers/2005-11/msg00385.php
http://developer.postgresql.org/docs/postgres/arrays.html
test= SELECT '{1,2,NULL,3,4}'::integer[];
int4
{1,2,NULL,3,4}
(1 row)
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Array performance
Ruben Rubio Rey [EMAIL PROTECTED] writes: SELECT (array[20]+array[21]+ ... +array[50]+array[51]) as total FROM table WHERE (array[20]+array[21]+ ... +array[50]+array[51])5000 AND array[20]0 AND array[21]0 ... AND array[50]0 AND array[51])0 Any ideas to make this query faster? What's the array datatype? Integer or float would probably go a lot faster than NUMERIC, if that's what you're using now. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
