LIMIT vs performance | Postgres.FM 095 |

[ 🇬🇧_🇺🇸 Check out the subtitles - we now edit them, ChatGPT+manually! You can also try TH-cam's auto-translation of them from English to your language; try it and share it with people interested in Postgres!]
Nikolay and Michael discuss LIMIT in Postgres - what it does, how it can help with performance, and an interesting example where adding it can actually hurt performance(!)
Here are some links to things they mentioned:
* LIMIT considered harmful in PostgreSQL (Twitter thread by Christophe Pettus) / 1413542818673577987
* LIMIT and OFFSET (docs) www.postgresql.org/docs/curre...
* No OFFSET (by Markus Winand) use-the-index-luke.com/no-offset
* LIMIT clause (docs) www.postgresql.org/docs/curre...
~~~
What did you like or not like? What should we discuss next time? Let us know in the comments, or by tweeting us on @postgresfm / postgresfm , @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 video version. Check out postgres.fm to subscribe to the audio-only version, to see the transcript, guest profiles, and more.

[@keenmate9719]

Looking forward for this one... paging and limits, it's like naming and cache invalidation :-)

[@kirkwolak6735]

Michael, thank you for sticking to your guns to get your explanation out there. There is a subtle difference in the AUDIENCE you two seem to be addressing. Nikolay seems to not care about launching a long-running query... Because when he sits down, he likely either knows he has a problem already, OR he's got such deep experience in PG, that he knows to check a few thing before he starts pounding out a query. I believe he implies this when he talks about how he adds the LIMIT based on what he is expecting (eg, when he might be wrong, he will do a LIMIT 2 and let the error guide him).
Whereas you were (IMO) driving from a Novice (like me) who *thought* that just adding a LIMIT was *always* a decent safety approach. And my understanding is currently limited to (LIMIT + Order By = Red Flag). Your point goes deeper than that. So, now I realize the correct formula is:
(LIMIT + (Order By|Index Range Scan) = Red Flag). Meaning the optimizer might be doing what looks like a simple range scan on some column, but it is orthogonal to the data being found, and can quickly become a semi-seq_scan (find first row with the index, and the seq_scan in reverse until the number of records hit the limit... Which may never happen! Making it scan to the beginning/end).
That's two wildly different target audiences. And I could be completely wrong. It's my guess. Of course I look up to both of you, so I apologize if I misstated your positions!

[@michristofides]

Thank you Kirk, for the kind words and the wonderful summary! I think you're spot on, and am glad to hear it was helpful