Re: A *short* planner question

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: nickf(at)ontko(dot)com
Cc: "pgsql-admin" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: A *short* planner question
Date: 2002-04-12 22:29:46
Message-ID: 11224.1018650586@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

"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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Gianmarco Piola 2002-04-12 23:29:22 upgrade
Previous Message Nick Fankhauser 2002-04-12 21:56:03 A *short* planner question

Browse pgsql-general by date

  From Date Subject
Next Message Nick Fankhauser 2002-04-12 23:31:48 Re: A *short* planner question
Previous Message Tom Lane 2002-04-12 22:13:42 Re: Nested Loop WAS: VACUUM ANALYZE makes things worse!