public inbox for [email protected]  

help / color / mirror / Atom feed

From: Daniel Blanch Bataller <[email protected]>
To: Flávio Henrique <[email protected]>
Cc: [email protected]
Subject: Re: Slow query after 9.3 to 9.6 migration
Date: Wed, 28 Dec 2016 14:11:33 +0100
Message-ID: <[email protected]> (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>

The biggest impact on performance you can achieve is by using a materialized view. if it’s so heavily used as you said, even 2-3 seconds in a multiuser OLTP environment still unacceptable under my point of view. I don’t know if this is the case but if you have 1000 users connecting at 8 am all at the same time … it will freeze the app for a while ..

Ask your self: how old data can be? and take into account that you can refresh the materialized view as often as you want, even every 10 secs if you want.

Beides this, there there's still some room for improvement. Perhaps you have not created the right index to avoid seq scans. Have a look at indexes on expressions.

On systems side: ask them if they have not changed anything in effective_cache_size and shared_buffers parameters, I presume they haven’t change anything related to costs.

Regards.

Daniel Blanch.


> El 28 dic 2016, a las 0:50, Flávio Henrique <[email protected]> escribió:
> 
> 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 <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.
> 
> 
> Flávio Henrique

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: <[email protected]>

* 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