Re: AW: BUG #15923: Prepared statements take way too much memory. - Mailing list pgsql-bugs

Daniel Migowski <dmigowski@ikoffice.de> writes:
> I am considering trying PostgreSQL hacking to better understand the reasons of the memory consumption of Query Plans
inorder to be able to maybe reduce the memory used by them. 

FWIW, I've thought for some time that we should invent a memory context
allocator that's meant for data that doesn't get realloc'd (much) after
first allocation, with lower overhead than aset.c has.  Such an allocator
would be ideal for plancache.c, and perhaps other use-cases such as
plpgsql function parsetrees.  IMV this would have these properties:

* Doesn't support retail pfree; to recover space you must destroy the
whole context.  We could just make pfree a no-op.  With the details
sketched below, repalloc would have to throw an error (because it would
not know the size of the old chunk), but I think that's OK for the
intended purpose.

* Minimum chunk header overhead, ie only the context pointer required by
the mmgr.c infrastructure.  In particular, don't store the chunk size.

* Request sizes are only rounded up to a MAXALIGN boundary, not to a
power of 2.

* All else could be pretty much the same as aset.c, except we'd not need
to have any freelist management.  I would imagine that callers might want
to use a smaller initial and maximum block size than is typical with
aset.c, to reduce end-of-context wastage, but that would be up to the
callers.  (Possibly, instead of freelists, we'd trouble to track
end-of-block space in more than one active block, so that we avoid
wasting such space when a large allocation doesn't quite fit.)

This wouldn't take very much new code, and it would potentially offer
up to perhaps 2X space savings compared to aset.c, thanks to cutting the
per-chunk overhead.  Whether that's enough to float your boat with respect
to cached plans isn't too clear, though.


A totally different idea is to make a variant version of copyObject
that is intended to produce a compact form of a node tree, and does
not create a separate palloc allocation for each node but just packs
them as tightly as it can in larger palloc chunks.  This could outperform
the no-pfree-context idea because it wouldn't need even context-pointer
overhead for each node.  (This relies on node trees being read-only to
whatever code is looking at them, which should be OK for finished plan
trees that are copied into the plancache; otherwise somebody might think
they could apply repalloc, GetMemoryChunkContext, etc to the nodes,
which'd crash.)  The stumbling block here is that nobody is gonna
tolerate maintaining two versions of copyfuncs.c, so you'd have to
find a way for a single set of copy functions to support this output
format as well as the traditional one.  (Alternatively, maybe we
could learn to autogenerate the copy functions from annotated struct
definitions; people have muttered about that for years but not done
anything.)


Or you could imagine flattening the node tree into serialized text
(cf outfuncs.c/readfuncs.c) and compressing that, although I fear that
the overhead of doing so and recovering a node tree later would be
excessive.  Also, the serialized-text representation tends to be pretty
bulky, and I don't know if compression would win back enough to make it
all that much smaller than the existing in-memory representation.


> References to data like table and datatype definitions that are copied
> into the plan but are also copied multiple times. Doesn't matter for
> small plans, but maybe for large ones where different subselects query
> the same tables. Detecting such duplicated references would involve
> sweeping over a plan after it has been created, placing all stuff into
> some kind of map and unifying the references to those definitions. The
> time spend for this might even speed up executing the query because less
> memory has to be touched when the plan is executed potentially leading
> to better cpu cache hit rates. This seems the most promising to me.

TBH that sounds like a dead end to me.  Detecting duplicate subtrees would
be astonishingly expensive, and I doubt that there will be enough of them
to really repay the effort in typical queries.

            regards, tom lane