|
Home > Archive > PostgreSQL Performance > March 2006 > Array performance
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Ruben Rubio Rey 2006-03-24, 7:43 am |
| 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
| |
| Jim C. Nasby 2006-03-24, 7:43 am |
| 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/...ive/arrays.html
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 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
| |
| Ruben Rubio Rey 2006-03-24, 7:43 am |
| Jim C. Nasby wrote:
>On Fri, Mar 24, 2006 at 01:41:50PM +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?
>
>
>
>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/...ive/arrays.html
>
>
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Jim C. Nasby 2006-03-24, 7:43 am |
| On Fri, Mar 24, 2006 at 02:01:29PM +0100, Ruben Rubio Rey wrote:
> 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.
> 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 jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 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
| |
| Michael Fuhr 2006-03-24, 7:43 am |
| 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:
>
> Damn, you're right. Another reason I tend to stay away from them...
8.2 will support NULL array elements.
http://archives.postgresql.org/pgsq...11/msg00385.php
http://developer.postgresql.org/doc...res/arrays.html
test=> SELECT '& #123;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
| |
| Tom Lane 2006-03-24, 11:42 am |
| Ruben Rubio Rey <ruben@rentalia.com> 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
| |
| Ruben Rubio Rey 2006-03-27, 3:29 am |
| Tom Lane wrote:
>Ruben Rubio Rey <ruben@rentalia.com> writes:
>
>
>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
|
|
|
|
|