Re: Report planning memory in EXPLAIN ANALYZE
От | Ashutosh Bapat |
---|---|
Тема | Re: Report planning memory in EXPLAIN ANALYZE |
Дата | |
Msg-id | CAExHW5vcF_npJ8SDUjR_ZnvuEck292in9jknNoZqbhcC1ia_MQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Report planning memory in EXPLAIN ANALYZE (Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>) |
Ответы |
Re: Report planning memory in EXPLAIN ANALYZE
|
Список | pgsql-hackers |
Forgot to attach patch. Here it is On Mon, Dec 18, 2023 at 12:55 PM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote: > > On Sun, Dec 17, 2023 at 10:31 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote: > > > > OK, I propose the following further minor tweaks. (I modified the docs > > following the wording we have for COSTS and BUFFERS). > > LGTM. Included in the attached patch. > > > > > There are two things that still trouble me a bit. First, we assume that > > the planner is using an AllocSet context, which I guess is true, but if > > somebody runs the planner in a context of a different memcxt type, it's > > going to be a problem. So far we don't have infrastructure for creating > > a context of the same type as another context. Maybe it's too fine a > > point to worry about, for sure. > > I had considered this point. Different contexts take different > arguments for creation, so some jugglery is required to create a > context based on type. It looked more than necessary for the limited > scope of this patch. That's why I settled on the assertion. If we see > the need in future we can always add that support. > > > > > The other question is about trying to support the EXPLAIN EXECUTE case. > > Do you find that case really useful? In a majority of cases planning is > > not going to happen because it was already done by PREPARE (where we > > _don't_ report memory, because we don't have EXPLAIN there), so it seems > > a bit weird. I suppose you could make it useful if you instructed the > > user to set plan_cache_mode to custom, assuming that does actually work > > (I didn't try). > > If we set plan_cache_mode to force_custom_plan, we always plan the > statement and thus report memory. > > You are right that we don't always plan the statement when EXECUTE Is > issued. But it seems we create plan underneath EXECUTE more often that > I expected. And the report looks mildly useful and interesting. > > postgres@21258=#prepare stmt as select * from pg_class where oid = $1; > PREPARE > postgres@21258=#explain (memory) execute stmt(1); -- first time > QUERY PLAN > ------------------------------------------------------------------------------------- > Index Scan using pg_class_oid_index on pg_class (cost=0.27..8.29 > rows=1 width=273) > Index Cond: (oid = '1'::oid) > Planner Memory: used=40448 bytes allocated=81920 bytes > (3 rows) > > > postgres@21258=#explain (memory) execute stmt(1); > QUERY PLAN > ------------------------------------------------------------------------------------- > Index Scan using pg_class_oid_index on pg_class (cost=0.27..8.29 > rows=1 width=273) > Index Cond: (oid = '1'::oid) > Planner Memory: used=40368 bytes allocated=81920 bytes > (3 rows) > > observe that the memory used is slightly different from the first > time. So when the plan is created again something happens that eats > few bytes less. I didn't investigate what. > > The same output repeats if the statement is executed 3 more times. > That's as many times a custom plan is created for a statement by > default. > > postgres@21258=#explain (memory) execute stmt(1); > QUERY PLAN > ------------------------------------------------------------------------------------- > Index Scan using pg_class_oid_index on pg_class (cost=0.27..8.29 > rows=1 width=273) > Index Cond: (oid = $1) > Planner Memory: used=40272 bytes allocated=81920 bytes > (3 rows) > > Observe that the memory used is less here again. So when creating the > generic plan something happened which causes the change in memory > consumption. Didn't investigate. > > > postgres@21258=#explain (memory) execute stmt(1); > QUERY PLAN > ------------------------------------------------------------------------------------- > Index Scan using pg_class_oid_index on pg_class (cost=0.27..8.29 > rows=1 width=273) > Index Cond: (oid = $1) > Planner Memory: used=3520 bytes allocated=24576 bytes > (3 rows) > > And now the planner is settled on very low value but still non-zero or > 240 bytes. I think the parameter evaluation takes that much memory. > Haven't verified. > > If we use an non-parameterized statement > postgres@21258=#prepare stmt as select * from pg_class where oid = 2345; > PREPARE > postgres@21258=#explain (memory) execute stmt; > QUERY PLAN > ------------------------------------------------------------------------------------- > Index Scan using pg_class_oid_index on pg_class (cost=0.27..8.29 > rows=1 width=273) > Index Cond: (oid = '2345'::oid) > Planner Memory: used=37200 bytes allocated=65536 bytes > (3 rows) > > first time memory is consumed by the planner. > > postgres@21258=#explain (memory) execute stmt; > QUERY PLAN > ------------------------------------------------------------------------------------- > Index Scan using pg_class_oid_index on pg_class (cost=0.27..8.29 > rows=1 width=273) > Index Cond: (oid = '2345'::oid) > Planner Memory: used=240 bytes allocated=8192 bytes > (3 rows) > > Next time onwards it has settled on the custom plan. > > I think there's something to learn and investigate from memory numbers > here. So not completely meaningless and useless. > > I added that support on lines of "planning time". > > -- > Best Wishes, > Ashutosh Bapat -- Best Wishes, Ashutosh Bapat
Вложения
В списке pgsql-hackers по дате отправления: