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(dot)F9606D33(dot)54D41E1B(at)outlook(dot)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(dot)B9424DDD(dot)2EC6D3D5(at)outlook(dot)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(dot)6233EAD2(dot)99E4DB84(at)outlook(dot)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(dot)14AD365E(dot)F21D11F6(at)outlook(dot)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(dot)39061AF3(dot)F36B715A(at)outlook(dot)com]