Re: Same query 10000x More Time

On Thu, Jan 6, 2022, 3:50 PM Avi Weinberg <AviW(at)gilat(dot)com> wrote:

> Hi Kyotaro Horiguchi and Vijaykumar Jain,
>
> Thanks for your quick reply!
>
> I understand that the fact the slow query has a join caused this problem.
> However, why can't Postgres evaluate the table of the "IN" clause (select
> 140 as id union select 144 union select 148) and based on its size decide
> what is more optimal.
> Push the local table to the linked server to perform the join on the
> linked server
> Pull the linked server table to local to perform the join on the local.
>
> In my case the table size of the local is million times smaller than the
> table size of the remote.

I understand when the optimizer makes a decision it uses stats to use the
least expensive plan to get the result.
I can reply but I am pretty sure making an analogy to a local setup of big
and small table is not the same as small local table and a big remote table.
I would leave it to the experts here unless you are open to read the src
for postgres_fdw extension.
https://github.com/postgres/postgres/blob/master/contrib/postgres_fdw/postgres_fdw.c

There must be a reason if that is beyond cost calculation as to why this
happens.
Else if this is all just cost based, you can try tweaking the cost params
and see if you can get a better plan.

For exp, if you force parallel cost to 0 on the foreign server, it may use
parallel workers and do some speed up, but given my exp, fighting optimizer
is mostly asking for trouble :)