Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eUFEV-0001rN-Tc for pgsql-performance@arkaria.postgresql.org; Wed, 27 Dec 2017 17:11:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eUFEV-0005jj-GL for pgsql-performance@arkaria.postgresql.org; Wed, 27 Dec 2017 17:11:11 +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 1eUFEV-0005jZ-45 for pgsql-performance@lists.postgresql.org; Wed, 27 Dec 2017 17:11:11 +0000 Received: from aibo.runbox.com ([91.220.196.211]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eUFES-0000KD-Cx for pgsql-performance@postgresql.org; Wed, 27 Dec 2017 17:11:10 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=runbox.com; s=rbselector1; h=Content-Type:MIME-Version:Message-ID:Date:Subject: In-Reply-To:References:Cc:To:From; bh=Lughrfo9wDHZQW3NtMfE/8V9ReKCmC2jf0409Kyg/nA=; b=HnsE89ty7GRbhL5wWEkETpva7L mwHpeea757uLmQosZCbjyeqAw+iCGBDqcfFtBckkafx6KdGYTFK10hD/9f6oj0OoUzAv/CSmHEB/Z a5Q83m64wtcEooS8ydAUFvq4hQPI+s2XzwO8I8KpefTSWsnr6iyPuko3lqmAiSKUJHG1Nx52Tyhlj ecJB5V0xlhWXQGfZqkoi1iTEeselLGutPIDCJzJb2tiMsdKKonPdxm4K3T6fA8XNWCAZvvr7K2xTG p71ypORqiksUl6dhAiWkOV5XnMoNQ4zclhl38RMd0A0qbRiU9jOnQ/DTIMaLHe5a7irYQ19g0NxAW 8NfloXQQ==; Received: from [10.9.9.212] (helo=mailfront12.runbox.com) by mailtransmit02.runbox with esmtp (Exim 4.86_2) (envelope-from ) id 1eUFER-0000Su-TB; Wed, 27 Dec 2017 18:11:07 +0100 Received: from cpe-66-27-125-35.san.res.rr.com ([66.27.125.35] helo=seasyslap4) by mailfront12.runbox.com with esmtpsa (uid:561468 ) (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.82) id 1eUFDx-0002Tb-Gb; Wed, 27 Dec 2017 18:10:37 +0100 From: "Mike Sofen" To: "'Jean Baro'" , "'Jeremy Finzel'" Cc: "'Danylo Hlynskyi'" , References: In-Reply-To: Subject: RE: Batch insert heavily affecting query performance. Date: Wed, 27 Dec 2017 09:10:33 -0800 Message-ID: <018c01d37f35$9cee6620$d6cb3260$@runbox.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_018D_01D37EF2.8ECC85B0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQKVBFIp7oUjc1kY6fmexFcEABEuuAJR49SrAMKZmkACNSPHeQHWJC6HAtz2rrgBTRPKowIufXEYoWieeNA= Content-Language: en-us List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_018D_01D37EF2.8ECC85B0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable In my experience, that 77ms will stay quite constant even if your db = grew to > 1TB. Postgres IS amazing. BTW, for a db, you should always = have provisioned IOPS or else your performance can vary wildly, since = the SSDs are shared. =20 Re Lambda: another team is working on a new web app using Lambda calls = and they were also experiencing horrific performance, just like yours (2 = seconds per call). They discovered it was the Lambda connection/spin-up = time causing the problem. They solved it by keeping several = Lambda=E2=80=99s =E2=80=9Chot=E2=80=9D, for an instant = connection=E2=80=A6solved the problem, the last I heard. Google for = that topic, you=E2=80=99ll find solutions. =20 Mike =20 From: Jean Baro [mailto:jfbaro@gmail.com]=20 Sent: Wednesday, December 27, 2017 9:03 AM Sorry guys, =20 The performance problem is not caused by PG.=20 =20 'Index Scan using idx_user_country on public.old_card = (cost=3D0.57..1854.66 rows=3D460 width=3D922) (actual = time=3D3.442..76.606 rows=3D200 loops=3D1)' ' Output: id, user_id, user_country, user_channel, user_role, = created_by_system_key, created_by_username, created_at, = last_modified_at, date_start, date_end, payload, tags, menu, deleted, = campaign, correlation_id' ' Index Cond: (((old_card.user_id)::text =3D '1234'::text) AND = (old_card.user_country =3D 'BR'::bpchar))' ' Buffers: shared hit=3D11 read=3D138 written=3D35' 'Planning time: 7.748 ms' 'Execution time: 76.755 ms' =20 77ms on an 8GB database with 167MM rows and almost 500GB in size is = amazing!! =20 Now we are investigating other bottlenecks, is it the creation of a new = connection to PG (no connection poller at the moment, like PGBouncer), = is it the Lambda start up time? Is it the network performance between = PG and Lambda? =20 I am sorry for wasting your time guys, it helped us to find the problem = though, even if it wasn't a PG problem.=20 =20 BTW, what a performance! I am impressed.=20 =20 Thanks PG community!=20 =20 Em 27 de dez de 2017 14:34, "Jean Baro" > escreveu: Thanks Jeremy, =20 We will provide a more complete EXPLAIN as other people have suggested.=20 =20 I am glad we might end up with a much better performance (currently each = query takes around 2 seconds!). =20 Cheers =20 =20 Em 27 de dez de 2017 14:02, "Jeremy Finzel" > escreveu: =20 =20 The EXPLAIN =20 'Index Scan using idx_user_country on card (cost=3D0.57..1854.66 = rows=3D460 width=3D922)' ' Index Cond: (((user_id)::text =3D '4684'::text) AND (user_country =3D = 'BR'::bpchar))' =20 Show 3 runs of the full explain analyze plan on given condition so that = we can also see cold vs warm cache performance. =20 There is definitely something wrong as there is no way a query like that = should take 500ms. Your instinct is correct there. =20 ------=_NextPart_000_018D_01D37EF2.8ECC85B0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

In my = experience, that 77ms will stay quite constant even if your db grew to = > 1TB.=C2=A0 Postgres IS amazing.=C2=A0 BTW, for a db, you should = always have provisioned IOPS or else your performance can vary wildly, = since the SSDs are shared.

 

Re = Lambda:=C2=A0 another team is working on a new web app using Lambda = calls and they were also experiencing horrific performance, just like = yours (2 seconds per call).=C2=A0 They discovered it was the Lambda = connection/spin-up time causing the problem.=C2=A0 They solved it by = keeping several Lambda=E2=80=99s =E2=80=9Chot=E2=80=9D, for an instant = connection=E2=80=A6solved the problem, the last I heard.=C2=A0 Google = for that topic, you=E2=80=99ll find solutions.

 

Mike

 

From: Jean Baro = [mailto:jfbaro@gmail.com]
Sent: Wednesday, December 27, 2017 = 9:03 AM

Sorry guys,

 

The performance problem is = not caused by PG. 

 

'Index Scan = using idx_user_country on public.old_card  (cost=3D0.57..1854.66 = rows=3D460 width=3D922) (actual time=3D3.442..76.606 rows=3D200 = loops=3D1)'

'  Output: = id, user_id, user_country, user_channel, user_role, = created_by_system_key, created_by_username, created_at, = last_modified_at, date_start, date_end, payload, tags, menu, deleted, = campaign, correlation_id'

'  Index = Cond: (((old_card.user_id)::text =3D '1234'::text) AND = (old_card.user_country =3D = 'BR'::bpchar))'

'  = Buffers: shared hit=3D11 read=3D138 = written=3D35'

'Planning time: = 7.748 ms'

'Execution = time: 76.755 ms'

 

77ms on an 8GB = database with 167MM rows and almost 500GB in size is = amazing!!

 

Now we are = investigating other bottlenecks, is it the creation of a new connection = to PG  (no connection poller at the moment, like PGBouncer), is it = the Lambda start up time? Is it the network performance  between PG = and Lambda?

 

I am sorry for = wasting your time guys, it helped us to find the problem though, even if = it wasn't a PG problem. 

 

BTW, what a = performance! I am impressed. 

 

Thanks PG = community! 

 

Em 27 de dez de 2017 14:34, = "Jean Baro" <jfbaro@gmail.com> = escreveu:

Thanks Jeremy,

 

We will provide a more = complete EXPLAIN as other people have = suggested. 

 

I am glad we might end up = with a much better performance (currently each query takes around 2 = seconds!).

 

Cheers

 

 

Em 27 de dez de 2017 14:02, = "Jeremy Finzel" <finzelj@gmail.com> = escreveu:

 

 

The = EXPLAIN

 

'Index Scan using idx_user_country on = card  (cost=3D0.57..1854.66 rows=3D460 = width=3D922)'

'  Index Cond: (((user_id)::text =3D = '4684'::text) AND (user_country =3D = 'BR'::bpchar))'

 

Show 3 runs of the full = explain analyze plan on given condition so that we can also see cold vs = warm cache performance.

 

There is definitely = something wrong as there is no way a query like that should take = 500ms.  Your instinct is correct = there.

 

------=_NextPart_000_018D_01D37EF2.8ECC85B0--