On Sun, Feb 17, 2019 at 1:52 PM Vijaykumar Jain <vj...@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. Cheers, Jeff >
