Обсуждение: partition table query allocate much memory

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!

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