CODEWITHSUNDEEP
postgresqltypesindexing
navige
navige

Reputation: 2517

PostgreSQL: Create an index for fields within a composite type?

Is it possible to have an index on a certain field of a composite type? Assume, for instance, I create a type

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

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

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

Assume I use complex[], would it be possible to have an index over all complex[0], over all complex[1], etc.

Upvotes: 15

Views: 8592

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659367

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

B-tree index on an element of a complex type:

CREATE TABLE tbl (tbl_id serial, co complex);

CREATE INDEX tbl_co_r_idx ON tbl (((co).r)); -- note the parentheses!

db<>fiddle here - with EXPLAIN ANALYZE
Old sqlfiddle

Same thing works for a pre-determined element of an array (not for the whole array!), even for an array of composite type:

CREATE TABLE tbl2 (tbl2_id serial, co complex[]);

CREATE INDEX tbl2_co1_idx ON tbl2 ((co[1])); -- note the parentheses!

Note that the expression-index can only be used for queries if the expression is matched more or less literally.

But that doesn't make sense with a GIN index like you mention. The manual:

GIN stands for Generalized Inverted Index. GIN is designed for handling cases where the items to be indexed are composite values, and the queries to be handled by the index need to search for element values that appear within the composite items.

A GIN index would make sense on an array of complex type as a whole, to let you search for an element within. But you need an implementation for your particular type. Here is a list of examples in standard Postgres (in addition to basic support for all one-dimensional arrays).

Upvotes: 38

Related Questions