Patch to clean Query after rewrite-and-analyze - reduces memusage up to 50% - increases TPS by up to 50%

From: Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To: "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Patch to clean Query after rewrite-and-analyze - reduces memusage up to 50% - increases TPS by up to 50%
Date: 2019-08-03 15:39:33
Message-ID: 7cf530b8-05c8-7e89-bab7-5ac7392d9972@ikoffice.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
query_cleaning_in_prepare.diff text/plain 2.4 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Fetter 2019-08-03 15:56:04 Re: [Patch] Adding CORRESPONDING/CORRESPONDING BY to set operation
Previous Message Chapman Flack 2019-08-03 15:12:15 Re: Fix XML handling with DOCTYPE