On Thu, Jan 6, 2022, 3:50 PM Avi Weinberg <AviW@xxxxxxxxx> 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.
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 :)
