Sorting in Postgres 15 is 371% faster in certain cases

preview_player
Показать описание
fantastic gains in Postgres 15! improved sorting by 371%. Normally you can avoid sorting by using an index access path. However, you can’t always have an index on every group by, order by, join or distinct predict. This will come handy. Let us discuss

Resources

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)

Introduction to NGINX (link redirects to udemy with coupon)

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

Become a Member on YouTube

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
  1. Hussein Nasser
  2. hussein nasser
  3. backend engineering
  4. postgres
Рекомендации по теме
Sorting in Postgres 0:09:38

Sorting in Postgres 15 is 371% faster in certain cases

5mins of Postgres 0:05:32

5mins of Postgres E19: Speeding up sort performance in Postgres 15

How to optimize 0:03:01

How to optimize the sort in Postgres query?

Scaling Postgres Episode 0:13:51

Scaling Postgres Episode 216 PG 15 Beta 1 Release, Optimized Query Breakdown, Sort Performance

Postgres Planner Quirks: 0:07:31

Postgres Planner Quirks: Incremental Sort, and when it goes wrong

Scaling Postgres Episode 0:18:05

Scaling Postgres Episode 27 | Multiple Databases | Slow Queries | Sorting Performance

5mins of Postgres 0:05:37

5mins of Postgres E21: Server-side backup compression with LZ4 and Zstandard in Postgres 15

Postgres 15 | 0:36:02

Postgres 15 | Postgres.FM 015 | #PostgreSQL #Postgres podcast

Databases: How to 0:03:08

Databases: How to use index to speed up sorting in postgres?

5mins of Postgres 0:05:23

5mins of Postgres E39: Postgres 15 release, and 6 tips for managing indexes

The surprising logic 0:07:39

The surprising logic of the Postgres work_mem setting, and how to tune it

Postgres Sort Instability? 0:19:50

Postgres Sort Instability? | Scaling Postgres 322

5mins of Postgres 0:05:40

5mins of Postgres E49: Speed up Postgres queries with UNIONs and subquery pull-up

Heaps in 3 0:03:29

Heaps in 3 minutes — Intro

5mins of Postgres 0:04:28

5mins of Postgres E37: Postgres 15: Logical replication column filters, database-wide ICU collations

Faster query plans 0:08:35

Faster query plans with Postgres 16: Incremental Sorts, Anti-JOINs and more

Tutorial 14 – 0:13:21

Tutorial 14 – How to use the Order by clause in Postgres

5mins of Postgres 0:05:04

5mins of Postgres E13: New SQL/JSON and JSON_TABLE features in Postgres 15

Scaling Postgres Episode 0:16:34

Scaling Postgres Episode 217 Schema Change Mistakes, Canceling Statements, pg_rman, Pedantry Removal

Scaling Postgres Episode 0:18:54

Scaling Postgres Episode 237 PG 15 Released, File Systems, Connection Pooling, Secure Connections

SQL indexing best 0:04:08

SQL indexing best practices | How to make your database FASTER!

Заметки к релизу 1:02:05

Заметки к релизу PostgreSQL 15 от Postgres Professional

Scaling Postgres Episode 0:16:22

Scaling Postgres Episode 235 Postgres 15 RC1, ICU Collations, Listen & Notify, Understanding TOA...

Explaining the Postgres 0:35:22

Explaining the Postgres Query Optimizer

Комментарии
Автор

In the balmer days, MS was the 1st or 2nd most despicable company in silicon valley imho
Ive watched Satya Nadella slowly shift my opinion in the other direction, but really its about as fast as I'd imagine possible at a behemoth the size of MS
I think hes worthy of recognition for that, it cant have been easy, or the easy thing to do, and I hope everyone there is rewarded for that.

CraneArmy
Автор

Your videos are amazing, man. Thanks for sharing such a detailed knowledge. Keep it up. I have also purchased your database course. Learnt a lot from it.

sohamnavadiya
Автор

Man, that's really cool, looking forward to take some time and investigate a little more on Postgres new release

rafaelmatsumoto
Автор

Microsoft contributing to Postgres may have to something to do with Citusdb.

arsalankhan
Автор

Thanks for the amazing video.. Union needs to remove repetitions so has to sort (or hash as decided by optimiser) whereas union all will not need to.

jasdeepsinghgrover
Автор

@8:16 wouldn't sorting be necessary for UNION due to the fact it removes duplicates? (but not UNION ALL)

benb.
Автор

UNION - I think it's same as the process of merging 2 sorted arrays. Think of it as how merge sort merges the result of next layer in O(N). If the arrays are not sorted, the union would require more than O(N).

codingmohan
Автор

At 7:10 you say Postres is infamous for write amplifications. But how is that specific to Postgres? Won't other databases amplify writes as you have more indexes just as much?

GrantGryczan
Автор

Game changer when this update gets pushed to AlloyDB

JohnMcclaned
Автор

Hi Nasser, can you please make a video or tutorial on Ora2pg installation and also how to use it.

narutoishan
Автор

Can you share the steps for postgres upgrade on version 15

prasadkumbhar
Автор

I am confused.
how did they calculate 371%? Did they considered the db query in postgresql-15 which takes "work_mem" amount of memory i.e. no rewrite on memory. And calculated the same thing for pSQL-14 which takes up extra time because of memory rewrite?

Thank you for the video

monarch
Автор

Can we try it beforehand ?
Maybe, just for fun ?

I'm very excited.

abc_cba
Автор

Hi, what are your thoughts on the Citus extension?

kevinkamau
Автор

You are taking too much time to explain 1-2 straight-forward lines. Information per unit time is very less. May be you can work in that :)

kshitijmittal