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

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: [BUGS] BUG #14845: postgres_fdw and mysql_fdw can cause crash of instance
Дата
Msg-id CAMkU=1yn=feWCzANUUG3okswzBM2EpFHcwv_R1Pii8HWKDaOyA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [BUGS] BUG #14845: postgres_fdw and mysql_fdw can cause crash of instance  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: [BUGS] BUG #14845: postgres_fdw and mysql_fdw can cause crash of instance  (Josef Machytka <josef.machytka@gmail.com>)
Список pgsql-bugs
Дерево обсуждения
On Fri, Oct 6, 2017 at 10:01 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Josef Machytka <josef.machytka@gmail.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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Andres Freund
Дата:
Сообщение: Re: [BUGS] json(b)_array_elements use causes very large memory usagewhen also referencing entire json document
Следующее
От: Andres Freund
Дата:
Сообщение: Re: [BUGS] json(b)_array_elements use causes very large memory usagewhen also referencing entire json document