Patch to clean Query after rewrite-and-analyze - reduces memusage upto 50% - increases TPS by up to 50%
От | Daniel Migowski |
---|---|
Тема | Patch to clean Query after rewrite-and-analyze - reduces memusage upto 50% - increases TPS by up to 50% |
Дата | |
Msg-id | 7cf530b8-05c8-7e89-bab7-5ac7392d9972@ikoffice.de обсуждение исходный текст |
Ответы |
Re: Patch to clean Query after rewrite-and-analyze - reduces memusage up to 50% - increases TPS by up to 50%
|
Список | 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
Вложения
В списке pgsql-hackers по дате отправления: