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@xxxxxxxxx> 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!).CheersEm 27 de dez de 2017 14:02, "Jeremy Finzel" <finzelj@xxxxxxxxx> 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.
