now we have some long running queries via FDW that take minutes and get killed explain runs as idle in transaction on remote servers.
Are you saying the EXPLAIN itself gets killed, or execution of the plan generated based on the EXPLAIN (issued under use_remote_estimate = true) gets killed? Who is doing the killing, the local side or the foreign side? Can you include verbatim log entries for this?
now the explain for
select * from sh01.view1 keeps running for minutes sometimes,
then fetch too keeps running for minutes, although the total rows are < 10000 maybe.
idle in transaction | FETCH 10000 FROM c1
What is this? Is it from some monitoring tool, or pg_stat_activity, or what? And is it on the local side or the foreign side?
other questions:
also, what is the cost of fetch_size?
It will always fetch rows from the foreign server in this sized chunks. A larger fetch_size will have less network latency and computational overhead if many rows are going to be consumed, but also consume more memory on the local server as all rows are stored in memory per each chunk. Also, in the case of a LIMIT, it reads a large number of rows even if most of them may be unneeded. Conceptually, the LIMIT could be used to modify the FETCH downward to match the LIMIT, but that is not implemented. In the case of a view over UNION ALL, I don't think the individual subqueries even know what the global LIMIT is.
I mean given we have a query
select * from foobar limit 10000; via FDW
limit 10000 does not get pushed.
so it seems all rows some to FDW node and then limit is applied?
It should not read all rows. It should read as many multiples of fetch_size as needed, which should just be 1 multiple in this case.
