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

On Sun, Feb 17, 2019 at 2:37 PM Vijaykumar Jain <vjain(at)opentable(dot)com> wrote:

>
> Ok, i raked this from the logs where enabled log_min_duration_statement =
> 10s
>
> 2019-01-31 12:48:18 UTC LOG: duration: 29863.311 ms statement: EXPLAIN
> SELECT blah, FROM public.view WHERE ((scheduled_bdt >= '2019-01-20'::date))
> AND ((scheduled_bdt <= '2019-01-26'::date)) AND ((somekey = ANY
> ('{269029,123399,263164,261487}'::bigint[]))) (both the columns are
> indexed)
>

That is interesting. Was that in the logs for the local or the foreign
side? And is it common, or rare?

If on the local side, could it be that the EXPLAINs sent to the foreign
side are being made to wait by the connection pooler, leading to long
delays? If that is from the foreign side, then it should be conceptually
unrelated to FDW. Any chance you could reproduce the slowness in your test
environment? Slowness in the planner is probably related to the schema
structure, not the data itself.

I don't think this would be related to the idle-in-transaction, except that
one FDW connection maybe idle-in-transaction after its EXPLAIN is done
because it is waiting for another FDW connection to slowly run its EXPLAIN.

Cheers,

Jeff

>