Thread: 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] makes our work much easier, sometimes. [1] https://github.com/tvondra/gdbpg -- regards, Andrey Lepikhov Postgres Professional