I've got a t3.micro Postgres instance on AWS that is having some massive performance issues.
On my local machine a simple select query runs in 10 seconds. On AWS it runs in 40 seconds. Same query, same number of workers. Performance insights indicates that it may be due to "DataFileRead" load being above my vCPU count, but I'm not sure how to fix that (other than just get a larger instance).
Any help would be much appreciated!
Here are the explain analyze queries:
The "Fast" Query
"QUERY PLAN" "Gather (cost=1000.00..575799.88 rows=17407 width=2062) (actual time=38.317..10467.481 rows=16335 loops=1)" " Workers Planned: 2" " Workers Launched: 2" " -> Parallel Seq Scan on infousa_2019 (cost=0.00..573059.18 rows=7253 width=2062) (actual time=16.792..10343.333 rows=5445 loops=3)" " Filter: ((employees >= '20'::numeric) AND ((market)::text = 'Boston-Cambridge-Newton, MA-NH Metro Area'::text))" " Rows Removed by Filter: 3043343" "Planning Time: 3.478 ms" "Execution Time: 10468.554 ms"
The slow query:
"Gather (cost=1000.00..1036178.66 rows=16967 width=1080) (actual time=23205.945..57724.828 rows=16335 loops=1)" " Workers Planned: 2" " Workers Launched: 2" " -> Parallel Seq Scan on infousa_2019 (cost=0.00..1033481.96 rows=7070 width=1080) (actual time=23179.979..57598.163 rows=5445 loops=3)" " Filter: ((employees >= '20'::numeric) AND ((market)::text = 'Boston-Cambridge-Newton, MA-NH Metro Area'::text))" " Rows Removed by Filter: 3043343" "Planning Time: 25.280 ms" "Execution Time: 57730.038 ms"
