X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id C2F3B9DCD79 for ; Fri, 24 Mar 2006 09:01:48 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 04157-03-2 for ; Fri, 24 Mar 2006 09:01:51 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.rentalia.net (mail.rentalia.com [213.192.209.8]) by postgresql.org (Postfix) with ESMTP id B42069DC897 for ; Fri, 24 Mar 2006 09:01:45 -0400 (AST) Received: (qmail 11315 invoked by uid 514); 24 Mar 2006 15:04:13 +0100 Received: from 62.37.217.236 by rigodon (envelope-from , uid 512) with qmail-scanner-1.25-st-qms (clamdscan: 0.88/1284. spamassassin: 3.0.2. perlscan: 1.25-st-qms. Clear:RC:0(62.37.217.236):SA:0(-2.1/5.0):. Processed in 1.666042 secs); 24 Mar 2006 14:04:13 -0000 X-Antivirus-MYDOMAIN-Mail-From: ruben@rentalia.com via rigodon X-Antivirus-MYDOMAIN: 1.25-st-qms (Clear:RC:0(62.37.217.236):SA:0(-2.1/5.0):. Processed in 1.666042 secs Process 11297) Received: from 62-37-217-236.mad1.adsl.uni2.es (HELO ?192.168.2.28?) (ruben@rentalia.com@62.37.217.236) by mail.rentalia.net with SMTP; 24 Mar 2006 15:04:11 +0100 Message-ID: <4423EDA9.3090502@rentalia.com> Date: Fri, 24 Mar 2006 14:01:29 +0100 From: Ruben Rubio Rey User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051011) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Array performance References: <200603170625.k2H6PJ926955@pillette.com> <1143112188.2810.27.camel@theo-laptop.int.coza.net.za> <20060323132448.GB4881@surnet.cl> <4423E90E.2090607@rentalia.com> <20060324125245.GB90527@pervasive.com> In-Reply-To: <20060324125245.GB90527@pervasive.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.111 required=5 tests=[AWL=0.111] X-Spam-Score: 0.111 X-Spam-Level: X-Archive-Number: 200603/492 X-Sequence-Number: 17929 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 > >