Re: Make EXPLAIN generate a generic plan for a parameterized query

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Michel Pelletier <pelletier(dot)michel(at)gmail(dot)com>, Julien Rouhaud <rjuju123(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Make EXPLAIN generate a generic plan for a parameterized query
Date: 2023-02-05 17:24:03
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Fri, 2023-02-03 at 15:11 -0500, Tom Lane wrote:
> I can think of a couple of possible ways forward:
> * Fix things so that the generic parameters appear to have NULL
> values when inspected during executor startup.  I'm not sure
> how useful that'd be though.  In partition-pruning cases that'd
> lead to EXPLAIN (GENERIC_PLAN) showing the plan with all
> partitions pruned, other than the one for NULL values if there
> is one.  Doesn't sound too helpful.
> * Invent another executor flag that's a "stronger" version of
> EXEC_FLAG_EXPLAIN_ONLY, and pass that when any generic parameters
> exist, and check it in CreatePartitionPruneState to decide whether
> to do startup pruning.  This avoids changing behavior for existing
> cases, but I'm not sure how much it has to recommend it otherwise.
> Skipping the startup prune step seems like it'd produce misleading
> results in another way, ie showing too many partitions not too few.
> This whole business of partition pruning dependent on the
> generic parameters is making me uncomfortable.  It seems like
> we *can't* show a plan that is either representative of real
> execution or comparable to what you get from more-traditional
> EXPLAIN usage.  Maybe we need to step back and think more.

I slept over it, and the second idea now looks like the the right
approach to me. My idea of seeing a generic plan is that plan-time
partition pruning happens, but not execution-time pruning, so that
I get no "subplans removed".
Are there any weird side effects of skipping the startup prune step?

Anyway, attached is v7 that tries to do it that way. It feels fairly
good to me. I invented a new executor flag EXEC_FLAG_EXPLAIN_GENERIC.
To avoid having to change all the places that check EXEC_FLAG_EXPLAIN_ONLY
to also check for the new flag, I decided that the new flag can only be
used as "add-on" to EXEC_FLAG_EXPLAIN_ONLY.

Laurenz Albe

Attachment Content-Type Size
0001-Add-EXPLAIN-option-GENERIC_PLAN.v7.patch text/x-patch 14.4 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Albin Hermange 2023-02-05 18:16:04 add a "status" column to the pg_rules system view
Previous Message Aleksander Alekseev 2023-02-05 17:05:51 Re: [PATCH] Compression dictionaries for JSONB