Re: Batch insert heavily affecting query performance.

Sorry guys,

The performance problem is not caused by PG.

'Index Scan using idx_user_country on public.old_card (cost=0.57..1854.66
rows=460 width=922) (actual time=3.442..76.606 rows=200 loops=1)'
' 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 = '1234'::text) AND
(old_card.user_country = 'BR'::bpchar))'
' Buffers: shared hit=11 read=138 written=35'
'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(at)gmail(dot)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(at)gmail(dot)com> escreveu:
>
>
>
>> The EXPLAIN
>>
>> 'Index Scan using idx_user_country on card (cost=0.57..1854.66 rows=460
>> width=922)'
>> ' Index Cond: (((user_id)::text = '4684'::text) AND (user_country =
>> '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.
>
>
>