Re: feature request: explain "with details" option

On Thu, Sep 8, 2016 at 10:40 AM, Roger Pack <rogerdpack2(at)gmail(dot)com> wrote:

> My apologies if this was already requested before...
>
> I think it would be fantastic if postgres had an "explain the explain"
> option:
> Today's explain tells us what loops and scans were used, and relative
> costs, etc. It doesn't seem to tell *why* the planner elected to use
> what it did.
>
> For instance, in the case of a corrupted index, it doesn't say why
> it's not using that index, it just doesn't use it, causing some
> confusion to end users. At least causing confusion to me.
>

I've never seen such a thing. If an index is corrupt, it still gets used
like normal. You just get wrong results, or crashes, depending on the
nature of the corruption.

Or in the case of where it iterates over an entire table (seq. scan)
> instead of using an index because the index range specified "is most
> of the table" (thus not helpful to use the index)

The planner just comes up with plans that use a seq scan, and plans that
use an index; and then compares the cost of them and finds that one cost is
lower than the other. It never explicitly develops a specific notion of "I
won't use the index because I'm retrieving too much of the table". So it
wouldn't be just a matter of reporting something that isn't currently
reported, it would first have to infer that thing in the first place, and
that would probably be very hard.

> ...The choice is
> appropriate. The reasoning why is not explicitly mentioned. Again
> causing possibility for some delay as you try to "decipher the mind"
> of the planner. Sometimes tables (ex: tables after having been first
> propagated) need an "analyze" run on them, but it's not clear from an
> "explain" output that the analyze statistics are faulty. Not even a
> hint.
>

You can get a hint, sometimes, by comparing the predicted rows and the
actual rows of an "explain (analyze)". Making this more user friendly to
do would probably be best done by making an expert-system tool to look at
the currently-reported plans (https://explain.depesz.com kind of does this
already, particularly the row_x column) rather than trying to build
something into core. It could be improved by detecting when the node being
misestimated is simple scan of a single table or index with a single
filter/condition, rather than a join or a complex filter/condition.

>
> So this is a feature request for an "EXPLAIN DETAILS" option or
> something, basically like today's explain but with more "rationale"
> included. This could be immensely useful to many Postgres users.
>

Unfortunately it would also be immensely hard to implement.

What I would find useful is simply to have it report details about how the
cost of each node of the plan was arrived at, i.e. how many of multiples of
each of the *_cost factors were summed to arrive at the total cost. that
would help me a lot in interpreting plans, and might also help someone like
Depesz a lot in improving his expert-system.

It would be far easier than what you are proposing, but would be still be a
lot of work. It would probably also be challenged because the accounting
overhead, while small, would be incurred on every single planner run.

Cheers,

Jeff