My mistake, very sorry, remote object is actually a view
On Tue, Oct 4, 2022, 18:39 Lothar Flatz <l.flatz@xxxxxxxxxx> wrote:
Hi Laurentiu,
I wonder if the estimates are not the more important concern.
If 1 would be correct in both places and can not see one hour runtime.
Thus, correct the estimates and the plans should fall into place.
Apart from that: the overhead of fetching rows over a network was so far
never considered in the costing. Maybe that has changed now.
Thanks
Lothar
Am 04.10.2022 um 16:38 schrieb Laurentiu Oprea:
Hello everyone,
DB version 19.5
I have a situation (which I think I saw in the past) where in the
execution plan the result of a NL join, estimated to cardinality 1, is
hash joined with a remote table (estimated similarly to cardinality 1).
The problem is, because of HASH join(right outer), the join predicate
is not pushed in the remote query..and as a consequence the remote
query is taking 1 hour. With NL, the remote query will take just a few
seconds (because of the added where clause).
Looking into 10053 looks like the cost for NL is 27.154647 and cost
for Hash is 27.180501, so in theory NL should be used but just above
best join method I can see: "Cost adjustment for NL join with remote
table 0.005859" and then HASH is used.
Can someone help me understand this "Cost adjustment for NL"?
Thank you.
