2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • 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
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • 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
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • 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
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / 2ndQuadrant3 / 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

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

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 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

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

© 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
×