pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedArray performance
7+ messages / 4 participants
[nested] [flat]
* Array performance
@ 2006-03-24 12:41 Ruben Rubio Rey <ruben@rentalia.com>
2006-03-24 12:52 ` Re: Array performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-24 15:51 ` Re: Array performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: Ruben Rubio Rey @ 2006-03-24 12:41 UTC (permalink / raw)
To: pgsql-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?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Array performance
2006-03-24 12:41 Array performance Ruben Rubio Rey <ruben@rentalia.com>
@ 2006-03-24 12:52 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-24 13:01 ` Re: Array performance Ruben Rubio Rey <ruben@rentalia.com>
1 sibling, 1 reply; 7+ messages in thread
From: Jim C. Nasby @ 2006-03-24 12:52 UTC (permalink / raw)
To: Ruben Rubio Rey <ruben@rentalia.com>; +Cc: pgsql-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 jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Array performance
2006-03-24 12:41 Array performance Ruben Rubio Rey <ruben@rentalia.com>
2006-03-24 12:52 ` Re: Array performance Jim C. Nasby <jnasby@pervasive.com>
@ 2006-03-24 13:01 ` Ruben Rubio Rey <ruben@rentalia.com>
2006-03-24 13:06 ` Re: Array performance Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 7+ messages in thread
From: Ruben Rubio Rey @ 2006-03-24 13:01 UTC (permalink / raw)
To: pgsql-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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Array performance
2006-03-24 12:41 Array performance Ruben Rubio Rey <ruben@rentalia.com>
2006-03-24 12:52 ` Re: Array performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-24 13:01 ` Re: Array performance Ruben Rubio Rey <ruben@rentalia.com>
@ 2006-03-24 13:06 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-24 13:32 ` Re: Array performance Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 7+ messages in thread
From: Jim C. Nasby @ 2006-03-24 13:06 UTC (permalink / raw)
To: Ruben Rubio Rey <ruben@rentalia.com>; +Cc: pgsql-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 jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Array performance
2006-03-24 12:41 Array performance Ruben Rubio Rey <ruben@rentalia.com>
2006-03-24 12:52 ` Re: Array performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-24 13:01 ` Re: Array performance Ruben Rubio Rey <ruben@rentalia.com>
2006-03-24 13:06 ` Re: Array performance Jim C. Nasby <jnasby@pervasive.com>
@ 2006-03-24 13:32 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 7+ messages in thread
From: Michael Fuhr @ 2006-03-24 13:32 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Ruben Rubio Rey <ruben@rentalia.com>; pgsql-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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Array performance
2006-03-24 12:41 Array performance Ruben Rubio Rey <ruben@rentalia.com>
@ 2006-03-24 15:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-27 07:03 ` Re: Array performance Ruben Rubio Rey <ruben@rentalia.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2006-03-24 15:51 UTC (permalink / raw)
To: Ruben Rubio Rey <ruben@rentalia.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Array performance
2006-03-24 12:41 Array performance Ruben Rubio Rey <ruben@rentalia.com>
2006-03-24 15:51 ` Re: Array performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-03-27 07:03 ` Ruben Rubio Rey <ruben@rentalia.com>
0 siblings, 0 replies; 7+ messages in thread
From: Ruben Rubio Rey @ 2006-03-27 07:03 UTC (permalink / raw)
To: pgsql-performance
Tom Lane wrote:
>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.
>
>
Already its integer[]
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-03-27 07:03 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-24 12:41 Array performance Ruben Rubio Rey <ruben@rentalia.com>
2006-03-24 12:52 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-24 13:01 ` Ruben Rubio Rey <ruben@rentalia.com>
2006-03-24 13:06 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-24 13:32 ` Michael Fuhr <mike@fuhr.org>
2006-03-24 15:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-27 07:03 ` Ruben Rubio Rey <ruben@rentalia.com>
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