slow query on multiple table join

hi guys，

I met a query performance issue in postgresql 9.6.2 with multiple tables
joined.

there were 2 slow queries,and the reasons were the same:the optimizer
generate a bad explain which using nest loop.

attached is the query and its explain.all tables are small and the
indexes were only created on primary keys .

in query 1 I noticed the explain forecast the nest loop anti join
return 1 row as below,it was the result of (f join p) join pt:

-> Nested Loop (cost=1.95..14838.66 rows=1 width=163)
Join Filter: ((f.shop)::text = (s.uuid)::text)
-> Nested Loop Anti Join (cost=1.95..14743.60 rows=1
width=111)
-> Hash Join (cost=1.53..12067.46 rows=4751
width=115)
Hash Cond: ((p.shop)::text = (f.shop)::text)
-> Seq Scan on shopsku p
(cost=0.00..11483.96 rows=106892 width=106)
Filter: ((state)::text = 'normal'::text)
-> Hash (cost=1.29..1.29 rows=19 width=9)
-> Seq Scan on shopfranchise f
(cost=0.00..1.29 rows=19 width=9)
Filter: (enabled = 1)
-> Index Only Scan using platformsku_pkey on
platformsku pt (cost=0.42..0.55 rows=1 width=36)
Index Cond: (uuid = (p.platformsku)::text)
-> Seq Scan on shop s (cost=0.00..75.58 rows=1558
width=61)
-> Hash (cost=2823.76..2823.76 rows=43376 width=46)
-> Seq Scan on merchantsku m (cost=0.00..2823.76
rows=43376 width=46)

while in analyze explain,it actually returns 57458 row.so higher level
nest loop would get 57458*1558 rows,this cause this query runs for more
than 40 seconds.

-> Nested Loop (cost=1.95..14838.66 rows=1 width=163)
(actual time=0.817..43150.583 rows=57458 loops=1)
Join Filter: ((f.shop)::text = (s.uuid)::text)
Rows Removed by Join Filter: 89462106
-> Nested Loop Anti Join (cost=1.95..14743.60 rows=1
width=111) (actual time=0.060..408.092 rows=57458 loops=1)
-> Hash Join (cost=1.53..12067.46 rows=4751
width=115) (actual time=0.046..174.523 rows=57485 loops=1)
Hash Cond: ((p.shop)::text = (f.shop)::text)
-> Seq Scan on shopsku p
(cost=0.00..11483.96 rows=106892 width=106) (actual time=0.008..107.416
rows=106580 loops=1)
Filter: ((state)::text = 'normal'::text)
Rows Removed by Filter: 429
-> Hash (cost=1.29..1.29 rows=19 width=9)
(actual time=0.026..0.026 rows=20 loops=1)
Buckets: 1024 Batches: 1 Memory
Usage: 9kB
-> Seq Scan on shopfranchise f
(cost=0.00..1.29 rows=19 width=9) (actual time=0.006..0.017 rows=20 loops=1)
Filter: (enabled = 1)
Rows Removed by Filter: 4
-> Index Only Scan using platformsku_pkey on
platformsku pt (cost=0.42..0.55 rows=1 width=36) (actual
time=0.003..0.003 rows=0 loops=57485)
Index Cond: (uuid = (p.platformsku)::text)
Heap Fetches: 0
-> Seq Scan on shop s (cost=0.00..75.58 rows=1558
width=61) (actual time=0.001..0.332 rows=1558 loops=57458)

If I disabled nest loop,ti only use 519 ms.

in query 2 ,the nest loop join also process more than 200m rows,it runs
almost 2 minutes.After disable nest loop,it use hash join,finished in
1.5 sec.

purchase join (shopfranchise f_4 join inventory k)
join gdname
-> Nested Loop Left Join (cost=3972.43..4192.40
rows=1 width=1074) (actual time=268.989..106066.932 rows=45615 loops=1)
Join Filter: (((k.shop)::text =
(purchase.shop)::text) AND ((k.shopsku)::text = (purchase.shopsku)::text))
Rows Removed by Join Filter: 208410367

(shopfranchise f_4 join inventory k) join
gdname
-> Hash Join (cost=3972.43..4165.52 rows=1
width=1112) (actual time=247.088..1754.448 rows=45615 loops=1)
Hash Cond: (((gdname.shop)::text =
(k.shop)::text) AND ((gdname.shopsku)::text = (k.shopsku)::text))
-> CTE Scan on gdname (cost=0.00..140.42
rows=7021 width=1246) (actual time=156.543..1563.121 rows=51153 loops=1)
-> Hash (cost=3925.81..3925.81 rows=3108
width=63) (actual time=90.520..90.520 rows=45622 loops=1)
Buckets: 65536 (originally 4096)
Batches: 1 (originally 1) Memory Usage: 4745kB
shopfranchise f_4 join inventory k
-> Hash Join (cost=1.53..3925.81
rows=3108 width=63) (actual time=0.046..70.173 rows=45622 loops=1)
Hash Cond: ((k.shop)::text =
(f_4.shop)::text)
-> Seq Scan on inventory k
(cost=0.00..3449.47 rows=88747 width=54) (actual time=0.009..22.978
rows=88747 loops=1)
-> Hash (cost=1.29..1.29
rows=19 width=9) (actual time=0.025..0.025 rows=19 loops=1)
Buckets: 1024 Batches:
1 Memory Usage: 9kB
-> Seq Scan on
shopfranchise f_4 (cost=0.00..1.29 rows=19 width=9) (actual
time=0.006..0.017 rows=19 loops=1)
Filter: (enabled = 1)
Rows Removed by
Filter: 4
-> CTE Scan on purchase (cost=0.00..15.36
rows=768 width=196) (actual time=0.001..1.013 rows=4569 loops=45615)

please kindly let me know there's any solution to solve the
problem,thanks a lot!