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

Question

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.

Advanced index usage, might be a candidate for migration to dba.SE. — Erwin Brandstetter
– Erwin Brandstetter, Commented Feb 23, 2013 at 13:50

Erwin Brandstetter · Accepted Answer · 2022-02-14 23:29:17Z

39

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).

edited Feb 14, 2022 at 23:29

answered Feb 23, 2013 at 13:34

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

navige Over a year ago

Thanks for your very detailed and good answer! Thanks also for the hint regarding GIN, you are completely right there!

Climax Over a year ago

@Erwin Brandstetter your second situation/example is very close to what I need too. Though I need to be able to query rows which have elements in co which match some predicate -> though not necessarily on the whole, but say just the r field. Is there some way to "unnest" the array into a single GIN index, or alternatively somehow a btree index on all internal co.r fields, not just one array entry's r field. So basically ANY on a composite/complex type with the ((co).r) like index.

Erwin Brandstetter Over a year ago

@Climax: Please ask your question as question. You can always link to this one for context, and drop a comment here to link back.

Climax Over a year ago

@ErwinBrandstetter I've created the question in the dba stack dba.stackexchange.com/q/307484/69985

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related