Re: [RFC][PATCH] Order qual clauses by combined cost and selectivity

Staroverov Ilja <i(dot)staroverov(at)ftdata(dot)ru> writes:
> The attached patch changes the ranking heuristic to use
> cost / (1 - selectivity)
> where selectivity is the fraction of rows that pass the clause.

This (or some close relative) has been proposed before, but we
have been hesitant to do it because our cost metrics for qual
clauses are pretty nearly completely bogus: practically all
the built-in functions are assigned cost 1, even though in
reality they have a wide range of runtimes. Selectivity isn't
enormously reliable either. We could easily be taking a qual
order that the user has chosen carefully and stirring it around
more or less at random.

I'm suspicious of the particular form of this expression, too,
because selectivities close to 1 will produce very substantial
effects on the estimate even though there may not be that much
difference in practice, and the selectivity difference may be
mostly sampling error in the first place. I think you need
a formula that's not very sensitive to small differences, but
this will fail that test.

We had a similar discussion about two years ago concerning a
patch that (IIRC) tried to order sort columns according to
the estimated cost of the comparison functions. That got
reverted for a few reasons, but one of the big ones was that
the cost comparisons were largely garbage-in-garbage-out.

I think that a prerequisite for any work in this area is to
try to assign more realistic procost estimates to at least
a substantial fraction of the built-in pg_proc entries.
That's going to be tedious and probably contentious, but
it's hard to believe we can make much progress without
better cost data.

regards, tom lane