SQL IN clauses are miles faster in Postgres 17

Fundamentals of Database Engineering udemy course

Postgres 17 is released! and it brings amazing performance features. In this video I discuss one of those performance features specifically the BTree scan optimization with IN. So if you are using IN clauses, postgres 17 is just faster now with it.

0:00 Intro
1:00 Overview of PG 17 features
5:00 IN Clause in Postgres 16 vs Postgres 17
11:30 How SQL Server Does it
12:35 Postgres 16 IN clause performance
17:08 Postgres 17 IN clause performance

Fundamentals of Operating Systems Course

Backend Troubleshooting Course

Fundamentals of Backend Engineering (link redirects to udemy with coupon)

Fundamentals of Networking for Effective Backends udemy course (link redirects to udemy with coupon)

Fundamentals of Database Engineering udemy course (link redirects to udemy with coupon)

Follow me on Medium

Introduction to NGINX (link redirects to udemy with coupon)

Python on the Backend (link redirects to udemy with coupon)

Become a Member on YouTube

Buy me a coffee if you liked this

Arabic Software Engineering Channel

🔥 Members Only Content


🏭 Backend Engineering Videos in Order

💾 Database Engineering Videos

🎙️Listen to the Backend Engineering Podcast

Gears and tools used on the Channel (affiliates)

🖼️ Slides and Thumbnail Design
Canva


Stay Awesome,
Hussein

[abc_cba]

Did you guys take part in Postgres survey they posted up on their website?

That data helps them develop, so, do contribute to their survey questionnaires.

[ivinsergey]

We always had dots (or slashes) in zeros :)
It came to us from ancient times when you could confuse "o" ("O") with zero. Some early teletypes began using the slashed zero to avoid confusion.
Dots sometimes simulate the slash

[jacob_s]

Since the structure of the tree is log(n), you can cache the nonleaf nodes (and current leaf node that you're on), I can see how you can easily use that to speed up searches while minimizing how many nodes need to be loaded. See which items match up to the current leaf node, then start traversing the cached parent nodes until you see where you need to start searching for the next node in the block.
The most important thing would definitely be to sort the elements in the IN clause, but since most databases place an upper limit on those to just a few thousand anyways, that's generally not an issue.
Very clever of them.

[sebschrader]

15:47 good programming fonts have dots in zeroes to easily distinguish the letter O and the digit 0 from each other.

[Voidstroyer]

Small mistake in your query for postgres 17. You missed the id 4. You did a select where id in (1, 2, 3, 5, 6, 7, 8, 9, 10) number 4 is missing. Whereas in postgres 16 you did have all 10 ids.

I also noticed that even though the number of hits and idx scans in postgres 17 is much lower than 16, the planning time and execution time was higher? (And that was including the mistake above where the query in postgres 17 had 1 less item to find). That seemed a bit weird.

[Alf_GS]

since Postgres uses a heap and the data is not sorted, this means that to effectively use the new logic, you need to use the CLUSTER command more often, right?

[appsky]

Can in be combined with prepared statements for dynamic length? So the planner does not get executed each time. Probably using the "any" function?

[trymoto]

Mocking american accent from Hussein in the end is 🤌

[jaysaha]

I noticed planning time for 17 is little higher than 16. can you explain this impact?

[Oggy-WanKenobi]

Incremental backups is huge I think! You just glossed over it :(

[banksy]

Wondering why in PG17 the planning and execution times were longer? This doesn't seem like an improvement.

[iaminterestedineverything]

Music to my ears.. in clauses are much more human readable compared to exists

[Vic-kycc]

Such an interesting topic completely butchered with a ridiculous presentation style. Which is a shame because @hnasr clearly would be able to do better.