I have found usages of creating indexes with WHERE and INCLUDE operators at some project migrations. The index creation looks like:
CREATE INDEX CONCURRENTLY IF NOT EXISTS some_index
ON some_table USING btree(some_column)
INCLUDE (status)
WHERE status = 'Done';
And the query looks like this:
SELECT * FROM some_table WHERE status = 'Done' and some_column = 'abcd'
I think that INCLUDE operator has no sense here cause there is no filtration on status column and some_index will be applied without some filtration on column if we make query. So index without include will be as faster as index with this operator.
May be there are some other reasons to use INCLUDE in partial index in such situation?
2
Answers
The
INCLUDE
is adding additional columns to an index, without indexing them. So – creation or update of the index will be more expensive than one without. Scanning many items from the index will also probably be slower because more disk or memory needs to be accessed.The advantage is if you want only the included columns then PostgreSQL might be able to avoid fetching the main table pages at all. This might though:
Obviously in your example you are
INCLUDE
ing a column that you then filter on anyway, so I can’t think of any scenario where that makes sense.You use
include
when you want to perform index only scan. Index withinclude
has ability to return the contents of non-key columns without having to visit the index’s tableIn short when you want to leverage the benefit of index-only scan, whatever you put in Select has to be included in
include
In your case, instead of
select *
, useselect status
And yes, to answer your question for the query you have given, index without include will be faster