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.
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.
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 :)
Thank you for your consideration in this regard.
-roger-
