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
[cid:part1.2A42CFAD.715BF014@outlook.com]
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!
