Hello Tao,
I'm not sure it was a bug and I also cloud not explain why it allocated so much memory.Dosn't each sub partition table allocated the size of work_mem memory and not free it?
It can, and it did it for hashed subPlan at least in PG 9.4, see
https://www.slideshare.net/AlexeyBashtanov/postgresql-and-ram-usage/41 Generally, work_mem is per operation, not per query -- that's not a bug
When work_mem=1GB or more,the query plan is a HashAggregate.otherwise it was Unique and running on parallel mode.
I would appreciate if you could send full plans with ANALYZE.
I suspect it's hashed subPlan hashing goodsh.gid separately for each partition, but maybe something else.
NOT IN is generally tricky, both semantically and for planning, rewriting it into NOT EXISTS or LEFT JOIN may change the plan dramatically.
Best,
Alexey