Grundsätzlich gebe ich Dir Recht. Mich hat es auch gewundert, dass man Tags mit Wildcard sucht.
Aber das immer stimmt nicht. In Postgres kann man einen trigram Index anlegen, der dann auch von einer Bedingung wie where tag ilike '%bla%'
verwendet wird. Da die Volltextsuche nicht unbedingt gut geeignet ist für eine Teilwortsuche (also das was LIKE macht), ist das vielleicht sogar die bessere Alternative in diesem Fall.
Die Variante die Tags zu normalisieren, so dass man mit =
(oder IN
) arbeiten kann, ist aber vermutlich insgesamt schneller.
Postgres ist bei der Variante mit LEFT JOIN etwas empfindlicher welche Spalte man dann auf NULL prüft (für ein NOT EXISTS äquivalent). Wenn man das IS NOT NULL auf der FK Spalte macht geht’s schnell:
select count(c.id)
from customers c
left join orders o on o.customer_id = c.id
where o.customer_id is null;
Dazugehöriger Plan ist:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=50352.17..50352.18 rows=1 width=8) (actual time=147.754..147.755 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=300850
-> Nested Loop Anti Join (cost=0.43..50286.63 rows=26213 width=0) (actual time=147.752..147.752 rows=0 loops=1)
Buffers: shared hit=300850
-> Seq Scan on stuff.customers c (cost=0.00..1849.00 rows=100000 width=4) (actual time=0.011..6.295 rows=100000 loops=1)
Output: c.id, c.customer_name, c.zip_code, c.city
Buffers: shared hit=849
-> Index Only Scan using orders_customer_id_idx on stuff.orders o (cost=0.43..1.27 rows=36 width=4) (actual time=0.001..0.001 rows=1 loops=100000)
Output: o.customer_id
Index Cond: (o.customer_id = c.id)
Heap Fetches: 0
Buffers: shared hit=300001
Settings: max_parallel_workers_per_gather = '0'
Planning:
Buffers: shared hit=14
Planning Time: 0.286 ms
Execution Time: 147.790 ms
Aber das hier ist super langsam:
select count(c.id)
from customers c
left join orders o on o.customer_id = c.id
where o.id is null;
Dazu gehöriger Plan ist:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=74233.43..74233.44 rows=1 width=8) (actual time=1414.097..1414.099 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=17565, temp read=4633 written=4633
-> Hash Right Join (cost=3490.00..74233.43 rows=1 width=0) (actual time=1414.093..1414.095 rows=0 loops=1)
Inner Unique: true
Hash Cond: (o.customer_id = c.id)
Filter: (o.id IS NULL)
Rows Removed by Filter: 2624378
Buffers: shared hit=17565, temp read=4633 written=4633
-> Seq Scan on stuff.orders o (cost=0.00..42959.28 rows=2624328 width=8) (actual time=0.020..335.147 rows=2624378 loops=1)
Output: o.customer_id, o.id
Buffers: shared hit=16716
-> Hash (cost=1849.00..1849.00 rows=100000 width=4) (actual time=29.174..29.175 rows=100000 loops=1)
Output: c.id
Buckets: 131072 Batches: 2 Memory Usage: 2781kB
Buffers: shared hit=849, temp written=146
-> Seq Scan on stuff.customers c (cost=0.00..1849.00 rows=100000 width=4) (actual time=0.012..10.705 rows=100000 loops=1)
Output: c.id
Buffers: shared hit=849
Settings: max_parallel_workers_per_gather = '0'
Planning:
Buffers: shared hit=17
Planning Time: 0.228 ms
Execution Time: 1414.453 ms
Die NOT EXISTS Variante hat das Problem nicht, da man da grundsätzlich über die FK Spalte gehen muss. Dazu gehöriger Plan ist identisch zur ersten Variante (mit o.customer_id is null
):
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=50352.17..50352.18 rows=1 width=8) (actual time=147.777..147.779 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=300850
-> Nested Loop Anti Join (cost=0.43..50286.63 rows=26213 width=0) (actual time=147.773..147.774 rows=0 loops=1)
Buffers: shared hit=300850
-> Seq Scan on stuff.customers c (cost=0.00..1849.00 rows=100000 width=4) (actual time=0.015..6.011 rows=100000 loops=1)
Output: c.id, c.customer_name, c.zip_code, c.city
Buffers: shared hit=849
-> Index Only Scan using orders_customer_id_idx on stuff.orders o (cost=0.43..1.27 rows=36 width=4) (actual time=0.001..0.001 rows=1 loops=100000)
Output: o.customer_id
Index Cond: (o.customer_id = c.id)
Heap Fetches: 0
Buffers: shared hit=300001
Settings: max_parallel_workers_per_gather = '0'
Planning:
Buffers: shared hit=14
Planning Time: 0.283 ms
Execution Time: 147.825 ms
Oracle verwendet für NOT EXISTS und LEFT JOIN mit “fk column is null” absolut identische Pläne. Für die Variante “pk column is null” einen schlechteren Plan weil dabei der FK Index nicht verwendet wird.
select count(*)
from customers c
where not exists (select *
from orders o
where o.customer_id = c.id);
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 2033 (100)| 1 |00:00:00.16 | 3143 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 18 | | | 1 |00:00:00.16 | 3143 | | | |
|* 2 | HASH JOIN ANTI | | 1 | 89775 | 1578K| 2192K| 2033 (2)| 0 |00:00:00.16 | 3143 | 6597K| 3201K| 6991K (0)|
| 3 | INDEX FAST FULL SCAN| SYS_C0013196 | 1 | 89775 | 1139K| | 68 (0)| 100K|00:00:00.01 | 251 | | | |
| 4 | INDEX FAST FULL SCAN| ORD_CUST_ID | 1 | 1298K| 6340K| | 793 (2)| 1298K|00:00:00.07 | 2892 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
select count(*)
from customers c
left join orders o on o.customer_id = c.id
where o.customer_id is null;
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 2033 (100)| 1 |00:00:00.18 | 3143 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 18 | | | 1 |00:00:00.18 | 3143 | | | |
|* 2 | HASH JOIN ANTI | | 1 | 89775 | 1578K| 2192K| 2033 (2)| 0 |00:00:00.18 | 3143 | 6597K| 3201K| 6992K (0)|
| 3 | INDEX FAST FULL SCAN| SYS_C0013196 | 1 | 89775 | 1139K| | 68 (0)| 100K|00:00:00.01 | 251 | | | |
| 4 | INDEX FAST FULL SCAN| ORD_CUST_ID | 1 | 1298K| 6340K| | 793 (2)| 1298K|00:00:00.09 | 2892 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
select count(*)
from customers c
left join orders o on o.customer_id = c.id
where o.id is null;
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 3026 (100)| 1 |00:00:00.10 | 5311 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 24 | | | 1 |00:00:00.10 | 5311 | | | |
|* 2 | FILTER | | 1 | | | | | 0 |00:00:00.10 | 5311 | | | |
|* 3 | HASH JOIN OUTER | | 1 | 89775 | 2104K| 2192K| 3026 (2)| 1298K|00:00:00.10 | 5311 | 6597K| 3201K| 6914K (0)|
| 4 | INDEX FAST FULL SCAN| SYS_C0013196 | 1 | 89775 | 1139K| | 68 (0)| 100K|00:00:00.01 | 251 | | | |
| 5 | TABLE ACCESS FULL | ORDERS | 1 | 1298K| 13M| | 1418 (2)| 1298K|00:00:00.03 | 5060 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------
Also zumindest für Postgres & Oracle ist die NOT EXISTS Variante damit die “sicherere Wahl” weil man bei der Bedingung nichts falsch machen kann.