Re: A *short* planner question

"Nick Fankhauser" <nickf(at)ontko(dot)com> writes:
> So... why wouldn't the planner do this:

Offhand I think the planner should have considered that plan; evidently
it thought it was more expensive than this plan. (Perhaps it was right;
how selective is the actor_case_assignment(actor_id) index?) You could
check by temporarily dropping the actor_case_assignment_both index and
seeing what plan you get. You don't even have to really drop it; try

begin;
drop index ...;
explain ...;
rollback;

Should work, without the pain of rebuilding the index afterwards...

> Another thing that doesn't make sense to me, but doesn't hurt the plan, is
> that although we have analyze stats on actor, the planner seems to be using
> the default of .01 on upper(actor_full_name) and predicts that 2799 rows
> will be returned, however, if I get rid of the upper(), it uses the stats
> and predicts that 1 row will be returned. Does the use of an index on a
> function make the planner stop using the stats?

There are no stats computed for the values of functional indexes, at
present, thus no way for the planner to derive any realistic estimate for
the selectivity of that clause. I have a private TODO item about that,
but I'm not sure if the public TODO list mentions it.

regards, tom lane