Re: PostgreSQL memory usage - Mailing list pgsql-general

From Tom Lane
Subject Re: PostgreSQL memory usage
Date
Msg-id 20765.1571316015@sss.pgh.pa.us
Whole thread Raw
In response to RE: PostgreSQL memory usage  (Alexander Pyhalov <alp@sfedu.ru>)
List pgsql-general
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



pgsql-general by date:

Previous
From: Alexander Pyhalov
Date:
Subject: RE: PostgreSQL memory usage
Next
From: Erwin Brandstetter
Date:
Subject: Re: Can functions containing a CTE be PARALLEL SAFE?