slow query on multiple table join

hi guys，

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

[cid:part1.F9606D33.54D41E1B@outlook.com]

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:

[cid:part2.B9424DDD.2EC6D3D5@outlook.com]

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.

[cid:part3.6233EAD2.99E4DB84@outlook.com]

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.

[cid:part4.14AD365E.F21D11F6@outlook.com] please kindly let me know there's any solution to solve the problem,thanks a lot!

ps"table size for query 1:

[cid:part5.39061AF3.F36B715A@outlook.com]