On Mon, Nov 25, 2024 at 10:23 PM Anton Shmigirilov < a.shmigiri...@postgrespro.ru> wrote:
> > > Hi Hackers, > > > > I am working on a feature in postgres_fdw extension to show plans used > by remote postgresql servers in the output of the EXPLAIN command. > > I think this will help end users understand query execution plans used > by remote servers. Sample output for table people where people_1 is local > partition and people_2 is remote partition would look like - > > > > postgres:5432> explain select * from "test"."people"; > > QUERY PLAN > > Append (cost=0.00..399.75 rows=2270 width=46) > > → Seq Scan on "people.people_1" people_1 (cost=0.00..21.00 rows=1100 > width=46) > > → Foreign Scan on "people.people_2" people_2 (cost=100.00..367.40 > rows=1170 width=46) > > Remote Plan > > Seq Scan on "people.people_2" (cost=0.00..21.00 rows=1100 > width=46) > > (5 rows) > > > > I would like community inputs on below high level thoughts: > > > > 1. To enable this feature, either we can introduce a new option in > EXPLAIN command e.g. (fetch_remote_plans true) or control this behaviour > using a guc defined in postgres_fdw extension. I am more inclined > towards guc as this feature is for extension postgres_fdw. Adding the > EXPLAIN command option might force other FDW extensions to handle this. > > > > 2. For ANALYZE = false, the idea is that postgres_fdw would create a > connection to a remote server, prepare SQL to send over connection and > store received plans in ExplainState. > > > > 3. For ANALYZE = true, idea is that postgres_fdw would set a new guc > over connection to remote server, remote server postgres_fdw would read > this guc and send back used query plan as a NOTICE (similar to auto_explain > extension does) with custom header which postgres_fdw extension > understands. . We also have an opportunity to introduce a new message type > in the protocol to send back explain plans but it might look like too much > work for this feature. Open to ideas here. > > > > Dinesh Salve > > SDE@AWS > > Hi Dinesh, > > Thank you for your proposal regarding explain for foreign servers. > > I have been working on a similar feature and there are several > considerations to take into account. > > To enable this feature it is preferable to use GUC rather than the EXPLAIN > option, as it simplifies regression testing. You can simply set it to off > before most tests that involve plan checking, while leaving the rest > unchanged. This leads to a reduction in the size of the differences. > > If it is necessary to provide only the execution plan of the foreign query > (without actual timing metrics), you should send EXPLAIN with ANALYZE set > to OFF, regardless of the initial ANALYZE state. This approach will prevent > the re-execution of the remote query (the SQL part of the ForeignScan > node), which could potentially lead to side effects. It's safe to send > ANALYZE ON during remote EXPLAIN only if your remote SQL is idempotent, > i.e. doesn't change anything. That way you can't sent it for *Modify nodes, > but it can be applicable for certain ForeignScans, such as those involving > FunctionScan. In general, it is safer to enforce ANALYZE OFF in all cases. > > Also you can't expose to main EXPLAIN some metrics obtained from remote > side through the "remote" explain. For example, values such as actual time, > planning time, execution time, and similar metrics cannot be exposed > because they relates to events that occurred during the "EXPLAIN" > communication, rather than during the actual planning and execution phases. > Therefore, these times would likely mislead the user. I suppose it's better > to enforce EXPLAIN with TIMING OFF and SUMMARY OFF when obtaining the > remote portion of EXPLAIN. > > While reconstructing (deparsing) the SQL query to send as part of EXPLAIN > to the remote server, you can obtain SQL statements with placeholders (i.e. > $1, $2, etc) instead of actual parameter values. It's syntactically > incorrect SQL, which will lead to an error on the remote side. There are > two ways to avoid this. You can use GENERIC_PLAN feature (v16+), which > accepts dollar-parameters here. Another option is to use params_list == > NULL in the deparseSelectStmtForRel() function to substitute dummy null > values for placeholders, thereby generating syntactically correct SQL. The > downside of this approach is the need to perform an additional deparse > stage, which can be redundant. > > However looking forward a patch, it is likely that some (or all) of my > thoughts may become irrelevant. > > -- > Best regards, > Anton Shmigirilov, > Postgres Professional Hello Anton, Yeah, using guc to enable this feature. I am using auto_explain style design to get a query plan after foreign server executes it. I am forwarding user EXPLAIN options to foreign as it is so as to ensure the user gets expected output. I have prepared a patch which works for SELECT commands and I am planning to work on other commands based on feedback so that I invest in right direction. Appreciate if you could take a look and share feedback. Attached the steps I used to test this as well. Looping in Andy as he expressed interest in review :) Dinesh Salve SDE@AWS
CREATE EXTENSION postgres_fdw;
DO $d$
BEGIN
EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname '$$||current_database()||$$',
port '$$||current_setting('port')||$$'
)$$;
END;
$d$;
CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw OPTIONS (port '5432',
dbname 'postgres');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
CREATE TABLE format_fdw_parent (c1 int) PARTITION BY RANGE (c1);
CREATE TABLE local_fdw_0_10 PARTITION OF format_fdw_parent FOR VALUES FROM (0)
TO (10);
CREATE TABLE foreign_fdw_10_20_remote (c1 int);
create foreign table foreign_fdw_10_20 partition of format_fdw_parent
FOR VALUES FROM (10) TO (20)
server loopback options (table_name 'foreign_fdw_10_20_remote');
explain select * from format_fdw_parent;
explain (analyze true, format json) select * from format_fdw_parent;
0001-enable-fetching-explain-plans-from-foreign-server.patch
Description: Binary data
