Session
BackCleaning out crocodiles teeth with PostgreSQL indexes - a story on all the index types in PG
Date: 2018-09-07
Time: 21:30 - 22:20
Room: Mission
Level: Beginner
Feedback: Leave feedback
Have you ever thought that indexes are like birds cleaning out crocodiles' teeth? Well if you think that the crocodile mouth is a table, and teeth are queries, and that those birds help crocodiles be more efficient in chewing things, you could say that they are the indexes of crocodiles... Or something. But different crocodiles, need different birds, and you couldn't ask any bird to do that. It goes the same with indexes. Really often, what we, developers, do when we have a slow query is to create an index on the column that is the most likely to be the reason of our performance problem. Sometimes, some of us, the most adventurous ones, will look into the query plan and find the best index... But a big amount of us do something that would drive DBAs crazy: we trust our ORM to handle the creation of indexes. That's so easy ! Why not use it right?
So what's the problem then ? Well ORMs only use BTree indexes. Most of the time, it's what we need. But why cut ourselves from all the other index types ?
This talk covers PostgreSQL indexes types (B-Tree, GIN, GiST, SP-GiST, BRIN and Hash). Through the very real example of a crocodile dentist office, examples of use-cases for each indexes will be explained to understand why this type fits best this situation. I will also talk about the internal data structure of indexes, which could help you choose the best index for you data type and query operators.