Re: match_unsorted_outer() vs. cost_nestloop()

On Sep 6, 2009, at 10:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Robert Haas <robertmhaas@gmail.com> writes:
>> And, by the way, is the algorithm proposed in the comment sensible
>> anyway?  Under what circumstances would it make sense to  
>> materialize a
>> sequential scan?
>
> Expensive filter conditions, for example.

Ah, right.  Yeah that could be a big win.

> I've occasionally wondered if this code isn't outright wrong anyway:
> when you consider the costs of checking tuple visibility and the costs
> involved in access to a shared buffer, it's possible that copying  
> tuples
> to a local materialization store would be a win for rescans in any  
> case.
> (Of course it's a lot easier to credit that concept when the store
> doesn't spill to disk.)  Given the basic bogosity of the costing rules
> I wasn't eager to mess with it; in fact I think we deliberately  
> tweaked
> things in this area to prevent materialization, because otherwise the
> planner *always* wanted to materialize and that didn't seem to be a  
> win.
> But now that we have a plan for a less obviously broken costing
> approach, maybe we should open the floodgates and allow  
> materialization
> to be considered for any inner path that doesn't materialize itself
> already

Maybe.  I think some experimentation will be required.  We also have  
to be aware of effects on planning time; match_unsorted_outer() is,  
AIR, a significant part of the CPU cost of planning large join problems.

...Robert