Proper way to index table columns which can be filtered in multiple ways

Question

I have a comments table with the usual fields in it, i.e. author, title, body, created_at, etc. There's also a field called "user_id" which stores the id of the user the comment is directed at.

On the front end I've created a filter UI that basically allows users to filter comments based on date, whether user has responded to a comment, full text body search, category, etc. All these filters are optional and can be combined to create one fairly large query. It essentially looks like this:

SELECT * FROM comments WHERE user_id = ? AND .... AND ...

My first approach was to index user_id and then leave it at that. However, after reading more about it I'm not sure that's the best way. On the other hand, creating an index for each column that's "filterable" doesn't seem like a good idea either.

Any suggestions?

Edit: Postgres documentation seems to answer my question https://www.postgresql.org/docs/9.6/static/indexes-bitmap-scans.html

• jjanes: Are you having performance problems? If just an index on user_id is good enough, than it is good enough.

Answer 1

Every index adds some value, and comes at a cost. It's up to you to figure out whether the value outweighs the cost.

In order to get a handle on the value, you have to find out which queries use the index, and how much slower those queries run without the index in place.

In order to get a handle on the cost, you have to find out how much slower updates run on the underlying table, and how much space the index itself takes up.

There are tools to help you evaluate these facts. Facts are objective. The value you place on them is not. How much value you place on the query speed up and how much cost you assign to the update slowdown depends on what you are trying to achieve, and on your relative priorities. That's up to you.