partition table query allocate much memory - Mailing list pgsql-general

hi guys,

I'm not sure it was a bug,but a query on partition table allocate nearly 100GB memory is not normal.pid :119775 as below

SQL:

SELECT DISTINCT etl_source,gid FROM zh_erp.buy2 WHERE gid NOT IN (SELECT gid FROM zh_erp.goodsh);

I had modified the query to:SELECT count(DISTINCT etl_source,gid) FROM zh_erp.buy2 WHERE gid NOT IN (SELECT gid FROM zh_erp.goodsh);

This problem appear again.

buy2:102GB,438382597 rows,118 partitions.

    etl_source VARCHAR,gid INTEGER.

goodsh:20GB,24692534 rows,118 partitions.

    gid INTEGER.

dbversion：PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.2.1 20170829 (Red Hat 7.2.1-1), 64-bit

mem and paralle setting:

shared_buffers = 8GB

work_mem = 2GB

max_worker_processes = 48               # (change requires restart)
max_parallel_workers_per_gather = 4     # taken from max_parallel_workers
max_parallel_workers = 8                # maximum number of max_worker_processes that

I reset the work_mem:when it was 1GB or 2GB,the problem appeared,while it set  to 64NB,128MB,and 512MB,it will run in parallel mode and the memory less than 10GB.

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?

When work_mem=1GB or more,the  query plan is  a HashAggregate.otherwise it was Unique and running on parallel mode.

Thanks!