Hello,
While examining the reasons for excessive memory usage in prepared
statements I noticed that RTE_JOIN-kind RTEs contain a bunch of
columnNames and joinaliasvars, that are irrelevant after the Query after
has been rewritten. I have some queries that join about 20 tables and
select only a few values, mainly names of objects from those tables.
The attached patch adds a small cleanup function that iterates thought
the query and cleans stuff up. I may have missed some places that could
also be cleaned up but for now the memory requirements for my largest
statements have dropped from 31.2MB to 10.4MB with this patch.
After the statement has be executed seven times a generic plan is stored
in the statement, resulting in an extra 8,8MB memory usage, but still
this makes a difference of more than 50% total.
But the most interesting thing was that this patch reduced query
execution time by 50% (~110ms vs. 55ms) when no generic was created yet,
and by 35% (7.5ms vs. 5.1ms) when the global query plan had been created.
All tests still pass with my cleanup command, but I am afraid the tests
might not contain queries that still need that info after statement
preparation.
If anyone might have a look at it and hint me to a situation where this
might crash later on? Also, would it be possible for someone to run a
benchmark after applying this test to ensure my findings are not totally
off? I tested on a Intel(R) Xeon(R) CPU E5-2667 v4 @ 3.20GHz with SSDs,
but everything should have been in memory when I ran the test.
Regards,
Daniel Migowski