Look at all paths?
I'm developing a new index access method. Sometimes the planner uses it and
sometimes it doesn't. I'm trying to debug the process to understand why the
index does or doesn't get picked up.
Is there a way to dump all of the query plans that the planner considered,
along with information on why they were rejected? EXPLAIN only gives info
on the plan that was actually selected.
I understand that this could generate way too much info for a query with
many joins, but that's not what I want it for. I just want to look at some
queries with zero or one joins to understand what is going on.
Three examples:
1. I spent two days debugging a problem where the index wasn't getting used
when it should have been. The problem turned out to be that the function
associated with the operator wasn't created as IMMUTABLE. Bizarrely, when I
made it IMMUTABLE, the index got used and the function didn't get called at
all!
2. I'm currently trying to debug a problem where neither the function nor
the index are getting called. EXPLAIN says "Result (cost=0.00 ...) One-Time
Filter: false". Which function does it consider to be a one-time filter and
why? I need a bit more info to track it down.
3. In one case, my access method costestimate() function was returning an
unexpected value. I couldn't see that because that plan didn't get selected.
I'm looking for a tool that gives a bit more insight.
Chris Cleveland <ccleve+github@dieselpoint.com> writes:
I'm developing a new index access method. Sometimes the planner uses it and
sometimes it doesn't. I'm trying to debug the process to understand why the
index does or doesn't get picked up.
Is there a way to dump all of the query plans that the planner considered,
along with information on why they were rejected? EXPLAIN only gives info
on the plan that was actually selected.
What you can do is "set enable_seqscan = off", then EXPLAIN.
If you get an indexscan where before you did not, then you have
a costing problem, ie use of index is estimated as more costly
than a seqscan. (This is not necessarily wrong, particularly
if you make the rookie mistake of testing with a tiny table.)
If you still get a seqscan, then the planner doesn't think the
query conditions match the index, and you have a different
problem to solve.
If you really want to see all the paths, you could do it with
gdb --- set a breakpoint at add_path and inspect the structs
that get passed to it. I doubt that will give you much
additional info for this problem. However, if (as seems
likely) it's a costing problem, then you may well end up
stepping through your amcostestimate function to see where
it's going off the rails; so learning to gdb the backend
will be well worth your time anyway.
regards, tom lane
On 12/29/21 5:07 AM, Chris Cleveland wrote:
I'm developing a new index access method. Sometimes the planner uses it
and sometimes it doesn't. I'm trying to debug the process to understand
why the index does or doesn't get picked up.Is there a way to dump all of the query plans that the planner
considered, along with information on why they were rejected? EXPLAIN
only gives info on the plan that was actually selected.
You can enable OPTIMIZER_DEBUG option. Also the gdbpg code [1]https://github.com/tvondra/gdbpg makes our
work much easier, sometimes.
[1]: https://github.com/tvondra/gdbpg
--
regards,
Andrey Lepikhov
Postgres Professional