From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Greg Stark <gsstark@mit.edu>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Gist indexes on int arrays
Date: Tue, 4 Mar 2003 09:42:13 -0200 (GMT+2)
Message-ID: <Pine.LNX.4.44.0303040934040.26498-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <87wujgi1g1.fsf@stark.dyndns.tv>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, gsstark@mit.edu
Subject: Re: Gist indexes on int arrays
In-Reply-To: <Pine.LNX.4.44.0303040934040.26498-100000@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox