Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 395
navige Avatar asked Feb 23 '13 11:02

navige


People also ask

What is a composite index Postgres?

A “composite index”, also known as “concatenated index”, is an index on multiple columns in a table.

Can we CREATE INDEX on multiple columns in PostgreSQL?

You can create an index on more than one column of a table. This index is called a multicolumn index, a composite index, a combined index, or a concatenated index. A multicolumn index can have maximum 32 columns of a table. The limit can be changed by modifying the pg_config_manual.

How does multi column index work in PostgreSQL?

A multicolumn GiST index can be used with query conditions that involve any subset of the index's columns. Conditions on additional columns restrict the entries returned by the index, but the condition on the first column is the most important one for determining how much of the index needs to be scanned.

Can we CREATE INDEX on partitioned table in Postgres?

We can CREATE INDEX on the partitioned table in PostgreSQL, which will automatically create indexes on each existing child table. Also, the index is created on any new future partition created. Above, we have created partitioned tables up to 3 levels and create indexes on the partitioned tables of different levels.


1 Answers

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

like image 99
Erwin Brandstetter Avatar answered Sep 21 '22 13:09

Erwin Brandstetter