PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15923
PG Version11.2
OSWindows, Linux, doesn't matters
Opened2019-07-24 21:39:35+00
Reported byDaniel Migowski
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15923
Logged by:          Daniel Migowski
Email address:      (redacted)
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

Messages

DateAuthorSubject
2019-07-24 21:39:35+00PG Bug reporting formBUG #15923: Prepared statements take way too much memory.
2019-07-24 22:23:45+00Tom LaneRe: BUG #15923: Prepared statements take way too much memory.
2019-07-24 22:32:15+00Daniel MigowskiAW: BUG #15923: Prepared statements take way too much memory.
2019-07-25 05:56:10+00Daniel MigowskiAW: BUG #15923: Prepared statements take way too much memory.
2019-07-25 22:13:33+00Tom LaneRe: AW: BUG #15923: Prepared statements take way too much memory.
2019-07-25 22:55:35+00Thomas MunroRe: AW: BUG #15923: Prepared statements take way too much memory.
2019-07-26 00:41:24+00Andres FreundRe: BUG #15923: Prepared statements take way too much memory.
2019-07-26 01:04:10+00Andres FreundRe: BUG #15923: Prepared statements take way too much memory.
2019-07-26 02:16:36+00Andres FreundRe: BUG #15923: Prepared statements take way too much memory.
2019-07-28 11:14:45+00Daniel MigowskiRE: BUG #15923: Prepared statements take way too much memory.
2019-07-29 06:11:04+00Thomas MunroRe: BUG #15923: Prepared statements take way too much memory.
2019-07-29 09:55:16+00Daniel MigowskiAW: BUG #15923: Prepared statements take way too much memory.
2019-07-30 03:59:11+00Kyotaro HoriguchiRe: BUG #15923: Prepared statements take way too much memory.