From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Scott Marlowe <scott.marlowe@gmail.com>
To: Kumar, Virendra <Virendra.Kumar@guycarp.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Performance of a Query
Date: Wed, 10 Jan 2018 09:51:47 +0100
Message-ID: <1515574307.2848.20.camel@cybertec.at> (raw)
In-Reply-To: <CAOR=d=0cPhUjHhX4wRtBGX6Fu3NRKfMDi4YdTs1nh6DVKeE+Xw@mail.gmail.com>
References: <7b438042521b45f89e510c20a798d22b@USFKL11XG20CN01.mercer.com>
<CAOR=d=0cPhUjHhX4wRtBGX6Fu3NRKfMDi4YdTs1nh6DVKeE+Xw@mail.gmail.com>
Scott Marlowe wrote:
> On Tue, Jan 9, 2018 at 2:18 PM, Kumar, Virendra
> <Virendra.Kumar@guycarp.com> wrote:
> > Hello Gurus,
> >
> > I am struggling to tune a query which is doing join on top of aggregate for
> > around 3 million rows. The plan and SQL is attached to the email.
> >
> > Below is system Details:
> >
> > PGSQL version – 10.1
> >
> > OS – RHEL 3.10.0-693.5.2.el7.x86_64
> >
> > Binary – Dowloaded from postgres.org compiled and installed.
> >
> > Hardware – Virtual Machine with 8vCPU and 32GB of RAM, on XFS filesystem.
>
> I uploaded your query plan here: https://explain.depesz.com/s/14r6
>
> The most expensive part is the merge join at the end.
>
> Lines like this one: "Buffers: shared hit=676 read=306596, temp
> read=135840 written=135972"
>
> Tell me that your sorts etc are spilling to disk, so the first thing
> to try is upping work_mem a bit. Don't go crazy, as it can run your
> machine out of memory if you do. but doubling or tripling it and
> seeing the effect on the query performance is a good place to start.
>
> The good news is that most of your row estimates are about right, so
> the query planner is doing what it can to make the query fast, but I'm
> guessing if you get the work_mem high enough it will switch from a
> merge join to a hash_join or something more efficient for large
> numbers of rows.
Looking at the plan, I'd guess that the following index could be helpful:
CREATE INDEX ON ap.site_exposure(portfolio_id, peril_id, account_id);
Don't know how much it would buy you, but you could avoid the
sequential scan and the sort that way.
Yours,
Laurenz Albe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, scott.marlowe@gmail.com, Virendra.Kumar@guycarp.com
Subject: Re: Performance of a Query
In-Reply-To: <1515574307.2848.20.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox