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
