Look at all paths?

From: Chris Cleveland <ccleve+github(at)dieselpoint(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Look at all paths?
Date: 2021-12-29 00:07:50
Message-ID: CABSN6VcXcUpsA_9YJuouqw8ya_Odkg3yWRyPHpq0yA4vgtypKg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2021-12-29 00:18:20 Re: Look at all paths?
Previous Message Jelte Fennema 2021-12-28 22:19:20 Re: [EXTERNAL] Re: Add ETIMEDOUT to ALL_CONNECTION_FAILURE_ERRNOS