Let's see how much performance we will gain from Index (B-tree) and the Inverted Index (GIN) in PostgreSQL!
We have a database with 15 million rows and each row has both regular fields, some with B-tree index, and attributes JSONB field with GIN (Generalized Inverted Index) index on it.
We will discover exactly how much performance we can gain for the same data in the different formats. Let's go!
Note: at ~ 9:50, GIN Index took query from ~4000 ms to 2 ms (instead of 0.2 ms). Apologies for the mistake guys!
2023-10-08 update: I have finally finished writing the promised blog post. Enjoy it! binaryigor.com/index-a-crucia...
Repo: github.com/BinaryIgor/code-ex...
Related post about the search space: binaryigor.com/reducing-the-s...
Official Postgresql docs about GIN www.postgresql.org/docs/curre...
#database #databaseprogramming #postgresql
28 сен 2023