Re: trouble with (lack of) indexing

On Thu, 9 May 2002, Tom Lane wrote:
> "Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> writes:
> > Or am I just barking up a dead horse here since I'm now thinking it
> > might be nice to include information about potential indexes and at
> > what row count from them it's decided a seqscan is better.
>
> There is no "row count at which it's decided a seqscan is better".
> The planner does cost estimates and picks the plan with the lowest
> estimated cost, so all the behavior is emergent from the cost
> estimation equations.

But those cost estimates can be extrapolated and intersect points determined
can't they?

> I have not been able to think of any output format that would show
> rejected plan estimates without being so verbose as to be unreadable
> --- except in the most trivial cases, there are a *lot* of rejected
> plans, and the planner has no idea which of them might be interesting
> to a human. (You can try defining OPTIMIZER_DEBUG if you want to see
> it in action, but I don't think the output is very useful in practice.)

That's why I started thinking I was barking.

> What's worse is that (a) the only reason why you'd care is if the
> estimates are wrong, but (b) there is no way to tell anything about the
> actual cost that might be associated with a rejected plan fragment.
> To compare estimates to reality you really have to arrange for the plan
> to be the selected one, so that you can run it.

Good point.

I was thinking that it might be nice for explain to show some justification for
the choice of plan. However, I'm quite prepared to agree that this isn't a good
idea to try and do. I haven't looked to see what explanation of the statistics
and how the planner uses them there is in the documentation. It might be a good
idea if there was some detail about it there, I mean detail such that someone
can go to the documentation, query the tables and say 'Ah, so that's why it
chose that plan.' (Well at least for reasonably simple cases). The trouble is
I'm no where near qualified enough to it and so can't add it if it's not there
already.

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants