I am not sure what normal is with postgres.
I'm running this on a postgres instance in Google Cloud where the managed VM, I've tried tweaking even up to 4GB Ram and 4 cpu, but didn't really make any difference.
The query below takes 200-300ms to run each time and that adds up as I am looping for each country_id and so this is called probably 50 times from my JavaScript code:
select
c.community_id,
c.name
from community c
join community_location cl on cl.community_id = c.community_id
join location l on cl.location_id = l.location_id
where l.country_id = 60
Execution Time: 223ms
The tables themselves are very small, no more than 200 rows in community or location.
Is is reasonable for this to take that long? Or is this just a badly formed query?
Here's the explain run of it:
"Nested Loop (cost=1.79..5.54 rows=4 width=40)"
" -> Hash Join (cost=1.65..3.29 rows=4 width=4)"
" Hash Cond: (cl.location_id = l.location_id)"
" -> Seq Scan on community_location cl (cost=0.00..1.50 rows=50 width=8)"
" -> Hash (cost=1.60..1.60 rows=4 width=4)"
" -> Seq Scan on location l (cost=0.00..1.60 rows=4 width=4)"
" Filter: (country_id = 60)"
" -> Index Scan using community_pkey on community c (cost=0.14..0.56 rows=1 width=40)"
" Index Cond: (community_id = cl.community_id)"
I tried also running this query, instead of calling it each time from my JS code, and just trying to hard code with an IN all the country IDs my JavaScript is looping with for country, but same speed basically when I run the query straight up with IN:
select
c.community_id,
c.name
from community c
join community_location cl on cl.community_id = c.community_id
join location l on cl.location_id = l.location_id
where l.country_id in (
6,
8,
9,
26,
23,
32,
35,
44,
60,
64,
66,
77,
81,
83,
93,
116,
123,
131,
137,
138,
142,
153,
164,
169,
178,
184,
185
)
Execution time: 232ms
Execute result:
"Hash Join (cost=5.57..9.91 rows=33 width=40)"
" Hash Cond: (c.community_id = cl.community_id)"
" -> Seq Scan on community c (cost=0.00..3.74 rows=74 width=40)"
" -> Hash (cost=5.15..5.15 rows=33 width=4)"
" -> Hash Join (cost=3.51..5.15 rows=33 width=4)"
" Hash Cond: (cl.location_id = l.location_id)"
" -> Seq Scan on community_location cl (cost=0.00..1.50 rows=50 width=8)"
" -> Hash (cost=3.10..3.10 rows=33 width=4)"
" -> Seq Scan on location l (cost=0.00..3.10 rows=33 width=4)"
" Filter: (country_id = ANY ('{6,8,9,26,23,32,35,44,60,64,66,77,81,83,93,116,123,131,137,138,142,153,164,169,178,184,185}'::integer[]))"
