From: | David Rowley <dgrowleyml(at)gmail(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Heikki Linnakangas <hlinnaka(at)iki(dot)fi>, Peter Geoghegan <pg(at)bowt(dot)ie>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: On disable_cost |
Date: | 2024-10-03 00:11:16 |
Message-ID: | CAApHDvpMyKJpLGWRmR3+3g4DxrSf6iRpwTRCXMorU0HvgWbocw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, 3 Oct 2024 at 03:04, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> I don't think this will produce the right answer in all cases because
> disabled node counts don't propagate across subquery levels.
I see my patch didn't behave correctly when faced with a SubqueryScan
as SubqueryScan does not use the "lefttree" field and has a "subplan"
field instead. The patch will need special handling for that (fixed in
the attached patch).
I can't quite find the area you're talking about where the
disabled_nodes don't propagate through subquery levels. Looking at
cost_subqueryscan(), I see propagation of disabled_nodes. If the
SubqueryScan node isn't present then the propagation just occurs
normally as it does with other path types. e.g. master does:
# set enable_Seqscan=0;
# explain (costs off) select * from (select * from pg_class offset 0)
order by oid;
QUERY PLAN
----------------------------
Sort
Disabled Nodes: 1
Sort Key: pg_class.oid
-> Seq Scan on pg_class
Disabled Nodes: 1
(5 rows)
Can you provide an example of what you mean?
I've attached an updated PoC patch which I think gets the SubqueryScan
stuff correct. I've not spent time testing everything as if nobody
likes the patch's EXPLAIN output, I don't want to waste time on the
patch for that.
I understand you're keen on keeping the output as it is in master. It
would be good to hear if other people agree with you on this. I
imagine you'd rather work on other things, but it's easier to discuss
this now than after PG18 is out.
For me, I find master's output overly verbose and not all that easy to
identify the disabled nodes as it requires scanning all the
disabled_node values and finding the nodes where the value is one
higher than the sum of the sum node's disabled_nodes. For example, if
a Nested Loop has "Disabled Nodes: 17" and the inner side of the join
has "Disabled Nodes: 9" and the outer side has "Disabled Nodes: 8",
it's not that easy to determine if the nested loop is disabled or not.
Of course, you only need to do 8+9=17 and see it isn't, but when faced
with run-time pruning done at executor startup, some
Append/MergeAppend nodes might be missing from EXPLAIN and when that
happens, you can't just manually add the Disabled Nodes up. Here's
what I mean:
setup:
create table lp (a int) partition by list(a);
create table lp1 partition of lp for values in(1);
create table lp2 partition of lp for values in(2);
set enable_seqscan=0;
prepare q1(int) as select * from lp where a = $1 order by a;
set plan_cache_mode=force_generic_plan;
explain (analyze, costs off, timing off, summary off) execute q1(1);
master:
Append (actual rows=0 loops=1)
Disabled Nodes: 2
Subplans Removed: 1
-> Seq Scan on lp1 lp_1 (actual rows=0 loops=1)
Disabled Nodes: 1
Filter: (a = $1)
patched:
Append (actual rows=0 loops=1)
Subplans Removed: 1
-> Seq Scan on lp1 lp_1 (actual rows=0 loops=1)
Disabled: true
Filter: (a = $1)
With master, it looks like Seq Scan and Append are disabled. With the
patched version, you can see it isn't.
David
Attachment | Content-Type | Size |
---|---|---|
poc_improve_disabled_nodes_explain_output_v2.patch | application/octet-stream | 35.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2024-10-03 00:30:38 | Re: pg_verifybackup: TAR format backup verification |
Previous Message | David G. Johnston | 2024-10-03 00:09:42 | Re: Enhance create subscription reference manual |