From: | Alexey Bashtanov <bashtanov(at)imap(dot)cc> |
---|---|
To: | tao tony <tonytao0505(at)outlook(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: partition table query allocate much memory |
Date: | 2018-05-24 08:52:50 |
Message-ID: | fdb54e28-e2ce-e9d2-8a85-078b315ea6ae@imap.cc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Maxim Boguk | 2018-05-24 09:38:03 | Re: found xmin from before relfrozenxid on pg_catalog.pg_authid |
Previous Message | talk to ben | 2018-05-24 08:21:33 | Re: Streaming Replication between PostGreSQL 9.2.2 on Red Hat and PostGreSQL 9.2.24 on Debian |