Am I missing anything here?
Not really. You are discovering that distributed query processing has lots of tricky limitations like this.
So the rule of thumb for using Linked Servers is to always use passthrough queries if you want remote execution.
UPDATE database1.dbo.table1 WITH(ROWLOCK)
SET number = ISNULL(number,0)
WHERE accounts = ''123''
I'm not 100% sure other than the difference in execution plan is enough for it to dumbly switch to a Remote Scan instead of Remote Query (maybe because of the order of events with the SET operator and using a function on the value being set, statistics or sargable related?).
What if you wrote the query like this instead, what does the execution plan show?
It's could be the lack of column-level statistics on the table variable. Try the same approach, but instead using a real temp table instead, like this:
CREATE TABLE #Query
Id int NOT NULL,
Time datetime NOT NULL
INSERT INTO #Query VALUES
ALTER TABLE #Query
ADD CONSTRAINT PK_Query PRIMARY KEY (Id);
This just looks like a quirk with the optimisation of that query producing a slightly different tree shape when simple parameterisation is attempted vs when it isn't.
This is not attempted in the case of the local temp table but is for the global temp table and permanent table.
The auto parameterisation does not succeed in any case but when attempted on this ...
Test 1 is logically incorrect for this use-case. If you provide NULL as the value of :B1, it will return 0 rows.
Test 2 is logically correct, but its execution plan is suboptimal, so is performance. In this case the optimizer is unable to utilize the index, because NULL values are not indexed by default.
Test 3 is logically correct, and its execution plan is ...
The query plan spills that you have an index payments_last_updated. That's all we need for payments.
As for assessmentreports:
There can be multiple entries in assessmentreports for one payment_id each with a different kind.
So there could (should) be this UNIQUE index:
CREATE UNIQUE INDEX assessmentreports_payment_id_kind_uni ON assessmentreports (...
There seem to be several things off:
The estimates are wrong. Since you already ANALYZEd both tables, retry with a higher default_statistics_target.
Since index scans seem to be estimated too expensive, perhaps your setting of random_page_cost is too high. Also, perhaps effective_cache_size is too low.
If you didn't select everything from books, a ...
To elaborate on your problem of bad estimates:
Postgres estimates to get 1393 rows from the index scan on run_books_index, but actually only finds 15. That's off by a factor of ~ 100.
... (cost=0.43..2448.52 rows=1393 width=32) (actual time=0.011..0.012 rows=15 loops=1)
After you ran ANALYZE, so we cannot blame outdated statistics.
@user1673665 ok, this definitelly looks like cardinality issue, prolly statistics.
You have SQL Server expecting few row, ie for scMat_MaterialText 4, but you get 62000. Thats way off.
Try to run a select on this table, then review estimates vs actual rows read. And try to update statistics. I would say thats the culprint
So I know this doesn't directly answer your question but I think are things to consider that may be of help:
Why do you lose the ability to create Views and would you be able to create a second database to house your Views instead? (Views can reference other databases besides the one they exist in.)
If the previous solution isn't possible, could you use a ...