On Sun, Feb 17, 2019 at 1:52 PM Vijaykumar Jain <vjain@opentable.com> 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.