LIMIT vs performance | Postgres.FM 095 |

Ойын-сауық

[ 🇬🇧_🇺🇸 Check out the subtitles - we now edit them, ChatGPT+manually! You can also try KZread'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.

Пікірлер: 3

  • @keenmate9719
    @keenmate971929 күн бұрын

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

  • @kirkwolak6735
    @kirkwolak673519 күн бұрын

    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

    @michristofides

    18 күн бұрын

    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

Келесі