A Deep Dive Into PostgreSQL Indexing - PostgreSQL Index Tutorial

Ibrar Ahmed (Percona) delivers the talk, "A Deep Dive Into PostgreSQL Indexing", on DAY 2 of the Percona Live Open Source Database Conference 2019, 5/30, at Austin, TX.
Comment 💬, Share 🔗, Like 👍🏻, and Subscribe ✅ to our channel + Turn on the 🔔
Indexes are a basic feature of relational databases, and PostgreSQL offers a rich collection of options to developers and designers. To take advantage of these fully, users need to understand the basic concept of indexes, to be able to compare the different index types and how they apply to different application scenarios. Only then can you make an informed decision about your database index strategy and design.
One thing is for sure: not all indexes are appropriate for all circumstances, and using a ‘wrong' index can have the opposite effect to that you intend, and problems might only surface once in production. Armed with more advanced knowledge, you can avoid this worst case scenario!
📺 Subscribe To Our Channel + Turn on the 🔔 and Get More videos about #OpenSource #Databases, and many other things.
percona.tv/subscribe
We'll take a look at how to use pg_stat_statment to find opportunities for adding indexes to your database. We'll take a look at when to add an index, and when adding an index is unlikely to result in a good solution.
So should you add an index to every column? Come and discover why this strategy is rarely recommended as we take a deep dive into PostgreSQL indexing.
Download Percona Distribution for PostgreSQL Free! ⏩per.co.na/postgresql
You can find more information about #PostgreSQL #Databases and Percona Live Online 2020 in these links 👇🏻👇🏻👇🏻
per.co.na/postgresql-support
per.co.na/postgresql-info
per.co.na/lGkGc9
per.co.na/pl2020
Connect With us on our Social Networks 👇🏻👇🏻👇🏻
Follow Percona on Twitter: per.co.na/twitter
Follow Percona on Facebook: per.co.na/facebook
Follow Percona on Instagram: per.co.na/instagram
Follow Percona on LinkedIn: per.co.na/linkedin

[@gauravdutta5486]

This is an extremely informative video sir

[@lotsa2000]

Thank you so much for this video!
Regarding 18:14 - Wouldn't it be better to avoid creating an index on this expression and simply rewriting the queries to something like this?
select * from bar where dt < now() - interval '2 days';
This avoids the performance hit and also creating another index on the date column--assuming there's already one on the dt column with no expression.
Of course if there's not already an index on dt, that won't work.
This also allows for much more flexible date queries in the future where you can filter on any interval you like.

[@sridhartn83]

I think got a better understanding of postgres indexes now. I have a question seems all the indexes that we have in postgres are kind of non-clustered indexes that we have in SQL Server, not containing the actual data (in a particular order), so in postgres a table is always a heap with ctid which is referenced by the indexes(for non included columns), is it correct, in postgres a table is always a heap unlike SQL server where we have an option to have a single clustered indexes which actually sorts the table based on the cluster index column? Second question if the underlying table has dead rows and when we run a vacuum on the table does the respective indexes also gets vacuumed? Or do we have to run a separate re-index on them? Thanks.

[@tamdinh6991]

This is very useful information about indexes. Thanks so much.

[@percona]

Thanks for your feedback!
Do not forget to Subscribe To Our Channel 📺 + Turn on the 🔔 and Get More videos about #OpenSource #Databases, and many other things.
percona.tv/subscribe

[@Bruh-jw2ze]

Good slides 👍

[@percona]

Thanks for your feedback!
Do not forget to Subscribe To Our Channel 📺 + Turn on the 🔔 and Get More videos about #OpenSource #Databases
percona.tv/subscribe

[@fxstreamer238]

Its ape not heap according to you