2ndQuadrant | PostgreSQL
PostgreSQL Solutions for the Enterprise
+39 0574 159 3000
  • Contact Us
  • EN
    • FR
    • IT
    • ES
    • DE
  • Support & Services
    • Support
      • 24/7 PostgreSQL Support
      • Developer Support
      • IBM Z Production Support
    • DBA Services
      • Remote DBA
      • Database Monitoring
    • Consulting Services
      • Health Check
      • Performance Tuning
      • Database Security Audit
      • PostgreSQL Upgrade
      • Kubernetes for Postgres and BDR
    • Migration Services
      • Migrate to PostgreSQL
      • Migration Assessment
  • Products
    • PostgreSQL with High Availability
    • BDR
    • 2ndQPostgres
    • pglogical
      • Installation instruction for pglogical
      • Documentation
    • repmgr
    • Barman
    • Postgres Cloud Manager
    • SQL Firewall
    • Postgres-XL
    • OmniDB
    • Postgres Installer
    • 2UDA
  • Downloads
    • Postgres Installer
    • 2UDA – Unified Data Analytics
  • Postgres Learning Center
    • Webinars
      • You forgot to put the WHERE in DELETE?
      • BDR Overview
    • Whitepapers
      • Highly Available Postgres Clusters
      • AlwaysOn Postgres
      • BDR
      • PostgreSQL Security Best Practices
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Training
      • Training Catalog and Scheduled Courses
        • Advanced Development & Performance
        • Linux for PostgreSQL DBAs
        • BDR
        • PostgreSQL Database Administration
        • PostgreSQL Data Warehousing & Partitioning
        • PostgreSQL for Developers
        • PostgreSQL Immersion
        • PostgreSQL Immersion for Cloud Databases
        • PostgreSQL Security
        • Postgres-XL-10
        • Practical SQL
        • Replication, Backup & Disaster Recovery
        • Introduction to PostgreSQL and Kubernetes
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • Business Case for PostgreSQL
      • Security Information
    • Events
    • Blog
  • About Us
    • About 2ndQuadrant
    • What Does “2ndQuadrant” Mean?
    • 2ndQuadrant’s Passion for PostgreSQL
    • Ask Simon
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu
You are here: Home / Blog / 2ndQuadrant / Planning to succeed
Simon Riggs

Planning to succeed

April 8, 2016/5 Comments/in 2ndQuadrant, Simon's PlanetPostgreSQL /by Simon Riggs

PostgreSQL 9.6 has a lot of good features; many of the changes are in the SQL planner, aiming to improve performance by carefully selecting the right execution plan. The great thing here is that doing less work makes many queries much, much faster than they were before.

First, we are now using Foreign Key data in the planner to improve estimates.

Next, we are combining aggregates to avoid duplicating effort.

We’re also improving the way that GROUP BY estimation occurs.

And we’re using partial indexes for index-only scans in more cases.

And we’ve improved estimates for distinct rows, leading to more accurate planning of hash joins and other plan types.

Congratulations to my 2ndQuadrant colleagues Tomas Vondra and David Rowley for their insight, rigour and persistence in chasing down these issues.

All of these things were planned. They didn’t occur randomly, they were part of a coordinated attack on planning problems with big data as part of the AXLE project.

Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
5 replies
  1. mbanck
    mbanck says:
    April 8, 2016 at 9:20 am

    While planner changes usually don’t feature prominently in the release announcement/release notes, a line like “Various planner changes have increased the TPC-H score by XXX %” would be taken notice of I think. Now just somebody will have to rerun those benchmarks ofter Beta or RC…

    Reply
  2. Anon
    Anon says:
    April 9, 2016 at 1:20 am

    As an observer, I have been watching and reading the commitfest emails/threads. Amazing performance gains coming.

    The only items im disappointed in not seeing is the POC cache of getsnapshotdata being pushed to 9.7. This shows huge performance gains for x86 (Intel I believe). I thought thats why you guys now run long Alphas, get it out quicker but also allow longer to fine tune things.
    There also seems be a performance bias towards Power8 in the threads which is unfortunate… I hope they start to benchmark 9.6 and 9.7 against AMD as well when it releases its next chip ZEN and ARM towards end of year, when they make decisions about commits.

    It would be nice to see the POC OR-clause as well, but again pushed to 9.7.

    Was hoping all the incredible performance improvements would be in 9.6 since there isn’t any major killer feature for end user in this release unlike the previous releases (JSONB etc in the past). There still seems like there is time to get this stuff in since the buffer spin lock atomic operations has been committed and in that state for quite a while.. so hope that makes it into 9.6 as well.

    Just by 2 cents 🙂

    Reply
  3. robert
    robert says:
    April 9, 2016 at 10:58 am

    I’m loving these developments.

    Reply
  4. Anon
    Anon says:
    April 11, 2016 at 6:26 pm

    So from a query performance perspective. Where is the next lot of best gains MOST LIKELY going to come from in your opinion?
    Better query plan selection (is it that bad currently?), LLVM, GPU, without having to Scale Out.. that we may see in 9.7 and 9.8?
    and how much more performance gain to you think there is to be had? 10%, 50% best case?

    Reply
  5. Alepaes
    Alepaes says:
    April 20, 2016 at 7:43 pm

    “Better query plan selection (is it that bad currently?)”
    Yes, it’s sometimes too bad, specially in aggregates (i.e. nothing pushing predicates). Oracle and SQL Server have a good work in helping bad wrote querys, what is good in times of web frameworks with data abstraction layer.
    Unfortunately, we will haven’t Transparent Table Partitioning in 9.6. This can bring a monster performance gain, IMHO.

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Recent Posts

  • Setting SSL/TLS protocol versions with PostgreSQL 12 November 27, 2019
  • Webinar: Using SSL with PostgreSQL and pgbouncer [Follow Up] November 14, 2019
  • PostgreSQL 12: Implementing K-Nearest Neighbor Space Partitioned Generalized Search Tree Indexes November 5, 2019
  • Webinar: PostgreSQL Partitioning [Follow up] October 28, 2019
  • Postgres-BDR: It is also about fast safe upgrades October 15, 2019

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB kanban logical decoding logical replication monitoring open source performance PG12 pgbarman pgday pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL 11 PostgreSQL11 PostgreSQL 11 New Features postgresql repmgr Recovery release replication sql standby wal webinar
UK +44 (0)870 766 7756

US +1 650 378 1218

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

©2001-2019 2ndQuadrant Ltd. All rights reserved | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Postgres-XL 9.5R1Beta2 Released! LVM: “Unable to create a snapshot smaller than 2 chunks”
Scroll to top
×