To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Gist indexes on int arrays
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2
Date: 03 Mar 2003 16:29:18 -0500
Message-ID: <87wujgi1g1.fsf@stark.dyndns.tv>
Lines: 32
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


> 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?

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