Opérations PostgreSQL que vous ne pouvez pas EXPLIQUER · Felix Geisendörfer
Publié :
Je m’intéresse actuellement aux composants internes de PostgreSQL pendant mon temps libre, notamment en essayant de comprendre le planificateur et l’exécuteur de requêtes. C’est une expérience profondément humiliante, mais je suis parfois ravi de pouvoir répondre à des questions simples sur lesquelles je fais des recherches.
L’une de ces questions est de savoir comment PostgreSQL s’exécute ORDER BY clauses à l’intérieur des expressions agrégées.
# CREATE TABLE foo AS
SELECT * FROM generate_series(1, 3) i;
SELECT 3
# SELECT * FROM foo;
i
---
1
2
3
(3 rows)
Considérons un tableau simple contenant 3 lignes d’une colonne int allant de 1 à 3 :
Supposons maintenant que nous souhaitions convertir ces lignes en un tableau json. Cela peut être accompli en utilisant la fonction d’agrégation json_agg :
# SELECT json_agg(i) FROM foo;
json_agg
-----------
(1, 2, 3)
(1 row)
Mais que se passe-t-il si nous voulons classer les éléments du tableau par ordre décroissant ? Pas de problème, nous pouvons simplement utiliser le support order-by-clause disponible pour toutes les expressions agrégées :
# SELECT json_agg(i ORDER BY i DESC) FROM foo;
json_agg
-----------
(3, 2, 1)
(1 row)
Si vous êtes comme moi, vous imaginerez probablement le EXPLAIN sortie pour cette requête devant contenir trois nœuds : un Seq Scanun Sortet un Aggregate. Cependant, lorsque nous courons EXPLAINil s’avère qu’il n’y a pas Sort nœud.
# EXPLAIN SELECT json_agg(i ORDER BY i DESC) FROM foo;
QUERY PLAN
-------------------------------------------------------------
Aggregate (cost=41.88..41.89 rows=1 width=32)
-> Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4)
(2 rows)
En fait, le plan reste même inchangé même si nous modifions l’ordre de tri ou supprimons entièrement la clause :
# EXPLAIN SELECT json_agg(i ORDER BY i ASC) FROM foo;
QUERY PLAN
-------------------------------------------------------------
Aggregate (cost=41.88..41.89 rows=1 width=32)
-> Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4)
(2 rows)
# EXPLAIN SELECT json_agg(i) FROM foo;
QUERY PLAN
-------------------------------------------------------------
Aggregate (cost=41.88..41.89 rows=1 width=32)
-> Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4)
(2 rows)
Mais comment est-ce possible ? S’il n’y a pas Sort nœud, comment l’agrégat effectue-t-il son tri ?
Eh bien, il s’avère que les nœuds du plan agrégé peuvent effectuer leur propre tri. Ces types n’apparaissent pas explicitement dans EXPLAINmais nous pouvons les tracer en utilisant l’option développeur trace_sort :
# SET trace_sort=true;
SET
# SET client_min_messages='log';
SET
# SELECT json_agg(i ORDER BY i DESC) FROM foo;
LOG: begin datum sort: workMem = 4096, randomAccess = f
LOG: performsort starting: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG: performsort done: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG: internal sort ended, 25 KB used: CPU 0.00s/0.00u sec elapsed 0.00 sec
json_agg
-----------
(3, 2, 1)
(1 row)
Une autre façon de voir que le tri fait partie du plan est d’utiliser l’option debug_print_plan, mais le résultat n’est pas pour les âmes sensibles. Plus précisément, vous devez rechercher le :aggorder champ ci-dessous :
# SET debug_print_plan=true;
SET
# SELECT json_agg(i ORDER BY i DESC) FROM foo;
LOG: plan:
DETAIL: {PLANNEDSTMT
:commandType 1
:queryId 0
:hasReturning false
:hasModifyingCTE false
:canSetTag true
:transientPlan false
:dependsOnRole false
:parallelModeNeeded false
:planTree
{AGG
:startup_cost 41.88
:total_cost 41.89
:plan_rows 1
:plan_width 32
:parallel_aware false
:plan_node_id 0
:targetlist (
{TARGETENTRY
:expr
{AGGREF
:aggfnoid 3175
:aggtype 114
:aggcollid 0
:inputcollid 0
:aggtranstype 2281
:aggargtypes (o 23)
:aggdirectargs <>
:args (
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 1
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 1
:location 16
}
:resno 1
:resname <>
:ressortgroupref 1
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
:aggorder (
{SORTGROUPCLAUSE
:tleSortGroupRef 1
:eqop 96
:sortop 521
:nulls_first true
:hashable true
}
)
:aggdistinct <>
:aggfilter <>
:aggstar false
:aggvariadic false
:aggkind n
:agglevelsup 0
:aggsplit 0
:location 7
}
:resno 1
:resname json_agg
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
:qual <>
:lefttree
{SEQSCAN
:startup_cost 0.00
:total_cost 35.50
:plan_rows 2550
:plan_width 4
:parallel_aware false
:plan_node_id 1
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 1
:location -1
}
:resno 1
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
:qual <>
:lefttree <>
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:scanrelid 1
}
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:aggstrategy 0
:aggsplit 0
:numCols 0
:grpColIdx
:grpOperators
:numGroups 1
:aggParams (b)
:groupingSets <>
:chain <>
}
:rtable (
{RTE
:alias <>
:eref
{ALIAS
:aliasname foo
:colnames ("i")
}
:rtekind 0
:relid 404407
:relkind r
:tablesample <>
:lateral false
:inh false
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 9)
:insertedCols (b)
:updatedCols (b)
:securityQuals <>
}
)
:resultRelations <>
:utilityStmt <>
:subplans <>
:rewindPlanIDs (b)
:rowMarks <>
:relationOids (o 404407)
:invalItems <>
:nParamExec 0
}
LOG: begin datum sort: workMem = 4096, randomAccess = f
LOG: performsort starting: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG: performsort done: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG: internal sort ended, 25 KB used: CPU 0.00s/0.00u sec elapsed 0.00 sec
json_agg
-----------
(3, 2, 1)
(1 row)
En conclusion : Tandis que EXPLAIN est une arme puissante pour ceux qui cherchent à comprendre les performances des requêtes, vous devez être conscient du fait qu’il y a des choses que vous ne pouvez pas EXPLAIN :).
La recherche pour cet article a été effectuée à l’aide de PostgreSQL 9.6.3 et LLDB, mais devrait également s’appliquer aux versions récentes ainsi qu’au prochain PostgreSQL 10.
# SELECT version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.3 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit
(1 row)
— Félix Geisendörfer
Abonnez-vous à ce blog via RSS ou E-Mail ou recevez de petites mises à jour de ma part via Gazouillement.
