PostgreSQL 13 Beta 1 has been released for testing.
|
1 2 3 4 5 |
postgres=# show server_version_num; server_version_num -------------------- 130000 (1 row) |
PostgreSQL 13 New Features
More than 160 new features have been added in PostgreSQL 13 compared to its previous versions.
This is a multi-post series on PostgreSQL 13 NF, so I would recommend you to subscribe to the mailer given down below so that you get notified whenever I release a new post in the series.
I will just compare the major difference between PostgreSQL 12 (12.3) and PostgreSQL 13 Beta 1 (13.0). However, I am not going to describe all the features here, I will list down the features which plays a significant role in performance improvement.
Hardware/Software used to test the PostgreSQL 13 New Features.
- Operating System : RHEL 6
- Disk : SSD
- PostgreSQL Versions : v12.3 vs v13 Beta 1
New Feature 1: Incremental Sorting
PostgreSQL 13 in its release notes highlighted Incremental sorting as one of the main new features in PostgreSQL 13.
PostgreSQL 13’s incremental sorting, which accelerates sorting data when data that is sorted from earlier parts of a query are already sorted.
For example, you have an index on c1 and you need to sort dataset by c1, c2. Then incremental sort can help you because it wouldn’t sort the whole dataset, but sort individual groups whose have the same value of c1 instead. The incremental sort is extremely helpful when you have a LIMIT clause.
Lets us run the query the following query in PostgreSQL 12 and PostgreSQL 13 Beta 1.
select * from pgbench_accounts order by aid, bid;
here aid has an index whereas bid doesn’t have one.
PostgreSQL 12.3
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
[postgres@dbapath06 ~]$ psql psql (12.3) Type "help" for help. postgres=# SET work_mem TO '64kB'; SET postgres=# explain analyze select * from pgbench_accounts order by aid, bid; QUERY PLAN ---------------------------------------------------------------------------------- Gather Merge (cost=60150.23..99041.91 rows=333334 width=97) (actual time=494.075..707.664 rows=400000 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (cost=59150.20..59566.87 rows=166667 width=97) (actual time=442.889..470.942 rows=133333 loops=3) Sort Key: aid, bid Sort Method: external merge Disk: 16592kB Worker 0: Sort Method: external merge Disk: 13432kB Worker 1: Sort Method: external merge Disk: 11936kB -> Parallel Seq Scan on pgbench_accounts (cost=0.00..8224.67 rows=166667 width=97) (actual time=0.030..37.491 rows=133333 loops=3) Planning Time: 0.052 ms Execution Time: 724.088 ms (11 rows) |
PostgreSQL 13 Beta 1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
[postgres@postgreshelp ~]$ psql psql (13beta1) Type "help" for help. postgres=# SET work_mem TO '64kB'; SET postgres=# explain analyze select * from pgbench_accounts order by aid, bid; QUERY PLAN ---------------------------------------------------------------------- Incremental Sort (cost=0.47..34957.42 rows=400000 width=97) (actual time=0.208..219.769 rows=400000 loops=1) Sort Key: aid, bid Presorted Key: aid Full-sort Groups: 12500 Sort Method: quicksort Average Memory: 29kB Peak Memory: 29kB -> Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.42..16957.42 rows=400000 width=97) (actual time=0.055..146.3 56 rows=400000 loops=1) Planning Time: 0.810 ms Execution Time: 234.531 ms (7 rows) |
By not sorting the entire table at once, but in blocks, it increases the chance that the sorted block will fit in memory, and it will be possible to use quicksort instead of the slower, more demanding external sort.
You can enable or disables the query planner’s use of incremental sort steps with the below parameter. The default is on.
enable_incrementalsort (boolean)
Stay tuned for more new feature test cases, you can do so by subscribing here [email-subscribers-form id=”1″]
Words from postgreshelp
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestions/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.
