4

How does PostgreSQL behave if I create two (or more) indexes of different type on the same column?

In my case I want to compare B-Tree indexes against GIN on a tsvector column. I know that GIN are specifically there for tsvector. What's curious is that PostgreSQL doesn't complaint if I create a B-Tree index, but I see that the query planner doesn't use it. I can also create a GIN index (without removing the B-Tree index) and now the planner uses the newly created index. The column now has two indexes but only one of them is used.

What's the criteria used for selecting an index type against another, even in presence of more than two indexes? Why doesn't PostgreSQL tell me that a B-Tree index on a tsvector is useless and won't be never used by the planner?

Update
The GIN index is used only on certain check conditions like my_tsvector IS NOT NULL but (obviously) not on conditions like my_tsvector @@ '...'::tsquery.

7

An index is going to be used if it's part of the fastest plan the query planner can come up with. Many factors are involved in the cost estimation. Conditio sine qua non: the index must be applicable. Indexes are bound to operators, not data types or functions or anything else. And operators are defined by the operator and data type of left and right operand. The manual about btree indexes:

B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators:

<
<=
=
>=
>

There is more, read the manual.

Related case with more explanation:

A btree index on tsvector is not useless. It can be used to sort rows (implicitly using >= or <= operators):

SELECT * FROM tbl ORDER BY my_tsvector;

Or it can be used for equality predicates:

DELETE FROM tbl WHERE my_tsvector = 'foo bar'::tsvector;

But it cannot be used for a predicate my_tsvector @@ '...'::tsquery. You need a GIN or GiST index for that. Both use the the operator class tsvector_ops by default (no need to declare it explicitly):

Then the index is applicable. But Postgres may still decide to use a different query plan, based on table statistics and cost settings ...

You can have many indexes involving the same index columns. There are various kinds, types and various operator classes, which can be combined in many ways. The query planner will only consider applicable indexes and estimate the cost based on many factors ...

And you can even create the same index any number of times under different names. Then Postgres will take an arbitrary pick. It's your responsibility not to create pointless, duplicated objects.

  • 1
    This is such a great explanation of index appraisal by the optimizer. – Erik Darling Sep 24 '16 at 22:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.