public inbox for [email protected]  

help / color / mirror / Atom feed

From: Merlin Moncure <[email protected]>
To: Flávio Henrique <[email protected]>
Cc: postgres performance list <[email protected]>
Subject: Re: Slow query after 9.3 to 9.6 migration
Date: Thu, 5 Jan 2017 08:40:59 -0600
Message-ID: <CAHyXU0wDi9VjfGC8aQeLsBq4ncLVOKJ=1QR6iRq71U2HXQso4Q@mail.gmail.com> (raw)
In-Reply-To: <CAOGex3nXTRPZTD-KeoSwD=bj62hQrMK+6h30u09srV71sePqUA@mail.gmail.com>
References: <CAOGex3nXTRPZTD-KeoSwD=bj62hQrMK+6h30u09srV71sePqUA@mail.gmail.com>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

On Tue, Dec 27, 2016 at 5:50 PM, Flávio Henrique <[email protected]> wrote:
> Hi there, fellow experts!
>
> I need an advice with query that became slower after 9.3 to 9.6 migration.
>
> First of all, I'm from the dev team.
>
> Before migration, we (programmers) made some modifications on query bring
> it's average time from 8s to 2-3s.
>
> As this query is the most executed on our system (it builds the user panel
> to work), every bit that we can squeeze from it will be nice.
>
> Now, after server migration to 9.6 we're experiencing bad times with this
> query again.
>
> Unfortunately, I don't have the old query plain (9.3 version) to show you,
> but in the actual version (9.6) I can see some buffers written that tells me
> that something is wrong.
>
> Our server has 250GB of memory available, but the database team says that
> they can't do nothing to make this query better. I'm not sure, as some
> buffers are written on disk.
>
> Any tip/help will be much appreciated (even from the query side).
>
> Thank you!
>
> The query plan: https://explain.depesz.com/s/5KMn
>
> Note: I tried to add index on kilo_victor table already, but Postgresql
> still thinks that is better to do a seq scan.

Hard to provide more without the query or the 'old' plan.   Here are
some things you can try:
*) Set effective_io_concurrency high.    You have some heap scanning
going on and this can sometimes help (but it should be marginal).
*) See if you can get any juice out of parallel query
*) try playing with enable_nestloop and enable_seqscan.   these are
hail mary passes but worth a shot.

Run the query back to back with same arguments in the same database
session. Does performance improve?

Big gains (if any) are likely due to indexing strategy.
I do see some suspicious casting, for example:

Join Filter: ((four_charlie.delta_tango)::integer =
(six_quebec.golf_bravo)::integer)

Are you casting in the query or joining through dissimilar data types?
 I suspect your database team might be incorrect.

merlin


-- 
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

reply

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: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Slow query after 9.3 to 9.6 migration
  In-Reply-To: <CAHyXU0wDi9VjfGC8aQeLsBq4ncLVOKJ=1QR6iRq71U2HXQso4Q@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox