[GENERAL] slow query on multiple table join - Mailing list pgsql-general

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:

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.

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.

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

ps"table size for query 1: