[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: [PERFORM] Slow query after 9.3 to 9.6 migration
From: Daniel Blanch Bataller <daniel.blanch.bataller () gmail ! com>
Date: 2016-12-28 13:11:33
Message-ID: 28C57974-472F-403D-8CEA-91F028C6A748 () gmail ! com
[Download RAW message or body]
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 <yoshimit@gmail.com> 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
[Attachment #3 (unknown)]
<html><head><meta http-equiv="Content-Type" content="text/html \
charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; \
-webkit-line-break: after-white-space;" class=""><div dir="auto" style="word-wrap: \
break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" \
class=""><div class="">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 ..</div><div class=""><br class=""></div><div \
class="">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.</div><div class=""><br class=""></div><div class="">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.</div><div class=""><br \
class=""></div><div class="">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.</div><div class=""><br class=""></div><div \
class="">Regards.</div><div class=""><br class=""></div><div class="">Daniel \
Blanch.</div><div class=""><br class=""></div><br class=""><div><blockquote \
type="cite" class=""><div class="">El 28 dic 2016, a las 0:50, Flávio Henrique \
<<a href="mailto:yoshimit@gmail.com" class="">yoshimit@gmail.com</a>> \
escribió:</div><br class="Apple-interchange-newline"><div class=""><div dir="ltr" \
class="">Hi there, fellow experts!<div class=""><br class=""></div><div class="">I \
need an advice with query that became slower after 9.3 to 9.6 migration.</div><div \
class=""><br class=""></div><div class="">First of all, I'm from the dev \
team.</div><div class=""><br class=""></div><div class="">Before migration, we \
(programmers) made some modifications on query bring it's average time from 8s to \
2-3s.</div><div class=""><br class=""></div><div class="">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.</div><div class=""><br class=""></div><div \
class="">Now, after server migration to 9.6 we're experiencing bad times with this \
query again.</div><div class=""><br class=""></div><div class="">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.</div><div \
class=""><br class=""></div><div class="">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.</div><div class=""><br \
class=""></div><div class="">Any tip/help will be much appreciated (even from the \
query side).</div><div class=""><br class=""></div><div class="">Thank you!</div><div \
class=""><br class=""></div><div class="">The query plan: <a \
href="https://explain.depesz.com/s/5KMn" \
class="">https://explain.depesz.com/s/5KMn</a></div><div class=""><br \
class=""></div><div class="">Note: I tried to add index on kilo_victor table already, \
but Postgresql still thinks that is better to do a seq scan.</div><div class=""><br \
class=""></div><div class=""><br clear="all" class=""><div class=""><div \
class="gmail_signature"><div class="">Flávio Henrique</div></div></div> </div></div>
</div></blockquote></div><br class=""></div></body></html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic