Re: [External] Re: FDW, too long to run explain

On Sun, Feb 17, 2019 at 1:52 PM Vijaykumar Jain <vjain(at)opentable(dot)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

>