Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YYI7k-0006wx-AU for pgsql-performance@arkaria.postgresql.org; Wed, 18 Mar 2015 17:51:20 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YYI7j-0008TR-MC for pgsql-performance@arkaria.postgresql.org; Wed, 18 Mar 2015 17:51:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YYI7i-0008TD-7v for pgsql-performance@postgresql.org; Wed, 18 Mar 2015 17:51:18 +0000 Received: from mail-lb0-x229.google.com ([2a00:1450:4010:c04::229]) by magus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YYI7Z-0006kE-Fi for pgsql-performance@postgresql.org; Wed, 18 Mar 2015 17:51:16 +0000 Received: by lbbsy1 with SMTP id sy1so35477052lbb.1 for ; Wed, 18 Mar 2015 10:51:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=iRxzM1CYguyW5YRybtBhhb7V/8El38urFOB+tswKIdU=; b=fSvBE26gWXYfPwDJQrHbZyoqtM3gR8iA1w5CnIPNE07QvcBVyqqPbzqcwAOhy93+zr O8Egux5DtR6nkfeS2swi3LLzDTxtXhGWCCkWxCvJYLnCTAL3Jskrl/BDqps/4IQ0Wtu5 i8jrvfi8bR7wx0QDmc5fmi+7GZrBIpNbbPeA/FY9B4FKdeDO5hpoipdYk9IZ130RmcDG rf2CrmGsClWygRQxMGG1wPwwfeZsJOhtBaVmKXOLQ/olwAcFy+swPWZoOJpB7D3dO6u5 FRcVHLBg9AO7643un8kC47PnSG46RjtqI6ktJoRQhiGrpEKRnJDJiYo9DTv0W3E7Da4V qorw== MIME-Version: 1.0 X-Received: by 10.112.182.69 with SMTP id ec5mr63028957lbc.118.1426701067093; Wed, 18 Mar 2015 10:51:07 -0700 (PDT) Received: by 10.114.1.234 with HTTP; Wed, 18 Mar 2015 10:51:06 -0700 (PDT) In-Reply-To: <9650933705b1a197888f77acbef26728@mail.gmail.com> References: <84a6fe4be04c3ef0a714851977860ac8@mail.gmail.com> <55034A48.4040409@2ndquadrant.com> <849e75d9463938372dd4c516f5c5d89c@mail.gmail.com> <55037CEA.5050906@2ndquadrant.com> <55073272.20209@BlueTreble.com> <55074424.9050506@2ndquadrant.com> <55076FFB.9090105@BlueTreble.com> <55083236.8070900@2ndquadrant.com> <55083D74.4000601@2ndquadrant.com> <55084890.6000907@2ndquadrant.com> <9650933705b1a197888f77acbef26728@mail.gmail.com> Date: Wed, 18 Mar 2015 14:51:06 -0300 Message-ID: Subject: Re: Performance issues From: Felipe Santos To: vjoshi@zetainteractive.com Cc: Tomas Vondra , pgsql-performance Content-Type: multipart/alternative; boundary=001a11c3686e4e0c49051193ba42 X-Pg-Spam-Score: -2.7 (--) 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 --001a11c3686e4e0c49051193ba42 Content-Type: text/plain; charset=UTF-8 2015-03-18 14:31 GMT-03:00 Vivekanand Joshi : > So, here is the first taste of success and which gives me the confidence > that if properly worked out with a good hardware and proper tuning, > PostgreSQL could be a good replacement. > > Out of the 9 reports which needs to be migrated in PostgreSQL, 3 are now > running. > > Report 4 was giving an issue and I will see it tomorrow. > > Just to inform you guys that, the thing that helped most is setting > enable_nestloops to false worked. Plans are now not miscalculated. > > > > > Regards, > Vivek > > -----Original Message----- > From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tomas Vondra > Sent: Tuesday, March 17, 2015 9:00 PM > To: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Performance issues > > On 17.3.2015 16:24, Thomas Kellerer wrote: > > Tomas Vondra schrieb am 17.03.2015 um 15:43: > >> On 17.3.2015 15:19, Thomas Kellerer wrote: > >>> Tomas Vondra schrieb am 17.03.2015 um 14:55: > >>>> (2) using window functions, e.g. like this: > >>>> > >>>> SELECT * FROM ( > >>>> SELECT *, > >>>> ROW_NUMBER() OVER (PARTITION BY touchpoint_execution_id > >>>> ORDER BY FROM max_creation_dt) AS rn > >>>> FROM s_f_touchpoint_execution_status_history > >>>> ) foo WHERE rn = 1 > >>>> > >>>> But estimating this is also rather difficult ... > >>> > >>> > >>> From my experience rewriting something like the above using DISTINCT > >>> ON is usually faster. > >> > >> How do you get the last record (with respect to a timestamp column) > >> using a DISTINCT ON? > > > > You need to use "order by ... desc". See here: > > http://sqlfiddle.com/#!15/d4846/2 > > Nice, thanks! > > > > > Btw: your row_number() usage wouldn't return the "latest" row either. > > It would return the "oldest" row. > > Oh, right. I forgot the DESC in the window. > > > -- > Tomas Vondra http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > "But this is not a production-suitable setting. So what do you think how to get a work around this?" What about creating a read-only replica and apply this setting there? --001a11c3686e4e0c49051193ba42 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable


2015-03-18 14:31 GMT-03:00 Vivekanand Joshi <vjoshi@zetainter= active.com>:
So, here is the first taste = of success and which gives me the confidence
that if properly worked out with a good hardware and proper tuning,
PostgreSQL could be a good replacement.

Out of the 9 reports which needs to be migrated in PostgreSQL, 3 are now running.

Report 4 was giving an issue and I will see it tomorrow.

Just to inform you guys that, the thing that helped most is setting
enable_nestloops to false worked. Plans are now not miscalculated.




Regards,
Vivek

-----Original Message-----
From: pgsql-perfo= rmance-owner@postgresql.org
[mailto:pgsql-per= formance-owner@postgresql.org] On Behalf Of Tomas Vondra
Sent: Tuesday, March 17, 2015 9:00 PM
To: pgsql-performance@p= ostgresql.org
Subject: Re: [PERFORM] Performance issues

On 17.3.2015 16:24, Thomas Kellerer wrote:
> Tomas Vondra schrieb am 17.03.2015 um 15:43:
>> On 17.3.2015 15:19, Thomas Kellerer wrote:
>>> Tomas Vondra schrieb am 17.03.2015 um 14:55:
>>>>=C2=A0 (2) using window functions, e.g. like this:
>>>>
>>>>=C2=A0 =C2=A0 =C2=A0 SELECT * FROM (
>>>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT *,
>>>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ROW_NUMBER(= ) OVER (PARTITION BY touchpoint_execution_id
>>>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ORDER BY FROM max_crea= tion_dt) AS rn
>>>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM s_f_touchpoint_execution_s= tatus_history
>>>>=C2=A0 =C2=A0 =C2=A0 ) foo WHERE rn =3D 1
>>>>
>>>>=C2=A0 =C2=A0 =C2=A0 But estimating this is also rather dif= ficult ...
>>>
>>>
>>> From my experience rewriting something like the above using DI= STINCT
>>> ON is usually faster.
>>
>> How do you get the last record (with respect to a timestamp column= )
>> using a DISTINCT ON?
>
> You need to use "order by ... desc". See here:
> http:/= /sqlfiddle.com/#!15/d4846/2

Nice, thanks!

>
> Btw: your row_number() usage wouldn't return the "latest"= ; row either.
> It would return the "oldest" row.

Oh, right. I forgot the DESC in the window.


--
Tomas Vondra=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 http://www.2ndQuadrant.c= om/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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


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


<= /div>


"But this is not a production-suitable = setting. So what do you think how to
get a work around this?"
What about creating a read-only replica and apply this sett= ing there?
--001a11c3686e4e0c49051193ba42--