Over indexing | Postgres.FM 068 |

[ 🇬🇧_🇺🇸 Check out the subtitles - we now edit them, ChatGPT+manually! You can also try KZbin's auto-translation of them from English to your language; try it and share it with people interested in Postgres!]
Nikolay and Michael discuss over-indexing - what we mean by it, the regular issues people discuss about it, as well as a novel one Nikolay has come across and benchmarked recently.
Here are some links to things they mentioned:
* Query planning (docs) www.postgresql.org/docs/curre...
* Statistics used by the planner (docs) www.postgresql.org/docs/curre...
* default_statistics_target www.postgresql.org/docs/curre...
* Optimiser hints discussion (wiki) wiki.postgresql.org/wiki/Opti...
* An example mailing list thread from 2006 www.postgresql.org/message-id...
* Peter Geoghegan tweet regarding invalid index / 1599191964045672449
* plantuner sigaev.ru/git/gitweb.cgi?p=pla...
* pg_hint_plan github.com/ossc-db/pg_hint_plan
* Aurora PostgreSQL query plan management docs.aws.amazon.com/AmazonRDS...
* Building automatic adviser & performance tuning tools - Julien Rouhaud & Tatsuro Yamada: PGCon 2020 (the completely misremembered presentation Michael mentioned 🙈) • Building automatic adv...
* hypopg github.com/HypoPG/hypopg
~~~
What did you like or not like? What should we discuss next time? Let us know in the comments, or by tweeting us on @samokhvalov / samokhvalov and @michristofides / michristofides
~~~
Postgres FM is brought to you by:
- Nikolay Samokhvalov, founder of Postgres.ai postgres.ai/
- Michael Christofides, founder of pgMustard pgmustard.com/
~~~
This is the uncut version, with video. Check out Postgres.fm to find audio-only podcast episodes, edited for convenient listening.

[@maximkondrashin7031]

Hello! Thanks for podcast, it is great! I found it worth mentioning that deletes also might be affected by indexes - if there is a foreign constraint, you have to lookup the referenced table while delete, and indexes are involved in this lookup. I had an issue with slow deletes, and it turned out that it was due to the full scan of a big table referenced by constraint. Adding index on the big table solved the issue.

[@michristofides]

Thank you for the kind words. And you're quite right - we class this as a result of under-indexing, which we decided to cover in the episode this week! kzbin.info/www/bejne/h6i8fnxpotCMqrc