In a earlier weblog submit, we’ve marketed the use of SQL EXISTS relatively than COUNT(*) to verify for existence of a worth in SQL.
I.e. to verify if within the Sakila database, actors referred to as WAHLBERG have performed in any movies, as a substitute of:
SELECT depend(*)
FROM actor a
JOIN film_actor fa USING (actor_id)
WHERE a.last_name = 'WAHLBERG'
Do that:
SELECT EXISTS (
SELECT 1 FROM actor a
JOIN film_actor fa USING (actor_id)
WHERE a.last_name = 'WAHLBERG'
)
(Relying in your dialect you could require a FROM DUAL clause, or a CASE expression if BOOLEAN varieties aren’t supported).
Verify for a number of rows
However what if you wish to verify if there are a minimum of 2 (or N) rows? In that case, you can’t use EXISTS, however must revert to utilizing COUNT(*). Nonetheless, as a substitute of simply counting all matches, why not add a LIMIT clause as nicely? So, if you wish to verify if actors referred to as WAHLBERG have performed in a minimum of 2 movies, as a substitute of this:
SELECT (
SELECT depend(*)
FROM actor a
JOIN film_actor fa USING (actor_id)
WHERE a.last_name = 'WAHLBERG'
) >= 2
Write this:
SELECT (
SELECT depend(*)
FROM (
SELECT *
FROM actor a
JOIN film_actor fa USING (actor_id)
WHERE a.last_name = 'WAHLBERG'
LIMIT 2
) t
) >= 2
In different phrases:
- Run the be a part of question with a
LIMIT 2in a derived desk - Then
COUNT(*)the rows (at most 2) from that derived desk - Lastly, verify if the depend is excessive sufficient
Does it matter?
In precept, the optimiser might have figured this out itself, particularly as a result of we used a relentless to check the COUNT(*) worth with. However did it actually apply the transformation?
Let’s verify execution plans and benchmark the question on varied RDBMS.
PostgreSQL 15
No LIMIT
End result (price=14.70..14.71 rows=1 width=1) (precise time=0.039..0.039 rows=1 loops=1)
InitPlan 1 (returns $1)
-> Mixture (price=14.69..14.70 rows=1 width=8) (precise time=0.037..0.037 rows=1 loops=1)
-> Nested Loop (price=0.28..14.55 rows=55 width=0) (precise time=0.009..0.032 rows=56 loops=1)
-> Seq Scan on actor a (price=0.00..4.50 rows=2 width=4) (precise time=0.006..0.018 rows=2 loops=1)
Filter: ((last_name)::textual content="WAHLBERG"::textual content)
Rows Eliminated by Filter: 198
-> Index Solely Scan utilizing film_actor_pkey on film_actor fa (price=0.28..4.75 rows=27 width=4) (precise time=0.003..0.005 rows=28 loops=2)
Index Cond: (actor_id = a.actor_id)
Heap Fetches: 0
With LIMIT
End result (price=0.84..0.85 rows=1 width=1) (precise time=0.023..0.024 rows=1 loops=1)
InitPlan 1 (returns $1)
-> Mixture (price=0.83..0.84 rows=1 width=8) (precise time=0.021..0.022 rows=1 loops=1)
-> Restrict (price=0.28..0.80 rows=2 width=240) (precise time=0.016..0.018 rows=2 loops=1)
-> Nested Loop (price=0.28..14.55 rows=55 width=240) (precise time=0.015..0.016 rows=2 loops=1)
-> Seq Scan on actor a (price=0.00..4.50 rows=2 width=4) (precise time=0.008..0.008 rows=1 loops=1)
Filter: ((last_name)::textual content="WAHLBERG"::textual content)
Rows Eliminated by Filter: 1
-> Index Solely Scan utilizing film_actor_pkey on film_actor fa (price=0.28..4.75 rows=27 width=4) (precise time=0.005..0.005 rows=2 loops=1)
Index Cond: (actor_id = a.actor_id)
Heap Fetches: 0
To know the distinction, concentrate on these rows:
Earlier than:
Nested Loop (price=0.28..14.55 rows=55 width=0) (precise time=0.009..0.032 rows=56 loops=1)
After:
Nested Loop (price=0.28..14.55 rows=55 width=240) (precise time=0.015..0.016 rows=2 loops=1)
In each instances, the estimated variety of rows produced by the be a part of is 55 (i.e. all WAHLBERGs are anticipated to have performed in a complete of 55 movies in response to statistics).
However int he second execution the precise rows worth is far decrease, as a result of we solely wanted 2 rows earlier than we might cease execution of the operation, due to the LIMIT above.
Benchmark outcomes:
Utilizing our really helpful SQL benchmarking method that compares operating two queries many instances (5 runs x 2000 executions on this case) on the identical occasion instantly from throughout the RDBMS utilizing procedural languages (to keep away from community latency, and so on.), we get these outcomes:
RUN 1, Assertion 1: 2.61927
RUN 1, Assertion 2: 1.01506
RUN 2, Assertion 1: 2.47193
RUN 2, Assertion 2: 1.00614
RUN 3, Assertion 1: 2.63533
RUN 3, Assertion 2: 1.14282
RUN 4, Assertion 1: 2.55228
RUN 4, Assertion 2: 1.00000 -- Quickest run is 1
RUN 5, Assertion 1: 2.53801
RUN 5, Assertion 2: 1.02363
The quickest run is 1 models of time, slower runs run in multiples of that point. The whole COUNT(*) question is persistently and considerably slower than the LIMIT question.
Each the plans and benchmark outcomes converse for themselves.
Oracle 23c
With Oracle 23c, we are able to lastly use BOOLEAN varieties and omit DUAL, yay!
No FETCH FIRST:
SQL_ID 40yy0tskvs1zw, little one quantity 0
-------------------------------------
SELECT /*+GATHER_PLAN_STATISTICS*/ ( SELECT depend(*)
FROM actor a JOIN film_actor fa USING (actor_id)
WHERE a.last_name="WAHLBERG" ) >= 2Plan hash worth: 2539243977
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Title | Begins | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 0 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 6 |
| 2 | NESTED LOOPS | | 1 | 55 | 56 |00:00:00.01 | 6 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR | 1 | 2 | 2 |00:00:00.01 | 2 |
|* 4 | INDEX RANGE SCAN | IDX_ACTOR_LAST_NAME | 1 | 2 | 2 |00:00:00.01 | 1 |
|* 5 | INDEX RANGE SCAN | IDX_FK_FILM_ACTOR_ACTOR | 2 | 27 | 56 |00:00:00.01 | 4 |
| 6 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 |
---------------------------------------------------------------------------------------------------------------------------Predicate Info (recognized by operation id):
---------------------------------------------------4 - entry("A"."LAST_NAME"='WAHLBERG')
5 - entry("A"."ACTOR_ID"="FA"."ACTOR_ID")
With FETCH FIRST:
SQL_ID f88t1r0avnr7b, little one quantity 0
-------------------------------------
SELECT /*+GATHER_PLAN_STATISTICS*/( SELECT depend(*)
from ( choose * FROM actor a JOIN
film_actor fa USING (actor_id) WHERE a.last_name =
'WAHLBERG' FETCH FIRST 2 ROWS ONLY ) t )
>= 2Plan hash worth: 4019277616
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Title | Begins | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 0 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 6 | | | |
|* 2 | VIEW | | 1 | 2 | 2 |00:00:00.01 | 6 | | | |
|* 3 | WINDOW BUFFER PUSHED RANK | | 1 | 55 | 2 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
| 4 | NESTED LOOPS | | 1 | 55 | 56 |00:00:00.01 | 6 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| ACTOR | 1 | 2 | 2 |00:00:00.01 | 2 | | | |
|* 6 | INDEX RANGE SCAN | IDX_ACTOR_LAST_NAME | 1 | 2 | 2 |00:00:00.01 | 1 | | | |
|* 7 | INDEX RANGE SCAN | IDX_FK_FILM_ACTOR_ACTOR | 2 | 27 | 56 |00:00:00.01 | 4 | | | |
| 8 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------Predicate Info (recognized by operation id):
---------------------------------------------------2 - filter("from$_subquery$_005"."rowlimit_$$_rownumber"<=2)
3 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=2)
6 - entry("A"."LAST_NAME"='WAHLBERG')
7 - entry("A"."ACTOR_ID"="FA"."ACTOR_ID")
Uh oh, this doesn’t look higher. The NESTED LOOPS operation doesn’t appear to have gotten the memo from the WINDOW BUFFER PUSHED RANK operation in regards to the question being aborted. The E-Rows (estimated) and A-Rows (precise) values nonetheless match, so the JOIN appears to be executed fully.
For good measure, let’s additionally attempt:
With ROWNUM:
I had hoped that this undead syntax belongs solely to distant recollections after Oracle 12c launched the usual SQL FETCH syntax, however let’s attempt what occurs with this various:
SELECT (
SELECT depend(*)
FROM (
SELECT *
FROM actor a
JOIN film_actor fa USING (actor_id)
WHERE a.last_name = 'WAHLBERG'
AND ROWNUM <= 2 -- Yuck, however it works
) t
) >= 2
The plan is now:
SQL_ID 6r7w9d0425j6c, little one quantity 0
-------------------------------------
SELECT /*+GATHER_PLAN_STATISTICS*/( SELECT depend(*)
from ( choose * FROM actor a JOIN
film_actor fa USING (actor_id) WHERE a.last_name =
'WAHLBERG' AND ROWNUM <= 2 ) t ) >= 2Plan hash worth: 1271700124
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Title | Begins | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 0 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 4 |
| 2 | VIEW | | 1 | 2 | 2 |00:00:00.01 | 4 |
|* 3 | COUNT STOPKEY | | 1 | | 2 |00:00:00.01 | 4 |
| 4 | NESTED LOOPS | | 1 | 55 | 2 |00:00:00.01 | 4 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR | 1 | 2 | 1 |00:00:00.01 | 2 |
|* 6 | INDEX RANGE SCAN | IDX_ACTOR_LAST_NAME | 1 | 2 | 1 |00:00:00.01 | 1 |
|* 7 | INDEX RANGE SCAN | IDX_FK_FILM_ACTOR_ACTOR | 1 | 27 | 2 |00:00:00.01 | 2 |
| 8 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 |
-----------------------------------------------------------------------------------------------------------------------------Predicate Info (recognized by operation id):
---------------------------------------------------3 - filter(ROWNUM<=2)
6 - entry("A"."LAST_NAME"='WAHLBERG')
7 - entry("A"."ACTOR_ID"="FA"."ACTOR_ID")
Now, that’s what I’m speaking about. The NESTED LOOPS operation has a A-Rows worth of 2, because it ought to have. The COUNT STOPKEY operation is aware of how you can inform its successors to behave.
Benchmark outcomes:
Run 1, Assertion 1 : 1.9564
Run 1, Assertion 2 : 2.98499
Run 1, Assertion 3 : 1.07291
Run 2, Assertion 1 : 1.69192
Run 2, Assertion 2 : 2.66905
Run 2, Assertion 3 : 1.01144
Run 3, Assertion 1 : 1.71051
Run 3, Assertion 2 : 2.63831
Run 3, Assertion 3 : 1 -- Quickest run is 1
Run 4, Assertion 1 : 1.61544
Run 4, Assertion 2 : 2.67334
Run 4, Assertion 3 : 1.00786
Run 5, Assertion 1 : 1.72981
Run 5, Assertion 2 : 2.77913
Run 5, Assertion 3 : 1.02716
Whoopsies. Certainly, it seems that the FETCH FIRST 2 ROWS ONLY clause is unhealthy on this case. It even made efficiency worse than if we omit it and depend the entire end result. Nonetheless, the ROWNUM filter helped vastly, similar to earlier than with PostgreSQL’s LIMIT. I might think about this an optimiser bug in Oracle. FETCH FIRST needs to be an operation that may be pushed down to numerous different operations
MySQL
No LIMIT:
-> Rows fetched earlier than execution (price=0.00..0.00 rows=1) (precise time=0.000..0.000 rows=1 loops=1)
-> Choose #2 (subquery in projection; run solely as soon as)
-> Mixture: depend(0) (price=1.35 rows=1) (precise time=0.479..0.479 rows=1 loops=1)
-> Nested loop interior be a part of (price=1.15 rows=2) (precise time=0.077..0.110 rows=56 loops=1)
-> Overlaying index lookup on a utilizing idx_actor_last_name (last_name="WAHLBERG") (price=0.45 rows=2) (precise time=0.059..0.061 rows=2 loops=1)
-> Overlaying index lookup on fa utilizing PRIMARY (actor_id=a.actor_id) (price=0.30 rows=1) (precise time=0.011..0.021 rows=28 loops=2)
With LIMIT:
-> Rows fetched earlier than execution (price=0.00..0.00 rows=1) (precise time=0.000..0.000 rows=1 loops=1)
-> Choose #2 (subquery in projection; run solely as soon as)
-> Mixture: depend(0) (price=4.08..4.08 rows=1) (precise time=0.399..0.400 rows=1 loops=1)
-> Desk scan on t (price=2.62..3.88 rows=2) (precise time=0.394..0.394 rows=2 loops=1)
-> Materialize (price=1.35..1.35 rows=2) (precise time=0.033..0.033 rows=2 loops=1)
-> Restrict: 2 row(s) (price=1.15 rows=2) (precise time=0.024..0.025 rows=2 loops=1)
-> Nested loop interior be a part of (price=1.15 rows=2) (precise time=0.024..0.024 rows=2 loops=1)
-> Overlaying index lookup on a utilizing idx_actor_last_name (last_name="WAHLBERG") (price=0.45 rows=2) (precise time=0.014..0.014 rows=1 loops=1)
-> Overlaying index lookup on fa utilizing PRIMARY (actor_id=a.actor_id) (price=0.30 rows=1) (precise time=0.008..0.008 rows=2 loops=1)
We once more get the Nested loop interior be a part of row with the needed distinction:
Earlier than:
Nested loop interior be a part of (price=1.15 rows=2) (precise time=0.077..0.110 rows=56 loops=1)
After:
Nested loop interior be a part of (price=1.15 rows=2) (precise time=0.024..0.024 rows=2 loops=1)
Benchmark outcomes:
Once more, the LIMIT is useful, although the distinction is much less spectacular:
0 1 1.2933
0 2 1.0089
1 1 1.2489
1 2 1.0000 -- Quickest run is 1
2 1 1.2444
2 2 1.0933
3 1 1.2133
3 2 1.0178
4 1 1.2267
4 2 1.0178
SQL Server
No LIMIT:
|--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [Expr1004]>=(2) THEN (1) ELSE (0) END))
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1010],0)))
|--Stream Mixture(DEFINE:([Expr1010]=Depend(*)))
|--Nested Loops(Interior Be part of, OUTER REFERENCES:([a].[actor_id]))
|--Desk Scan(OBJECT:([sakila].[dbo].[actor] AS [a]), WHERE:([sakila].[dbo].[actor].[last_name] as [a].[last_name]='WAHLBERG'))
|--Index Search(OBJECT:([sakila].[dbo].[film_actor].[PK__film_act__086D31FF6BE587FC] AS [fa]), SEEK:([fa].[actor_id]=[sakila].[dbo].[actor].[actor_id] as [a].[actor_id]) ORDERED FORWARD)
With LIMIT:
|--Compute Scalar(DEFINE:([Expr1007]=CASE WHEN [Expr1005]>=(2) THEN (1) ELSE (0) END))
|--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1011],0)))
|--Stream Mixture(DEFINE:([Expr1011]=Depend(*)))
|--High(TOP EXPRESSION:((2)))
|--Nested Loops(Interior Be part of, OUTER REFERENCES:([a].[actor_id]))
|--Desk Scan(OBJECT:([sakila].[dbo].[actor] AS [a]), WHERE:([sakila].[dbo].[actor].[last_name] as [a].[last_name]='WAHLBERG'))
|--Index Search(OBJECT:([sakila].[dbo].[film_actor].[PK__film_act__086D31FF6BE587FC] AS [fa]), SEEK:([fa].[actor_id]=[sakila].[dbo].[actor].[actor_id] as [a].[actor_id]) ORDERED FORWARD)
The textual content model doesn’t point out precise rows, even with SHOWPLAN_ALL, so let’s simply take a look at what occurs within the benchmark:
Benchmark outcomes:
Run 1, Assertion 1: 1.92118
Run 1, Assertion 2: 1.00000 -- Quickest run is 1
Run 2, Assertion 1: 1.95567
Run 2, Assertion 2: 1.01724
Run 3, Assertion 1: 1.91379
Run 3, Assertion 2: 1.01724
Run 4, Assertion 1: 1.93842
Run 4, Assertion 2: 1.04926
Run 5, Assertion 1: 1.95567
Run 5, Assertion 2: 1.03448
And once more, a powerful 2x enchancment for this specific question
Conclusion
Simply as with our earlier weblog submit about COUNT(*) vs EXISTS, the seemingly apparent is true once more on this case the place we need to verify if N or extra rows exist in a question. If we blindly depend all of the rows, then we’ve seen a lot worse efficiency than if we helped the optimiser with a LIMIT or TOP clause, or ROWNUM in Oracle.
Technically, an optimiser might have detected this optimisation itself, however as our earlier article about optimisations that don’t rely upon the fee mannequin has proven, optimisers don’t at all times do every little thing they’ll.
Sadly, in Oracle’s case, the usual SQL syntax made issues slower (on this benchmark). This doesn’t imply it’s typically slower for all instances, however it’s one thing value looking for. There are nonetheless instances the place historical ROWNUM clause is best optimised. That is a kind of instances.
Whether or not syntax X is quicker than syntax Y may be proven by learning execution plans (not simply with estimates, however with precise values), or by operating a easy SQL benchmark. As at all times with benchmarks, watch out when decoding outcomes, double verify, attempt extra options.
