In a earlier weblog put up, we’ve marketed the use of SQL EXISTS relatively than COUNT(*) to test for existence of a price in SQL.
I.e. to test if within the Sakila database, actors known 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 chances are you’ll require a FROM DUAL clause, or a CASE expression if BOOLEAN varieties aren’t supported).
Examine for a number of rows
However what if you wish to test if there are at the least 2 (or N) rows? In that case, you can’t use EXISTS, however should revert to utilizing COUNT(*). Nonetheless, as a substitute of simply counting all matches, why not add a LIMIT clause as effectively? So, if you wish to test if actors known as WAHLBERG have performed in at the least 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 part of question with a
LIMIT 2in a derived desk - Then
COUNT(*)the rows (at most 2) from that derived desk - Lastly, test if the depend is excessive sufficient
Does it matter?
In precept, the optimiser may have figured this out itself, particularly as a result of we used a relentless to match the COUNT(*) worth with. However did it actually apply the transformation?
Let’s test execution plans and benchmark the question on varied RDBMS.
PostgreSQL 15
No LIMIT
Outcome (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
Outcome (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 grasp 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 circumstances, the estimated variety of rows produced by the be part of is 55 (i.e. all WAHLBERGs are anticipated to have performed in a complete of 55 movies in line with 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 may cease execution of the operation, due to the LIMIT above.
Benchmark outcomes:
Utilizing our advisable SQL benchmarking method that compares operating two queries many occasions (5 runs x 2000 executions on this case) on the identical occasion immediately from inside 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 items 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 communicate for themselves.
Oracle 23c
With Oracle 23c, we will lastly use BOOLEAN varieties and omit DUAL, yay!
No FETCH FIRST:
SQL_ID 40yy0tskvs1zw, youngster 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 | Identify | 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 Data (recognized by operation id):
---------------------------------------------------4 - entry("A"."LAST_NAME"='WAHLBERG')
5 - entry("A"."ACTOR_ID"="FA"."ACTOR_ID")
With FETCH FIRST:
SQL_ID f88t1r0avnr7b, youngster 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 | Identify | 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 Data (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 utterly.
For good measure, let’s additionally strive:
With ROWNUM:
I had hoped that this undead syntax belongs solely to distant reminiscences after Oracle 12c launched the usual SQL FETCH syntax, however let’s strive what occurs with this different:
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, youngster 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 | Identify | 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 Data (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 the best way to 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 dangerous on this case. It even made efficiency worse than if we omit it and depend the entire end result. Nonetheless, the ROWNUM filter helped drastically, similar to earlier than with PostgreSQL’s LIMIT. I might take into account this an optimiser bug in Oracle. FETCH FIRST must 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 internal be 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 internal be 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 internal be part of row with the wished distinction:
Earlier than:
Nested loop internal be part of (price=1.15 rows=2) (precise time=0.077..0.110 rows=56 loops=1)
After:
Nested loop internal be 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 a 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(*)))
|--Prime(TOP EXPRESSION:((2)))
|--Nested Loops(Interior Be a 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 put up about COUNT(*) vs EXISTS, the seemingly apparent is true once more on this case the place we need to test 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 may have detected this optimisation itself, however as our earlier article about optimisations that don’t rely upon the price mannequin has proven, optimisers don’t all the time do all the pieces 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 circumstances, however it’s one thing value looking for. There are nonetheless circumstances the place historic ROWNUM clause is healthier optimised. That is a kind of circumstances.
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 all the time with benchmarks, watch out when deciphering outcomes, double test, strive extra options.
