Re: FDW, too long to run explain

On Mon, Feb 4, 2019 at 2:15 AM Vijaykumar Jain <vjain(at)opentable(dot)com> wrote:

>
> now we have some long running queries via FDW that take minutes and get
> killed explain runs as idle in transaction on remote servers.
>

Are you saying the EXPLAIN itself gets killed, or execution of the plan
generated based on the EXPLAIN (issued under use_remote_estimate = true)
gets killed? Who is doing the killing, the local side or the foreign
side? Can you include verbatim log entries for this?

> now the explain for
> select * from sh01.view1 keeps running for minutes sometimes,
>
> then fetch too keeps running for minutes, although the total rows are <
> 10000 maybe.
> idle in transaction | FETCH 10000 FROM c1
>

What is this? Is it from some monitoring tool, or pg_stat_activity, or
what? And is it on the local side or the foreign side?

> other questions:
> also, what is the cost of fetch_size?
>

It will always fetch rows from the foreign server in this sized chunks. A
larger fetch_size will have less network latency and computational overhead
if many rows are going to be consumed, but also consume more memory on the
local server as all rows are stored in memory per each chunk. Also, in the
case of a LIMIT, it reads a large number of rows even if most of them may
be unneeded. Conceptually, the LIMIT could be used to modify the FETCH
downward to match the LIMIT, but that is not implemented. In the case of a
view over UNION ALL, I don't think the individual subqueries even know what
the global LIMIT is.

>
> I mean given we have a query
>
> select * from foobar limit 10000; via FDW
> limit 10000 does not get pushed.
> so it seems all rows some to FDW node and then limit is applied?
>

It should not read all rows. It should read as many multiples of
fetch_size as needed, which should just be 1 multiple in this case.

Cheers,

Jeff

>