X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 6097E4761D3 for ; Tue, 4 Mar 2003 13:17:28 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 57513-10 for ; Tue, 4 Mar 2003 13:17:26 -0500 (EST) Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) by postgresql.org (Postfix) with ESMTP id A5F18475F2B for ; Tue, 4 Mar 2003 13:17:25 -0500 (EST) Received: from ra (ra [158.250.29.2]) by ra.sai.msu.su (8.9.3/8.9.3) with ESMTP id VAA16694; Tue, 4 Mar 2003 21:17:21 +0300 (MSK) Date: Tue, 4 Mar 2003 21:17:21 +0300 (MSK) From: Oleg Bartunov X-X-Sender: megera@ra.sai.msu.su To: Achilleus Mantzios Cc: Greg Stark , pgsql-sql Subject: Re: Gist indexes on int arrays In-Reply-To: Message-ID: References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new X-Archive-Number: 200303/121 X-Sequence-Number: 12161 On Tue, 4 Mar 2003, Achilleus Mantzios wrote: > 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". no-no, just use contrib/btree_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 > > > > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83