Effectiveness of enable_material = off - Mailing list pgsql-hackers

From Jeff Janes
Subject Effectiveness of enable_material = off
Date
Msg-id CAMkU=1zYsYH7EpHF_RKBiN5fjoudFqGiYKVMnhb2LhU3m8UHLA@mail.gmail.com
Whole thread Raw
List pgsql-hackers
I recently could not shift a plan off of using a materialize, to see
what other options were out there, by setting enable_material to off.

>From src/backend/optimizer/path/costsize.c:


     * We don't test the value of enable_material here, because
     * materialization is required for correctness in this case, and turning
     * it off does not entitle us to deliver an invalid plan.

True, but I don't think that the materialization being necessary for
correctness entitles us to ignore enable_material=off.  If we need to
return a plan with materialization in order for this plan to be
correct, it should be punished like all other violations of enable_*
are punished.

The attached patch does that.

Having done this, I wonder if the line here should also be changed to
remove the enable_material test:

    if (enable_material && mat_inner_cost < bare_inner_cost)

If the disable_cost is not enough to make the "mat_inner_cost <
bare_inner_cost" to be false, then I don't see that we need a special
case to prevent use of the materialize anyway, as most other enable_*
implementations do not get one--they rely solely on disable_cost.
However, out of conservatism, I have not made the change of removing
this.

The example I used for demonstrating this is from
http://www.depesz.com/2013/05/09/explaining-the-unexplainable-part-3/


explain analyze select * from
    ( select oid, * from pg_class order by oid) as c
    join
    ( select * from pg_attribute a order by attrelid) as a
    on c.oid = a.attrelid;

Why this can serve as an example is a bit mysterious to me anyway.
The ORDER BY is implemented via:
Index Scan using pg_attribute_relid_attnum_index on pg_attribute a

The index scan should support mark-restore, so why does
ExecSupportsMarkRestore return false in the first place?  (This is a
side question, not really important to the disposition of the proposed
patch).

Cheers,

Jeff

Attachment

pgsql-hackers by date:

Previous
From: Kohei KaiGai
Date:
Subject: Re: [v9.4] row level security
Next
From: Greg Stark
Date:
Subject: Re: [v9.4] row level security