Re: Slight change in query leads to unexpected change in query plan

Tom Lane wrote:
> Jack Orenstein <jack(dot)orenstein(at)hds(dot)com> writes:
>> Limit (cost=0.00..324.99 rows=100 width=451)
>> -> Index Scan using t_pkey on t (cost=0.00..296027.98 rows=91088 width=451)
>> Index Cond: (pk > 1000000000)
>
>> Adding the value restriction at the top of this query plan wouldn't increase the
>> cost very much.
>
> You're missing the point: with the value restriction in place, it's
> estimating that it will probably have to scan all 91000 rows (because
> there are fewer than 100 satisfying the value restriction). And that
> is going to cost somewhere north of 296027 cost units --- the cost
> shown, plus 91000 invocations of the value-restriction check.
> Which is considerably more than the cost of the other plan.

I see -- the optimizer is calculating that it will have to examine a very large
fraction of the rows.

Actually, pk and value are highly correlated. If a row gets past the index scan,
odds are very high that the value predicate will evaluate to true. So I am sure
that the index scan is the right way to go. I'm just trying to convince the
optimizer of this.

One thing I am considering is introducing a function with high execution cost.
E.g., if I do this:

create function return_input(bytea) returns bytea
as '
begin
return $1;
end;
' language 'plpgsql' cost 10000;

explain
select *
from t
where pk > 1000000
and return_input(value = 'asdf'::bytea)
order by pk
limit 100;

then I get the plan I want.

Limit (cost=0.00..563490.32 rows=100 width=451)
-> Index Scan using t_pkey on t (cost=0.00..34226402.07 rows=6074 width=451)
Index Cond: (pk > 1000000)
Filter: (return_input(value) = 'asdf'::bytea)

Is there a more elegant way of forcing the plan that I want?

Jack