Re: A *short* planner question

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: A *short* planner question
Дата
Msg-id 11224.1018650586@sss.pgh.pa.us
обсуждение исходный текст
Ответ на A *short* planner question  ("Nick Fankhauser" <nickf@ontko.com>)
Ответы Re: A *short* planner question  ("Nick Fankhauser" <nickf@ontko.com>)
Список pgsql-admin
"Nick Fankhauser" <nickf@ontko.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

В списке pgsql-admin по дате отправления:

Предыдущее
От: "Nick Fankhauser"
Дата:
Сообщение: A *short* planner question
Следующее
От: "Gianmarco Piola"
Дата:
Сообщение: upgrade