BUG #15923: Prepared statements take way too much memory.

The following bug has been logged on the website:

Bug reference: 15923
Logged by: Daniel Migowski
Email address: dmigowski(at)ikoffice(dot)de
PostgreSQL version: 11.2
Operating system: Windows, Linux, doesn't matters
Description:

Hello,

Prepared Statements take too much memory. This is a bug report I already
filled a few years as #14726
(https://www.postgresql.org/message-id/20170702090956.1469.41812%40wrigleys.postgresql.org)
ago but now I have a proof and can provide a testcase for you to simply
verify my assumptions.

I have a large query like https://explain.depesz.com/s/gN2, which results in
30MB query plans.

To verify if that is true I wrote a small script that prepares this query (a
simple SELECT * FROM myNotSoSimpleFatView) 250 times:

DO $$
DECLARE i int4;
BEGIN
FOR i IN 1..250 LOOP
RAISE NOTICE 'This is prepared statement %', i;
EXECUTE 'PREPARE testxy_'||i||' AS SELECT *, ''test_'||i||''' FROM
vw_report_salesinvoice WHERE salesinvoice_id = $1;';
END LOOP;
END $$;

To reproduce just insert your favority view and primary key name after the
FROM and have a look at memory consumption for yourself.

31540 postgres 20 0 1414452 976,9m 26816 R 99,6 12,2 0:11.11 postgres
after a few queries
31540 postgres 20 0 2480276 1,903g 26816 R 99,9 24,3 0:23.10 postgres
after 66 queries
31540 postgres 20 0 3824908 3,097g 26816 R 99,9 39,6 0:38.07 postgres
after 100 queries
31540 postgres 20 0 5727036 4,786g 26816 R 99,9 61,2 0:59.04 postgres
after 160 queries
...
31540 postgres 20 0 8646140 7,351g 19712 S 0,0 94,0 1:31.81 postgres
after 250 queries <- WTF 7 point 5 whopping gigs of RAM just for a few
prepared statements? Thats about 45M for each query!!

PostgreSQL crashes regulary at my customer servers, because I use automatic
prepared statements for queries that are done often. At least I thought that
would be a good idea.

Please note that this bug also affects other (like
https://github.com/rails/rails/issues/14645, where they just stopped using
Prepared Statements alltogether as a solution to their crashes).Most users
that use an ORM enable prepared queries, not seeing that PostgreSQL just
isn't capable to handle them.

I have the problem on 9.5, and testing this on 11.2 still shows the same
behaviour. Please, please, someone have a look at where all that memory goes
and I will immediately roll out a new version to all of my customers. I love
your database and the stability, but this is definitely a point where you
can drastically improve!

Best and kindest regards,
Daniel Migowski