Alexander Pyhalov <alp@sfedu.ru> writes:
> However, one table has 2250 partitions and I've seen several selects to this table (based on the primary key of
individualpartitions) with a huge IN () list (about 500 keys). Don't expect this to be efficient, but unsure that
thesequeries caused such memory consumption.
Yeah, it could be that it's just the number of partitions involved in
the query that's causing the memory bloat. v10 is not bright about
pruning uninteresting partitions from a query (v12 is better), so
there will be 2250 tables involved so far as the planner and executor
are concerned. And then if you've got a large/complex WHERE clause,
that's probably copied and modified for each of those tables :-(.
We don't generally recommend having more than a hundred or so partitions,
because of issues like this. v12 is starting to make it feasible to have
more, but only in limited use-cases.
regards, tom lane