Analyze all plans

From: Donald Dong <xdong(at)csumb(dot)edu>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Analyze all plans
Date: 2019-01-23 08:44:28
Message-ID: 7F3EC06F-78C0-48A7-8797-E0257C2971BD@csumb.edu
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Hi,

I'm working on an extension which analyzes all possible plans
generated by the planner. I believe this extension would become
useful for benchmarking the planner (e.g. the performance of the
estimation and the cost model) and better understanding the cases
where the planners would make a suboptimal move.

Here are my procedures:
1. Enumerate all the plans
1.1 Create a hook for add_path so that it won't discard the
expensive paths from the planner's point of view.
1.2 Collect all the paths from final_rel->pathlist, and turn them
into PlannedStmt nodes by patching standard_planner.
1.3 Mark the cheapest path from the planner's point of view.

2. Explain all the plans
2.1 First explain the cheapest plan
2.1.1 If analyzing, collect the execution time and use it to set
a timer interrupt.
2.2 Explain the remaining plans
2.2.1 The other plans can be disastrous; the plans may never
finish in a reasonable amount of time. If analyzing, the timer
interrupt shall stop the executor.
2.2.2 Move on to the next plan

Are those procedures reasonable?

I'm able to implement all the steps except for 2.2.1.

- Attempt 1
Our most common way of handling the timeouts is to kill the
process. However, it would terminate the entire explain statement,
yet there're still plans to be explained.

- Attempt 2
Fork before explain so it would possible to kill the child process
without disrupting the explain statement. However, simply
allocating shared memory for the QueryDesc would not work (PANIC:
failed to re-find shared lock object). To me, this plan is more
doable, but it seems there exist other mechanisms I need to be
aware, to execute a plan in the child process and report the
result in the parent process?

What do you think? I will appreciate any feedback.

Thank you,
Donald Dong

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fabien COELHO 2019-01-23 08:48:12 yet another comment typo patch
Previous Message Kyotaro HORIGUCHI 2019-01-23 08:35:02 Re: Protect syscache from bloating with negative cache entries