PostgreSQL17 beta: B-Tree just got promoted to Index CEO
The release of PostgreSQL17 beta brought a bunch of new interesting features. Improvements to the vacuum execution time, memory consumption, faster ANALYZE, etc.., but the one that most databases and developers will appreciate that also caught my eye right of the bat, are the improvements to the B-Tree Index when using the IN or ANY clauses. I’ve read improvements ranging from 10% to 30% without any change to your database or table structure so I wanted to test it out for one of my production use cases.
The B-Tree is the default index when you don’t specify the type you wan to sue, likely making it the most used Index by the community. Going in-depth on how it works is out of the scope of this performance test but the following articles make a wonderful job describing it:
In short, the patch written by Peter Geoghegan and Matthias van de Meent avoids duplicate leaf page access that will ultimately result in better query plans and faster query overall.
Table
Loaded the following table with 20M records.
Query
Query plan?—?v16
领英推荐
Query plan?—?v17
To note the drop down of buffers shared hit, which translates into smaller number of pages being read. (Smaller is better, for a change)
PgBench?—?v16
PgBench?—?v17
Note:
Depending on how your index is structured you might also see a reduced number on the idx_scan of your tables on pg_stat_all_indexes since in cases of duplicate leaf pages, PostgreSQL 17 wouldn’t need to traverse the Index Tree again and therefore producing a smaller number of scans.
Conclusion
The query plan execution shows a ~32% improvement on PostgreSQL and PgBench more than 100% improvement in the number of transactions possible over the same query in a period of 300 seconds. It’s possible that you can see a bigger overall improvement of your database performance depending on how your workload and how your database is structured but these values are definitely promising.