On Sun, Feb 17, 2019 at 1:52 PM Vijaykumar Jain <vjain@xxxxxxxxxxxxx> wrote:
Assuming your questions as 1,2,3, please find my answers below.1)"explain" on foreign servers run as "idle in transactions". coz they were running very long (in the order of some minutes) , pgbouncer (in tx level pooling) setting kill them (as idle in tx time limit exceeded of 5 mins) or else results in too many connections piling up.2)yes, i get those from pg_stat_activity, it truncates the full statement, but it shows up as * EXPLAIN select col1, col2 .... * 00:00:44 | idle in transaction (this is just one of the screenshots i have). (on the foreign side)
You are misinterpreting that data. The EXPLAIN is not currently running. It is the last statement that was running prior to the connection going idle-in-transaction. See my just previous email--I think the reason it is idle is that the local is servicing some other part of the query (probably on a different FDW), and that is taking a long time.
Are all the connections piling up from postgres_fdw, or are many of them from other applications? I think your timeout is just shifting symptoms around without fixing the underlying problem, while also making that underlying problem hard to diagnose.
3)yes, i think we kind of understood that part (fetch and memory), but i am not sure if that is used as any hint in plan generation too.
The query is planned as part of a cursor. As such, it will use cursor_tuple_fraction as the "hint". Perhaps you could tweak this parameter on the foreign side. I think that a low setting for this parameter should give similar plans as a small LIMIT would give you, while large settings would give the same plans as a large (or no) LIMIT would.
I think postgres_fdw should pass does the LIMIT when it can do so, but it doesn't currently.
Cheers,
Jeff
