Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cME2n-00080D-2Q for pgsql-performance@arkaria.postgresql.org; Wed, 28 Dec 2016 13:13:25 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cME2m-00074a-Ln for pgsql-performance@arkaria.postgresql.org; Wed, 28 Dec 2016 13:13:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cME16-00043Q-BY for pgsql-performance@postgresql.org; Wed, 28 Dec 2016 13:11:40 +0000 Received: from mail-wj0-x230.google.com ([2a00:1450:400c:c01::230]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cME12-0005sx-24 for pgsql-performance@postgresql.org; Wed, 28 Dec 2016 13:11:38 +0000 Received: by mail-wj0-x230.google.com with SMTP id c11so131997530wjx.3 for ; Wed, 28 Dec 2016 05:11:35 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=I3FzzdbY6F8EvW1tS/1JH7IwWQOt2Ryvfnvii9Cm5P4=; b=Y08G5xJqHA11ySDxkdX4mnkSRjr9PZsCWnaDaIcMGKoMBpUb0bDlBao7MrA67bMFMv S0XpzQaZXupEy2H1T3ZNtyA6I7zGBAHKEFt1jpJjTgpRVFdGDrJNubh0gMvktjNkpGAQ mw/FjUfIjSnyF1tHXFZW7ZYlEiXmu+Ev7RVLIQtpGCdcqFkN3IPbgSIICmPcMpVpK0R7 gWIr7cXaMA/zD47riJf9x579HjPiD24ueBp8LiePbPgmkUY463L9RBtcpk9rRyE3Z8Hy p6dFJZ/ddr9qJf4w+Adw1IjEUKH4VGNJclCSRlcCmTJQLepZPsJLYHDrnrohI1F8Zk2L fxFQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=I3FzzdbY6F8EvW1tS/1JH7IwWQOt2Ryvfnvii9Cm5P4=; b=QFhzf+Awy1S1VgPHp6RRtqoAcqjpO3m6jPOm+Apn9NnPvu+MvNx4/6szFJEgoTNI5P EQcgNftaGKATJIyYIkm/IiJNxpvXa5vmlEESeBoYnvDgekcfmCmJ1qM07IYA8gpjMqOa 2drPrLD3VXGEl5BmcIAcz++fa38Tng35b34ZSr9T2QQUQ4dH3rbOBLcU6pKhHZMxuJN4 WjDHaMdHnJ5PjBOOM5BImB1V7xIhooTIx7je1JmN3PQhf4WLdncV/iTqmPnt2m9D63Rj m2YFqGiwk8ZGjBv/h0FD0vgnt0BNSTM8LHQ8rbEtcA5hfX2ybzm0EiqvfYhQ3RkBel9I 9FnA== X-Gm-Message-State: AIkVDXIwStNE0/jV8F9zeKfNoKJorU22QvttNzTig1oLYobtmiCZJ91l1JQDCFrlyFUtZw== X-Received: by 10.194.198.196 with SMTP id je4mr31099968wjc.25.1482930695077; Wed, 28 Dec 2016 05:11:35 -0800 (PST) Received: from [192.168.1.81] (222.red-81-35-96.dynamicip.rima-tde.net. [81.35.96.222]) by smtp.gmail.com with ESMTPSA id 204sm60665858wmj.7.2016.12.28.05.11.33 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 28 Dec 2016 05:11:34 -0800 (PST) From: Daniel Blanch Bataller Message-Id: <28C57974-472F-403D-8CEA-91F028C6A748@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_FEE1CD7F-38AE-4CF7-A0EF-BBA17D659A9F" Mime-Version: 1.0 (Mac OS X Mail 10.1 \(3251\)) Subject: Re: Slow query after 9.3 to 9.6 migration Date: Wed, 28 Dec 2016 14:11:33 +0100 In-Reply-To: Cc: pgsql-performance@postgresql.org To: =?utf-8?Q?Fl=C3=A1vio_Henrique?= References: X-Mailer: Apple Mail (2.3251) X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --Apple-Mail=_FEE1CD7F-38AE-4CF7-A0EF-BBA17D659A9F Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 The biggest impact on performance you can achieve is by using a = materialized view. if it=E2=80=99s so heavily used as you said, even 2-3 = seconds in a multiuser OLTP environment still unacceptable under my = point of view. I don=E2=80=99t know if this is the case but if you have = 1000 users connecting at 8 am all at the same time =E2=80=A6 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=E2=80=99t change anything related to costs. Regards. Daniel Blanch. > El 28 dic 2016, a las 0:50, Fl=C3=A1vio Henrique = escribi=C3=B3: >=20 > Hi there, fellow experts! >=20 > I need an advice with query that became slower after 9.3 to 9.6 = migration. >=20 > First of all, I'm from the dev team. >=20 > Before migration, we (programmers) made some modifications on query = bring it's average time from 8s to 2-3s. >=20 > 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. >=20 > Now, after server migration to 9.6 we're experiencing bad times with = this query again. >=20 > 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. >=20 > 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. >=20 > Any tip/help will be much appreciated (even from the query side). >=20 > Thank you! >=20 > The query plan: https://explain.depesz.com/s/5KMn = >=20 > Note: I tried to add index on kilo_victor table already, but = Postgresql still thinks that is better to do a seq scan. >=20 >=20 > Fl=C3=A1vio Henrique --Apple-Mail=_FEE1CD7F-38AE-4CF7-A0EF-BBA17D659A9F Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
The biggest impact on performance you can = achieve is by using a materialized view. if it=E2=80=99s so heavily used = as you said, even 2-3 seconds in a multiuser OLTP environment still = unacceptable under my point of view. I don=E2=80=99t know if this is the = case but if you have 1000 users connecting at 8 am all at the same time = =E2=80=A6 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=E2=80=99t change = anything related to costs.

Regards.

Daniel Blanch.


El = 28 dic 2016, a las 0:50, Fl=C3=A1vio Henrique <yoshimit@gmail.com> = escribi=C3=B3:

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.


Fl=C3=A1vio = Henrique

= --Apple-Mail=_FEE1CD7F-38AE-4CF7-A0EF-BBA17D659A9F--