Aan de andere kant is een recursieve functie in principe gewoon een normale Postgres functie die recursief wordt uitgevoerd (en zichzelf oproept). Onderstaand voorbeeld toont de recursieve functie die hetzelfde resultaat bereikt als de bovenstaande recursieve CTE.
Een eenvoudige use-case
De bovenstaande voorbeelden kunnen worden gezien als een eenvoudige use-case. Er zijn geen grote tabellen, geen complexe operaties en geen samenvoegingen van tabellen.
Het verschil in prestaties is triviaal. Recursieve CTE duurt 18,46 ms en recursieve functie duurt 42,54 ms. Maar de recursieve CTE is nog steeds een beetje sneller, netter en handiger omdat het gewoon een SQL is.
In dit geval is de winnaar de recursieve CTE.
Doorkruisen van een boom
Laten we eens kijken naar een complexer scenario. Ik heb een eenvoudige boomtabel gemaakt die boomknopen opslaat met slechts 2 kolommen (id en kind id). Er zijn 500 boomknopen (rijen) in. Elk knooppunt is uniek gekoppeld aan een ander knooppunt in de tabel.
Het doel is om alle kinderknooppunten te vinden, gegeven een knooppunt-id.
Implementatie met recursieve CTE
QUERY PERFORMANCE ANALYSIS
--------------------------CTE Scan on recursive_query (cost=113.31..115.33 rows=101 width=8) (actual time=0.686..1257.405 rows=500 loops=1)
CTE recursive_query
-> Recursive Union (cost=0.27..113.31 rows=101 width=8) (actual time=0.680..1254.706 rows=500 loops=1)
-> Index Scan using idx on my_tree (cost=0.27..8.29 rows=1 width=8) (actual time=0.664..0.670 rows=1 loops=1)
Index Cond: (id = 0)
-> Hash Join (cost=0.33..10.30 rows=10 width=8) (actual time=1.301..2.487 rows=1 loops=500)
Hash Cond: (t.id = recursive_query_1.child_id)
-> Seq Scan on my_tree t (cost=0.00..8.00 rows=500 width=8) (actual time=0.009..1.225 rows=500 loops=500)
-> Hash (cost=0.20..0.20 rows=10 width=4) (actual time=0.013..0.013 rows=1 loops=500)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> WorkTable Scan on recursive_query recursive_query_1 (cost=0.00..0.20 rows=10 width=4) (actual time=0.003..0.005 rows=1 loops=500)
Planning time: 0.688 ms
Execution time: 1259.438 ms
De query ziet er netjes uit. De prestaties zijn echter niet zo goed als ik had verwacht. Het duurde heel 1259.438 ms (bijna 1,3 seconde!). Als we beter kijken, zien we dat de trage delen
- 500 recursies zijn. Elke recursie roept een sequentie scan van de boomtabel op om kinderen te vinden.
Seq Scan on my_tree t (cost=0.00..8.00 rows=500 width=8) (actual time=0.009..1.225 rows=500 loops=500) - 500 unions voor het samenvoegen van nieuwe resultaten met bestaande resultaten
Laat me het beter uitleggen. De query herhaalt het recursieve deel om de kinderen van een node te vinden totdat er geen kinderen meer terugkomen. In dit geval wordt het 500 keer herhaald omdat er 500 knooppunten in de boomtabel zijn en elk knooppunt is uniek gekoppeld aan een ander, dus het moet 500 niveaus doorlopen om het laatste kind te vinden.
Postgres besluit om een Sequence Scan (scant elke entry van de tabel) op de boomtabel te gebruiken in plaats van een geoptimaliseerde Index Scan (Index kan worden gezien als een manier om resultaten te cachen om bulk reads te versnellen). Dus 500 keer scannen van een tabel met 500 rijen veroorzaakt een grote prestatie-hit.
De samenvoeging van recursie resultaten met bestaande rijen voegt ook meer prestatie-hits toe. De algehele prestatie is eigenlijk vrij slecht, gezien het feit dat het niet eens een enorme tabel is. Als de tabel groter wordt, kunnen de prestaties nog slechter zijn.
Het is zo traag. In werkelijkheid, als uw toepassing probeert om vaak en recursief te vinden kinderen knooppunten op deze manier, moet u uw strategie te heroverwegen! Je kunt recursief zoeken waarschijnlijk volledig vermijden als de ouder/kind relatie kan worden opgeslagen in een string structuur. Ik zal de details in dit artikel niet behandelen.
Implementatie met recursieve functie
QUERY PERFORMANCE ANALYSIS
--------------------------
Function Scan on recursive_function (cost=0.25..10.25 rows=1000 width=8) (actual time=132.234..133.347 rows=500 loops=1)
Planning time: 0.062 ms
Execution time: 134.489 ms
Het is een black box, omdat ik EXPLAIN ANALYSE heb gebruikt om de query performance te inspecteren. Er is geen eenvoudige manier om te inspecteren wat er in de functie gebeurt. Maar het feit is, dat het een veel sneller proces is dan recursieve CTE.
Mijn gok is dat alle queries die in deze functie worden gebruikt, condities zoals WHERE A.id = my_id gebruiken, waardoor Postgres de tabelindex kan gebruiken in plaats van de hele tabel te moeten scannen. Dit versnelt het proces van het vinden van kinderen aanzienlijk.
In dit specifieke geval is de recursieve functie de winnaar.
Het doorkruisen van een grotere boom
Ik heb de boomtabel vergroot tot 1000 rijen. Dit betekent dat de recursie 1000 keer moet gebeuren om het laatste kind te bereiken.
De recursieve CTE komt terug onder de 5.4 Seconden. (Het is tenslotte een lokale DB die op mijn trage computer wordt gehost)
Maar de recursieve functie geeft me ERROR: stack depth limit exceeded. HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
Als je niet weet hoeveel niveaus het kan gaan en mogelijk een groot aantal recursies kan oproepen, gebruik dan geen recursieve functie. Je zou het niet leuk vinden als deze fout optreedt bij productie.
Recursieve functie heeft een maximum diepte limiet
Deze fout geeft ook aan dat een recursieve functie veel geheugen MOGT gebruiken omdat het een nieuwe functie context aanmaakt als het dieper gaat. Een recursieve CTE gebruikt iteraties, dus al het werk wordt gedaan in dezelfde context. Het zou minder geheugen kunnen gebruiken.
Recursive CTE een winnaar in dit geval.
Conclusie
In dit artikel heb ik een paar scenario’s bestudeerd waar zowel Recursive CTE als Recursive Function kunnen worden toegepast.
Recursive CTE is eenvoudiger te schrijven maar de prestaties zijn niet geweldig vanwege het niet gebruiken van Index. Recursieve Functie is sneller, maar beperkt door recursie diepte als het zou kunnen gebruiken meer memory.
In werkelijkheid, moet u overwegen beide benaderingen en zich richten op de prestaties resultaat. Optimaliseer uw query te gebruiken tabel Index indien mogelijk. Als je kunt, vermijd het gebruik van recursieve dingen op alle om de prestaties implication.
te minimaliseren