Re: feature request: explain "with details" option
| От | Jeff Janes |
|---|---|
| Тема | Re: feature request: explain "with details" option |
| Дата | |
| Msg-id | CAMkU=1yL4M3cZuaN0mzwi80_=U=Cv1hkG8F77D4iP_GUpz9enA@mail.gmail.com обсуждение исходный текст |
| Ответ на | feature request: explain "with details" option (Roger Pack <rogerdpack2@gmail.com>) |
| Список | pgsql-hackers |
On Thu, Sep 8, 2016 at 10:40 AM, 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.
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
В списке pgsql-hackers по дате отправления:
Следующее
От: Tom LaneДата:
Сообщение: Re: Re: [COMMITTERS] pgsql: Use LEFT JOINs in some system views in case referenced row doesn