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