CODEWITHSUNDEEP
sqlpostgresql
Alexander Andrews
Alexander Andrews

Reputation: 111

Choosing function index or new column index

In my database I would like to search for all results for a certain week number. What is the difference/ which is more efficient:
Using a function index: 

CREATE INDEX week_number_index ON my_table (EXTRACT (WEEK FROM date));

:OR:
Creating a new column with an index storing the week number for each entry.

Upvotes: 0

Views: 56

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

The difference is really in how you want access the data. In both cases, the overhead of maintaining the index occurs on insert/update/delete operations. Any overhead for calculating the value is minimal (in this case).

If you want to use:

where EXTRACT(WEEK FROM date) = :x

Then use a functional index.

If you want to use:

where date_week = :x

then use a column.

More importantly, I think it is bad idea to exclude the year when pulling the week. I would recommend date_trunc('week', date) for either index.

Upvotes: 0

apoteet
apoteet

Reputation: 784

Index expressions like you have in your example are computed on insert. This means that you'd see the same search improvement vs an index on a new column, but without the overhead and mental cost of storing data in a new column. Your expression index is the way to go.

Sources:
https://devcenter.heroku.com/articles/postgresql-indexes#expression-indexes
https://www.postgresql.org/docs/8.1/indexes-expressional.html

Upvotes: 2

Related Questions