Did you know that crocodile eat a lot of candies and need dentists? The best dentists in nature are plover birds ! If crocodiles don't get their teeth cleaned, they can't chew as fast as they would like... While creating a website "doc-croco" I found myself needing indexes.
Really often, what we, developers, 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 most ORMs only use BTree indexes. Often, 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 the crocodiles dentist office, examples of use-cases for each index type will be explained to understand why this type fits best this situation. At the end of the talk, you should be familiar with the internal data structure of indexes, which could help you choose the best index for you data type and query operators !