Re: feature request: explain "with details" option - Mailing list pgsql-hackers

On 9 September 2016 at 01:40, Roger Pack <rogerdpack2@gmail.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.

One thing that's been discussed here is to have a way to see which
potential plans are rejected and compare their costs.

This isn't simple because there are often *lots* of variants. You
don't just want to see the "top 10" candidate plans, because they're
probably a bunch of small variants on the same plan; the ones you'll
be interested in will probably be very different plans with very bad
relative estimates.

Say you've got a query over 20 tables through five views. You're only
interested in a particular part that runs much slower than the
estimates say it should. You don't care about any of the other parts
of the plan. How can Pg help with this?

My thinking is that maybe the planner can expose an interface tools
can use to offer plan alternative drill-down. You'd start with the
original plan and the tool would ask "show me alternatives for this
sub-path". You'd explore the plan to see what alternatives were
considered. A way to see how cost estimates are calculated and based
on what stats would be needed.

That's hardly a simple   explain (altplans)  though.

If you have good ideas about how to choose a small subset of alternate
plans to show the user that'd be informative and not risk being even
more misleading, that'd be good. But in a cost-based planner that
explores many paths this might not be as simple as you expect.

> 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.

It doesn't have a "corrupted index" flag. What do you mean by this?

> 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 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.

That one's not simple. If Pg knew the stats were wrong it'd say so,
but it has no idea. It'd have to consult its stats to figure out ...
oh, damn.

We could probably do a better job of identifying tables that have been
flagged as needing analyze but autovacuum hasn't got around to it yet,
though.

> 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.
>
> I'd even be willing to chip in a couple hundred bucks if it would help
> grease the wheels for somebody taking up the challenge if that helps
> at all :)

I think you missed a zero or two, unfortunately. I don't think this is
a small project to do well and right. Doing it badly might just add
more confusing/misleading information. Then again, I'm not exactly a
planner expert.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services