Postgres Planner Quirks: The impact of ORDER BY + LIMIT on index usage
Find the article on our blog here:
https://pganalyze.com/blog/5mins-post...
In E113 of "5mins of Postgres" we discuss a commonly encountered Postgres planner quirk, which is how Postgres behaves when you have a LIMIT and an ORDER BY clause, and it picks the wrong index.
Learn more about pganalyze:
https://pganalyze.com
https://pganalyze.com/newsletter
/ pganalyze
Check out the pganalyze library for eBooks, webinars, and more:
https://pganalyze.com/resources
📑 What we have discussed in this episode of 5mins of Postgres:
DBA StackExchange - Poor performance on query with LIMIT when I add an ORDER BY?
https://dba.stackexchange.com/questio...
Why is there a Sort after an Index Only Scan? - Mailinglist discussion on pgsql-performance
https://www.postgresql.org/message-id...
Example of bad ORDER BY + LIMIT on dbfiddle
https://dbfiddle.uk/D7wiXpbv
Debugging the Postgres query planner
https://gocardless.com/blog/debugging...
pg_hint_plan
https://github.com/ossc-db/pg_hint_plan
Recent Hacker News discussion on pg_hint_plan, including discussion of the ORDER BY + LIMIT problem
https://news.ycombinator.com/item?id=...
One WEIRD Trick for Speeding Up ORDER BY That You Probably Shouldn't Use - Blog post by Cassidy on DEV Community
https://dev.to/cassidycodes/one-weird...