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