X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D1188476154 for ; Tue, 4 Mar 2003 11:50:20 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 33029-05 for ; Tue, 4 Mar 2003 11:50:19 -0500 (EST) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id 8213D475ED1 for ; Tue, 4 Mar 2003 11:50:18 -0500 (EST) Received: from matrix.gatewaynet.com (matrix.gatewaynet.com [217.19.69.50] (may be forged)) by matrix.gatewaynet.com (8.12.5/8.12.5) with ESMTP id h24KvNIs032655; Tue, 4 Mar 2003 18:57:23 -0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.5/8.12.5/Submit) with ESMTP id h24KvN9k032651; Tue, 4 Mar 2003 18:57:23 -0200 Date: Tue, 4 Mar 2003 18:57:23 -0200 (GMT+2) From: Achilleus Mantzios To: Greg Stark Cc: pgsql-sql Subject: Re: Gist indexes on int arrays In-Reply-To: <87smu3kv7d.fsf@stark.dyndns.tv> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new X-Archive-Number: 200303/116 X-Sequence-Number: 12156 On 4 Mar 2003, Greg Stark wrote: > > Achilleus Mantzios writes: > > > Moreover if your array element positions that you want to compare > > against(e.g attr_a[1], or attr_b[n], where n is the last element) are > > known, then you could have a function "first" that returns > > the first element ... > > Except that's precisely what I'm *not* doing. I'm treating the arrays as sets > and looking for records where the set contains a given value. This is a > denormalized table generated nightly from fully normalized raw data. > > So to simplify it, the query might have clauses like: > > WHERE foo_id = 900 > AND '{5}'::integer[] ~ attribute_set_array > > Right now I have a btree index on (foo_id). > > Can I have a GiST index on (foo_id, attribute_set_array) and have it be just > as fast at narrowing the search to just foo_id = 900 but also speed up the ~ > operation? I am afraid you cant do that easily. (but you can follow recent discussions on the matter). int4 does not have an opclass that can cope with "gist". > > Incidentally, it seems odd that there isn't an operator like ~ but optimized > specifically for searching for a single item. It seems awkward and possibly > unnecessarily slow to have to construct an array for the search parameter > every time. > What do you mean by "slow"? > -- > greg > -- ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-210-8981112 fax: +30-210-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr