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.