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
enable_material_v01.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers