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.comPostgreSQL Training, Services and Support
