Re: [GENERAL] Performance of full outer join in 8.3

On Sat, 2009-04-18 at 08:32 -0400, Tom Lane wrote:

> The issues that I think would be worth having tests for are
> questions like "will the planner push comparisons to constants down
> through a full join?" (which was the bug that started this thread).

Yes, that sounds good.

> With a test methodology like the above, it wouldn't be enough to
> write a test case that exercised the behavior; you'd have to make
> sure that any alternative plan was an order of magnitude worse.
>
> I'm inclined to think that some sort of fuzzy examination of EXPLAIN
> output (in this example, "are there constant-comparison conditions in
> the relation scans?") might do the job, but I'm not sure how we'd
> go about that.

We can compose unit tests that have plans where the presence/absence of
the optimizer action is critical to a good plan. i.e. if the
constant-comparison is *not* pushed down it will be unable to use an
index created for it and so run cost will be much greater. We can then
define success in terms of a reduction in plan cost below a threshold.

So for each test we specify
* SQL
* a success threshold for cost

e.g.

For a piece of SQL we have cost = 60002.2 without optimisation or 12.45
with optimisation, so we make the threshold 20.0. Enough slack to allow
for changes in plan costs on platforms/over time, yet sufficient to
discriminate between working/non-working optimisation.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support