Vijay
On Mon, Feb 18, 2019 at 12:56 AM Jeff Janes <jeff.janes@xxxxxxxxx> wrote:
On Sun, Feb 17, 2019 at 1:52 PM Vijaykumar Jain <vjain@xxxxxxxxxxxxx> 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.
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)
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.
same application, but when more than one person is using the analytical tool that runs the underlying query.
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.
As i already said, we have overcome the limit issue with a fake sample column in the huge tables. that way we limit the number of rows on the foreign server itself before the fetch. this is not the best and has its edge cases, but yeah, it works for now.
Cheers,Jeff
