I want to thank everybody who attended my session “Deep Dive in PostgreSQL Indexes” in Austin. It was quite a wonderful experience! To cover a bigger audience, I also did a webinar on the same topic. There were many questions in that webinar, but unfortunately, there was not enough time to cover each and every question, so I decided to have a followup Q&A session with a blog post on the topic.
The recording and slides are available here. Below is the list of your questions.
Q1: Is there a concept of the functional index in PostgreSQL?
The functional index is when an index is defined on the result of a function. This is present in PostgreSQL 7. In later releases of PostgreSQL, you can define the index on the result of an expression. The expression can be any PostgreSQL expression. The function is a subset of expression.
Q2: Can I create an index using a table row data that is attached to the index? Does my query have to use index-only-scan?
Yes, if all selected columns are in the index, then index-only-scans will be used.
Q3: It’s a little bit hard to choose the right index type. Is there any offer mechanism in Postgres for index types?
Yes, that’s true that is hard to choose, but it all depends on your data and queries types. I have mentioned recommendations in my slides. There is no mechanism for that in PostgreSQL.
Q4: Is it possible to manipulate which index is used by a query if you have multiple indexes?
No, it’s optimizer’s responsibility to choose between defined the indexes. Believe that the PostgreSQL optimizer will choose the best option for you.
Q5: If there’s a need to index a date column, would it be better to use B-TREE or BRIN index?
A date data type is mostly aligned with physical storage. If you are not making many updates and delete operations, then go for the BRIN. But if you have too many updates and deletes, then B-TREE is best for you.
Q6: For time-based filters what kind of index is preferable?
Time is also aligned with physical storage on disk, please see the answer for Q5 above.
Q6: How does the optimizer decide which index to use when multiple indexes are available?
Optimizer calculates the cost of each plan and chooses the best based on the cost.
Q7: How to identify if the index needs to rebuild or not?
You can check the size of an index. Index bloat detecting is a technique. You can check PostgreSQL wiki https://wiki.postgresql.org/wiki/Index_Maintenance
Q8: Can we create a clustered index like Oracle in PostgreSQL? More like the physical order of data in the index?
PostgreSQL offers CLUSTER INDEX using which we can store the data of a table in the order of an Index. However, upon updates/deletes, the data has to be re-clustered to put back in the order of the specified index. However, all the features offered by a clustered index in Oracle are not possible.
Q9: How does GiST index compare to things like Elasticsearch?
If you want to see the comparison of Elasticsearch and PostgreSQL full search text, both are based on inverted indices. I don’t think there should be any significant performance difference, but I don’t have the numbers.
Q10: Is Bitmap index default in PostgreSQL?
No, B-TREE index is default in PostgreSQL.
Q11: If B tree and Hash index work for “=” operator then which one need to select Hash?
According to the documentation, hash performs better for the equality operator. In my opinion, if the column size is bigger then hash perform better than B-TREE, but in the case of smaller size index column B-TREE works better.
Q13: Is there a best practice when to use GIN index and when to use B-Tree with the expression for JSONB columns?
It’s not a matter of choice, there are some cases where you can only use GIN, and B-TREE will not work. There is an example in the slides for the same.
Once again thank you, everybody, for attending the session. If you still have any questions, do attend my session in PostgreSQL Beijing, or write the question in the comment section.
One comment to Q2.
You can create covered index with all fields, more details are here: https://www.postgresql.org/docs/11/indexes-index-only-scans.html