PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Using INCLUDE with WHERE in PostgresSQL index

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?

Tags:

2

Answers


  1. 0

    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:

    SELECT status FROM some_table WHERE ... <indexable checks>

    Obviously in your example you are INCLUDEing a column that you then filter on anyway, so I can’t think of any scenario where that makes sense.

    Login or Signup to reply.
  2. 0

    You use include when you want to perform index only scan. Index with include has ability to return the contents of non-key columns without having to visit the index’s table

    In 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 *, use select status

    And yes, to answer your question for the query you have given, index without include will be faster

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search