Postgres Sort Instability? | Scaling Postgres 322

To get the show notes as well as get notified of new episodes, visit:
www.scalingpostgres.com/episo...
In this episode of Scaling Postgres, we discuss an incremental sort instability issue with the Postgres planner, whether we should use foreign keys, how the visibility map works and how to vacuum the template0 database.
Want to learn more about Postgres performance?
Join my FREE training called Postgres Performance Demystified here: www.scalingpostgres.com/cours...
Timestamps:
00:00 - Intro
00:48 - PostgreSQL Sort estimation instability
04:15 - Postgres Performance Demystified
04:49 - Should we use foreign keys?
07:09 - Making the PostgreSQL visibility map visible
11:28 - Understanding VACUUM, VACUUM template0, and Transaction ID Wraparound
13:35 - Custom ENUM Type Columns and ORDER BY
15:06 - Post-mortem - Postgres "out of shared memory" error
16:35 - Mentoring Program for Code Contributors
16:58 - call for applications: mentoring program for code contributors
17:58 - Crunchy Data PostgreSQL 16 Security Technical Implementation Guide Released by DISA
18:22 - POC: Distributing Trunk Binaries via OCI
18:42 - POC: PGXN Binary Distribution Format
18:53 - When I grow up I want to be a Database Administrator (said no-one ever)
19:23 - Outro
#postgres #postgresql

[@RU-qv3jl]

Neat video again, thanks for sharing.

[@jaimeduncan6167]

Thanks for the overview. The optimizer: that is one reason, a platform like Oracle supports hints, even if the Optimizer becomes more and more intelligent sometimes it does not have, or does not see patterns in the data that can massively impact performance. It's a big conversation, years ago it was rejected by the core team. For me, there are more impactful things like general performance and indirect indexes but once we have a modern engine like Oriole we should revisit this one.

[@hallkbrdz]

Ouch. I didn't realize how postgres would handle this (poorly). That should be a sub-second query, even the first time before any caching.
A hash join and group here really helps, bypassing all sorts.