From: | Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
---|---|
To: | Greg Stark <gsstark(at)mit(dot)edu> |
Cc: | pgsql-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Gist indexes on int arrays |
Date: | 2003-03-04 11:42:13 |
Message-ID: | Pine.LNX.4.44.0303040934040.26498-100000@matrix.gatewaynet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)postgresql(dot)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(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr
From | Date | Subject | |
---|---|---|---|
Next Message | Douglas Trainor | 2003-03-04 12:43:50 | Re: SQL books |
Previous Message | Gary Stainburn | 2003-03-04 11:19:30 | Re: Insert multiple Rows |