Re: BUG #14845: postgres_fdw and mysql_fdw can cause crash of instance

On Fri, Oct 6, 2017 at 10:01 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Josef Machytka <josef(dot)machytka(at)gmail(dot)com> writes:
> > In this case which went wrong I used postgres_fdw to compare data on
> local
> > and remote database using "select all from remote except select all from
> > local".
>
> I think my theory B probably applies then: without use_remote_estimate,
> the planner would not guess that the remote table is huge, and that could
> well allow it to pick a hashed EXCEPT implementation --- which will then
> try to collect the entire remote table into an in-memory hash table.
>

Unfortunately, use_remote_estimate won't help for this particular case.
While it gets the estimate for the number of rows from the remote estimate,
HashSetOp doesn't care about estimated number of rows, only estimated
number of distinct rows. And use_remote_estimate doesn't provide that, so
it falls back on assuming (I think) 200 distinct rows.

What could help is running 'analyze' locally against the foreign table.
That can cause other problems, though.

Cheers,

Jeff