It needs to validate that the row you are trying to delete is not a parent of an existing row.
You don't have an index on ParentTestId
.
So it must do the scan.
CREATE NONCLUSTERED INDEX ix ON [dbo].[Test](ParentTestId)
Then you see a seek.
BTW: The 20% estimated cost of the scan is likely to be an underestimate in this case.
The FK validation is under a left semi join and SQL Server costs it as though only a partial scan will be needed and it will find a matching row and the delete will fail.
Presumably the rows you are actually deleting will succeed more often than not and so a full scan will be required in order to validate that there are no conflicting rows.
Using trace flag 4138
to turn off row goals
DELETE FROM dbo.Test
WHERE TestId = 200
OPTION (querytraceon 4138 )
The re-costed plan shows the CI scan at 100% rather than 20% (as it now assumes a full scan will be needed)
This difference in estimated cost is sufficient for the missing index suggestion to show up.
The costs shown in this plan are still not very representative however. You might notice that they add up to 219%.
Also the overall plan cost of the queries with and without the trace flag are both identical at 0.0168268
. The full CI scan ought, in fact, to be costed at 0.152373
(0.0485075 + 0.103866
)
but it seems to be capped at no more than the original plan cost (and the overall plan cost doesn't get adjusted upwards either hence incorrect percentages)
EDIT: I am leaving the original accepted answer as it is, but please note that the edit below, as suggested by a_horse_with_no_name, is the preferred method for creating a temporary table using VALUES.
If you just want to select from some values, rather than just creating a table and inserting into it, you can do something like:
WITH vals (k,v) AS (VALUES (0,-9999), (1, 100))
SELECT * FROM vals;
To actually create a temporary table in a similar fashion, use:
WITH vals (k,v) AS (VALUES (0,-9999), (1, 100))
SELECT * INTO temporary table temp_table FROM vals;
EDIT: As pointed out by a_horse_with_no_name, in the docs it states that CREATE TABLE AS...
is functionally similar to SELECT INTO ...
, but that the former is a superset of the latter and that SELECT INTO
is used in plpgslq for assigning a value to a temporary variable -- so it would fail in that case. Therefore, while the above examples are valid for plain SQL, the CREATE TABLE
form should be preferred.
CREATE TEMP TABLE temp_table AS
WITH t (k, v) AS (
VALUES
(0::int,-99999::numeric),
(1::int,100::numeric)
)
SELECT * FROM t;
Note, also from the comments by a_horse_with_no_name, and in the OP's original question, this includes a cast to the correct datatypes inside the values list and uses a CTE (WITH) statement.
Also, as pointed out in Evan Carrol's answer, in Postgres prior to version 12 a CTE query is always an optimization fence, ie, the CTE is always materialized. There are many good reasons for using CTEs, but there can be quite a significant performance hit, if not used carefully. There are, however, many instances where the optimization fence can actually enhance performance, so this is something to be aware of, not to blindly avoid.
Best Answer
If you just want to return a true or false upon comparing an employee and a manager, try this: