X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C4520475FB9 for ; Tue, 4 Mar 2003 02:35:40 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 09584-09 for ; Tue, 4 Mar 2003 02:35:38 -0500 (EST) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id 84AF5475FC1 for ; Tue, 4 Mar 2003 02:35:21 -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 h24BgFIs030557; Tue, 4 Mar 2003 09:42:15 -0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.5/8.12.5/Submit) with ESMTP id h24BgEYF030553; Tue, 4 Mar 2003 09:42:14 -0200 Date: Tue, 4 Mar 2003 09:42:13 -0200 (GMT+2) From: Achilleus Mantzios To: Greg Stark Cc: pgsql-sql Subject: Re: Gist indexes on int arrays In-Reply-To: <87wujgi1g1.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/108 X-Sequence-Number: 12148 On 3 Mar 2003, Greg Stark wrote: > > > What do you mean?? > > GiST indexing just indexes columns of type *array* for the &&,=,@,~,@@, > > etc.. operators. > > Hm, you're right of course. I wonder where I got the idea that it didn't > handle these operators. > > This is fascinating and could be useful for something I'm working on. > > How do gist indexes interact with more normal data types to index? I have a > situation where I have a table with millions of records, and I'm mostly > operating on a subset of those records, usually 1k-10k of them. > > The queries would look like > > WHERE foo_id = ? > AND '{1}'::integer[] ~ attr_a > AND '{2}'::integer[] ~ attr_b > > Right now I'm using the contrib/array *= operator and I have an index on > foo_id. Having to scan through up to 10,000 records isn't great but isn't too > bad. I wonder whether having a gist index and using the ~ operator would be > worthwhile? Absolutely. 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 (you must pay attention to nulls and out of bound situations), and a function "last" that returns the last element. Then you could have normal btree indexes on first(attr_a), and on last(attr_b), but unfortunately not an index on both. > > The contrib/array, contrib/intagg, and contrib/intarray directories seem to > all be aimed at handling the same thing and seem to provide mostly > complementary features. Perhaps they should all be merged into one package. I > guess it does show there's lots of demand for this type of datatype. > > -- > greg > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > ================================================================== 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