Re: Planner debug views

Qingqing Zhou wrote:
> On Mon, Jul 27, 2015 at 8:20 PM, Alvaro Herrera
> <alvherre(at)2ndquadrant(dot)com> wrote:
> >
> > I think this is a pretty neat idea, but I'm not sure this user interface
> > is a good one. Why not have a new option for EXPLAIN, so you would call
> > "EXPLAIN (planner_stuff=on)" and it returns this as a resultset?
>
> Thank you for the feedback.
>
> Yeah, I agree piggy back on EXPLAIN sounds a better interface. A good
> thing about GUC is that it is global, so deep in planner we can see it.

Um, okay, I gather that GUC is convenient to use for this purpose. I
don't see it as a good choice; I think a bare separate global variable
at the C level is more appropriate.

> > This idea of creating random CSV files seems odd and inconvenient in the
> > long run. For instance it fails if you have two sessions doing it
> > simultaneously; you could tack the process ID at the end of the file
> > name to prevent that problem, but then the foreign table breaks each
> > time.
>
> The reason to use CSV file is a kinda of balance. We do have other
> options, like pass data to pgstat, or persist in some shared memory/heap,
> but they all have their own issues. Any suggestion here?

I would have a tuplestore, and the planner code would push tuples to it.
After the planning is done, EXPLAIN can read and return tuples from the
store to the user.

> The file name is not random, it is fixed so we can create foreign table
> once and use it afterwards - I actually want to push them into
> system_views.sql.

Got that. That seems fragile and not very convenient; I don't think
forcing retries until no concurrent writers were using the same file is
convenient at all. When you need this facility the most, which is
during slow planner runs, it is more likely that somebody else will
overwrite your file.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services