Postgres Planner Quirks: The impact of ORDER BY + LIMIT on index usage

*Find the article on our blog here:*

https://pganalyze.com/blog/5mins-postgres-planner-order-by-limit

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

https://twitter.com/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/questions/110636/poor-performance-on-query-with-limit-when-i-add-an-order-by

*Why is there a Sort after an Index Only Scan? - Mailinglist discussion on pgsql-performance*

https://www.postgresql.org/message-id/flat/014601d8600c%24e15aadc0%24a4100940%24%40webkr.de

*Example of bad ORDER BY + LIMIT on dbfiddle*

https://dbfiddle.uk/D7wiXpbv

*Debugging the Postgres query planner*

https://gocardless.com/blog/debugging-the-postgres-query-planner/

*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=39712211

*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-trick-for-speeding-up-order-by-that-you-probably-shouldn-t-use-4pk5