Re: On disable_cost

On Thu, 3 Oct 2024 at 03:04, Robert Haas <robertmhaas@gmail.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