X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 6B510476133 for ; Tue, 4 Mar 2003 10:28:07 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 10708-02 for ; Tue, 4 Mar 2003 10:28:05 -0500 (EST) Received: from sabre.velocet.net (sabre.velocet.net [216.138.209.205]) by postgresql.org (Postfix) with ESMTP id 7B12D47611E for ; Tue, 4 Mar 2003 10:28:05 -0500 (EST) Received: from stark.dyndns.tv (H162.C233.tor.velocet.net [216.138.233.162]) by sabre.velocet.net (Postfix) with ESMTP id 9FF6A1380E0; Tue, 4 Mar 2003 10:28:08 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 18qEKt-0007gU-00; Tue, 04 Mar 2003 10:28:07 -0500 To: Achilleus Mantzios Cc: Greg Stark , pgsql-sql Subject: Re: Gist indexes on int arrays References: In-Reply-To: From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 04 Mar 2003 10:28:06 -0500 Message-ID: <87smu3kv7d.fsf@stark.dyndns.tv> Lines: 30 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new X-Archive-Number: 200303/115 X-Sequence-Number: 12155 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? 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. -- greg