Re: match_unsorted_outer() vs. cost_nestloop()

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

> Robert Haas <robertmhaas(at)gmail(dot)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