PostgreSQL: create an index for fields in a composite type?

Question

Is it possible to have an index in a specific field of a composite type? Suppose, for example, that I create a type

CREATE TYPE complex AS ( r double precision, i double precision ); 

and would like to have an index (especially Hash / GIST / GIN) over r and one over i , is this possible?

Similarly, is it possible to have an index over the first, second, third, ... element of an array field?

Suppose I use complex[] , is it possible to have an index for all complex[0] for all complex[1] , etc.

Answer 1

Yes, absolutely possible. Use an index to express . The tricky part is the syntax for composite types .

B-tree index for an element of complex type:

 CREATE TABLE tbl (tbl_id serial, co complex); CREATE INDEX tbl_co_r_idx ON tbl (((co).r)); -- note the parentheses! 

SQL Fiddle with EXPLAIN ANALYZE .
The same thing works for array elements, even for an array of a composite type:

 CREATE TABLE tbl2 (tbl2_id serial, co complex[]); CREATE INDEX tbl2_co1_idx ON tbl2 ((co[1])); -- note the parentheses! 

Note that an expression index can only be used for queries if the expression matches more or less literally.

But that doesn't make sense with the GIN index, as you mentioned. In the documentation :

GIN stands for Generalized Inverted Index. The GIN is designed to handle cases where the elements to be indexed are composite values ​​and queries processed by the index must look for the element values ​​that appear in the composite elements.

The GIN index will make sense in an array of a complex type as a whole, so you can search for an element inside. But you need an implementation for your specific type. The following is a list of examples in standard Postgres (in addition to the basic support for all one-dimensional arrays).