Ecrire et optimiser une requête SQL

      Si vous devez écrire une requête, le plus important est d’obtenir le résultat souhaité. Les pires bugs sont évidemment les bugs "WRONG RESULTS" qui sont très rarement dus à une défaillance du SGBD et très souvent à une erreur dans notre code SQL. Pour éviter ces erreurs il faut connaître le modèle de données et avoir compris ce que doit ramener la requête. Le meilleur expert SQL du monde ne produira rien de bon sans un expert fonctionnel. Ensuite, si le résultat peut être obtenu rapidement, c’est évidemment un plus. Nous allons dérouler un exemple complet pour illustrer le processus d’écriture et d’optimisation d’une requête.

Le contexte

      Comme souvent dans les articles du site, le contexte est celui d’un clan de géants passant leur temps à lancer des cailloux de quelques centaines de kilogrammes. Le schéma comprend ainsi notamment 2 tables :

select version(); version ------------------------------------------------------------------------------------------ PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit (1 ligne) Temps : 0,417 ms postgres=# \d+ lancers Table public.lancers Colonne | Type | Modificateurs | Stockage | Cible de statistiques | Description ---------+-----------------------------+---------------+----------+-----------------------+------------- dtla | timestamp without time zone | non NULL | plain | | idge | integer | non NULL | plain | | perf | integer | | plain | | Index : "lancers_pkey" PRIMARY KEY, btree (dtla, idge) Contraintes de clés étrangères : "lancers_idge_fkey" FOREIGN KEY (idge) REFERENCES geants(idge) postgres=# \d+ geants Table public.geants Colonne | Type | Modificateurs | Stockage | Cible de statistiques | Description ---------+------------------------+---------------+----------+-----------------------+------------- idge | integer | non NULL | plain | | nmge | character varying(128) | | extended | | dnge | date | | plain | | actif | smallint | | plain | | sage | integer | | plain | | idge_ch | integer | | plain | | Index : "geants_pkey" PRIMARY KEY, btree (idge) Contraintes de clés étrangères : "geants_idge_ch_fkey" FOREIGN KEY (idge_ch) REFERENCES geants(idge) DEFERRABLE INITIALLY DEFERRED Référencé par : TABLE "geants" CONSTRAINT "geants_idge_ch_fkey" FOREIGN KEY (idge_ch) REFERENCES geants(idge) DEFERRABLE INITIALLY DEFERRED TABLE "lancers" CONSTRAINT "lancers_idge_fkey" FOREIGN KEY (idge) REFERENCES geants(idge) postgres=# \d+ Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+---------+-------+--------------+--------+------------- public | geants | table | postgres | 40 kB | public | lancers | table | postgres | 24 MB | (2 lignes) postgres=# \di+ Liste des relations Schéma | Nom | Type | Propriétaire | Table | Taille | Description --------+--------------+-------+--------------+---------+--------+------------- public | geants_pkey | index | postgres | geants | 16 kB | public | lancers_pkey | index | postgres | lancers | 17 MB | (2 lignes)

      La table géants fait 40kb, la table lancers fait 24mb. La clé de la table geants est idge, l’index unique associé fait 16kb. La clé de la table lancers est composée (dtla, idge) et l’index unique associé fait 17mb. Les tests sont exécutés sur un petit serveur disposant d’une version de PostgreSQL 9.5.4 configurée par défaut, 128Mb de mémoire partagée et 4Mb de mémoire de travail.

Le problème

      Il s’agit ici d’écrire une requête ramenant tous les jours des mois de mai-juin-juillet 2016 respectant les conditions suivantes : le Grand Chef (le géant n’ayant pas de chef) a lancé au moins 1 fois ET un géant a lancé à plus de 85000cm.
      Un développeur Java junior identifie 3 conditions pour qu’un jour soit retenu au niveau des résultats ;

      Il propose donc d’exprimer la condition d’intervalle directement et les autres conditions avec des clauses EXISTS, ce qui donne cette écriture :

EXPLAIN ANALYZE SELECT DISTINCT(DATE_TRUNC(’day’, dtla)) FROM lancers l WHERE l.dtla >= TO_TIMESTAMP(’01/05/2016’, ’DD/MM/YYYY’) AND l.dtla < TO_TIMESTAMP(’01/08/2016’, ’DD/MM/YYYY’) AND EXISTS (SELECT 1 FROM lancers l2 where DATE_TRUNC(’day’, l.dtla) = DATE_TRUNC(’day’, l2.dtla) AND l2.perf > 85000) AND EXISTS (SELECT 1 FROM lancers l3 where DATE_TRUNC(’day’, l.dtla) = DATE_TRUNC(’day’, l3.dtla) AND l3.idge IN (SELECT idge FROM geants WHERE idge_ch IS NULL)); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=32534.94..32951.80 rows=33349 width=8) (actual time=640.200..640.323 rows=4 loops=1) Group Key: date_trunc(’day’::text, l.dtla) -> Hash Semi Join (cost=20963.81..32451.56 rows=33349 width=8) (actual time=445.398..638.024 rows=5760 loops=1) Hash Cond: (date_trunc(’day’::text, l.dtla) = date_trunc(’day’::text, l3.dtla)) -> Hash Join (cost=10324.62..18715.08 rows=66698 width=16) (actual time=182.906..378.692 rows=7173 loops=1) Hash Cond: (date_trunc(’day’::text, l.dtla) = date_trunc(’day’::text, l2.dtla)) -> Index Only Scan using lancers_pkey on lancers l (cost=0.43..4749.21 rows=133395 width=8) (actual time=0.092..124.710 rows=132452 loops=1) Index Cond: ((dtla >= to_timestamp(’01/05/2016’::text, ’DD/MM/YYYY’::text)) AND (dtla < to_timestamp(’01/08/2016’::text, ’DD/MM/YYYY’::text))) Heap Fetches: 1638 -> Hash (cost=10318.92..10318.92 rows=421 width=8) (actual time=167.921..167.921 rows=21 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> HashAggregate (cost=10314.72..10318.92 rows=421 width=8) (actual time=167.866..167.877 rows=21 loops=1) Group Key: date_trunc(’day’::text, l2.dtla) -> Seq Scan on lancers l2 (cost=0.00..10313.66 rows=421 width=8) (actual time=6.004..167.769 rows=22 loops=1) Filter: (perf > 85000) Rows Removed by Filter: 575951 -> Hash (cost=10505.87..10505.87 rows=10666 width=8) (actual time=246.818..246.818 rows=10692 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 546kB -> Hash Semi Join (cost=1.55..10505.87 rows=10666 width=8) (actual time=0.088..236.792 rows=10692 loops=1) Hash Cond: (l3.idge = geants.idge) -> Seq Scan on lancers l3 (cost=0.00..8873.73 rows=575973 width=12) (actual time=0.010..90.396 rows=575973 loops=1) -> Hash (cost=1.54..1.54 rows=1 width=4) (actual time=0.046..0.046 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on geants (cost=0.00..1.54 rows=1 width=4) (actual time=0.007..0.025 rows=1 loops=1) Filter: (idge_ch IS NULL) Rows Removed by Filter: 53 Planning time: 1.010 ms Execution time: 641.516 ms (28 lignes)


       Hmmm...cela fonctionne mais 641ms ce n’est pas terrible du tout, l’utilisateur n’attend pas longtemps mais le délai de plus d’une demi-seconde est suffisant pour ne pas donner la sensation d’un résultat immédiat. D’autre part ce ne serait pas un temps acceptable pour une requête souvent exécutée car la charge occasionnée serait très importante. 2 balayages complets de lancers sont effectués donc les performances iraient en se dégradant avec l’augmentation de la taille de cette table au fil du temps. Il est sans doute possible de faire beaucoup mieux.

Une autre approche

      Un développeur C senior propose spontanément une solution différente. Son idée : chercher les jours entre le 1er mai et le 31 juillet pendant lesquels une performance de plus de 85000cm a été réalisée, les jours entre le 1er mai et le 31 juillet pendant lesquels le grand chef a lancé et croiser les résultats obtenus, c’est à dire déterminer l’intersection de 2 ensembles de données avec le mot clé INTERSECT. Cela donne cette écriture :

EXPLAIN ANALYZE SELECT DATE_TRUNC(’day’, dtla) FROM lancers WHERE perf > 85000 AND dtla >= TO_TIMESTAMP(’01/05/2016’, ’DD/MM/YYYY’) AND dtla < TO_TIMESTAMP(’01/08/2016’, ’DD/MM/YYYY’) INTERSECT SELECT DATE_TRUNC(’day’, dtla) FROM lancers WHERE idge = (SELECT idge FROM geants WHERE idge_ch IS NULL) AND dtla >= TO_TIMESTAMP(’01/05/2016’, ’DD/MM/YYYY’) AND dtla < TO_TIMESTAMP(’01/08/2016’, ’DD/MM/YYYY’) ; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ HashSetOp Intersect (cost=3394.40..13315.17 rows=98 width=8) (actual time=258.893..258.896 rows=4 loops=1) -> Append (cost=3394.40..13308.75 rows=2568 width=8) (actual time=115.225..258.303 rows=2424 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=3394.40..9511.02 rows=98 width=8) (actual time=115.225..153.668 rows=5 loops=1) -> Bitmap Heap Scan on lancers (cost=3394.40..9510.04 rows=98 width=8) (actual time=115.222..153.660 rows=5 loops=1) Recheck Cond: ((dtla >= to_timestamp(’01/05/2016’::text, ’DD/MM/YYYY’::text)) AND (dtla < to_timestamp(’01/08/2016’::text, ’DD/MM/YYYY’::text))) Filter: (perf > 85000) Rows Removed by Filter: 132447 Heap Blocks: exact=717 -> Bitmap Index Scan on lancers_pkey (cost=0.00..3394.38 rows=133395 width=0) (actual time=111.746..111.746 rows=132452 loops=1) Index Cond: ((dtla >= to_timestamp(’01/05/2016’::text, ’DD/MM/YYYY’::text)) AND (dtla < to_timestamp(’01/08/2016’::text, ’DD/MM/YYYY’::text))) -> Subquery Scan on "*SELECT* 2" (cost=1.97..3797.74 rows=2470 width=8) (actual time=0.351..104.352 rows=2419 loops=1) -> Index Only Scan using lancers_pkey on lancers lancers_1 (cost=1.97..3773.04 rows=2470 width=8) (actual time=0.351..103.793 rows=2419 loops=1) Index Cond: ((dtla >= to_timestamp(’01/05/2016’::text, ’DD/MM/YYYY’::text)) AND (dtla < to_timestamp(’01/08/2016’::text, ’DD/MM/YYYY’::text)) AND (idge = $0)) Heap Fetches: 5 InitPlan 1 (returns $0) -> Seq Scan on geants (cost=0.00..1.54 rows=1 width=4) (actual time=0.009..0.026 rows=1 loops=1) Filter: (idge_ch IS NULL) Rows Removed by Filter: 53 Planning time: 0.523 ms Execution time: 259.174 ms (20 lignes)


      Les performances sont bien meilleures. Nous sommes passés de 641ms à 259ms. La requête peut être considérée comme utilisable en production. Elle est lisible et aucune fonction n’est appliquée sur les colonnes utilisées pour filtrer les résultats. Cela laisse présager de performances encore meilleures avec une indexation adaptée qui ne nécessitera pas la création d’expression-based ou function-based index. Ces index peuvent être créés avec Oracle comme PostgreSQL mais autant les éviter lorsqu’ils ne sont pas indispensables. Tout index supplémentaire prend en effet de la place, entraîne une charge lors des opérations d’update, delete, insert etc.

Connaissance fonctionnelle avancée et utilisation des normes SQL post SQL92

      Les SGBD modernes peuvent beaucoup puisqu’ils choisissent le meilleur plan d’exécution en fonction de statistiques sur les données. Le rôle des êtres humains est cependant toujours important. Le DBA est souvent sollicité pour des problèmes de performance mais il n’est pas le mieux placé pour optimiser seul les requêtes car il n’a souvent qu’une connaissance fonctionnelle réduite. Certes, il est possible d’interroger les tables pour connaître ce qui est stocké mais le développeur connaissant à la fois l’applicatif et le modèle de données sera bien plus efficace. S’il connait en plus la technique SQL il est autonome. Le rôle du DBA se limite alors à dimensionner la mémoire, à sauvegarder les données etc.

      Ici le modèle de données est simple et nous avons quelques informations intéressantes diverses. Le clan ne peut avoir qu’un seul grand chef. Nous savons aussi qu’une performance de plus de 85000cm est exceptionnelle pour les géants mais qu’en revanche le grand chef lance quasi tous les jours. Une idée serait donc de déterminer d’abord tous les jours entre le 1er mai et le 31 juillet pendant lesquels une performance de plus de 85000cm a été réalisée PUIS de vérifier pour chacun de ces jours si le grand chef a lancé.

      Nous allons coder cela en utilisant une fonctionnalité introduite par la norme SQL99, la jointure latérale. Ce type de jointure est disponible avec Oracle Database depuis la version 12.1 et avec PostgreSQL depuis la version 9.3. Une jointure effectuée avec JOIN LATERAL permet de réaliser un "FOR EACH". JOIN LATERAL permet de faire référence à l’intérieur d’une sous-requête utilisée en membre d’une jointure à une colonne de l’autre table de la jointure, ce qui est impossible avec un JOIN classique. Dans la 1ère partie de la jointure nous allons donc avoir les jours de performance exceptionnelle et dans la 2ème partie nous allons chercher si le grand chef a lancé. Cette 2ème partie comprendra un FETCH FIRST 1 ROW ONLY puisqu’il suffit que le grand chef ait lancé une fois pour que le jour soit retenu. FETCH FIRST N ROW ONLY est le standard défini par la norme SQL2008 pour limiter le jeu de résultats. Cette syntaxe est disponible sous Oracle Database en remplacement de ROWNUM depuis la version 12.1 et avec PostgreSQL en remplacement de LIMIT depuis la version 8.4. Cela donne cette écriture :

EXPLAIN ANALYZE WITH jours_de_perf(jour) AS (SELECT DATE_TRUNC(’day’, dtla) FROM lancers WHERE perf > 85000 AND dtla >= TO_TIMESTAMP(’01/05/2016’, ’DD/MM/YYYY’) AND dtla < TO_TIMESTAMP(’01/08/2016’, ’DD/MM/YYYY’) GROUP BY DATE_TRUNC(’day’, dtla)) SELECT jour FROM jours_de_perf JOIN LATERAL (SELECT 1 FROM lancers WHERE dtla >= jour AND dtla < jour + INTERVAL ’1 DAY’ AND idge = (SELECT idge FROM geants WHERE idge_ch IS NULL) FETCH FIRST 1 ROW ONLY) l ON (TRUE); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=9513.47..9871.94 rows=98 width=8) (actual time=156.105..156.512 rows=4 loops=1) CTE jours_de_perf -> HashAggregate (cost=9510.28..9511.51 rows=98 width=8) (actual time=155.912..155.917 rows=5 loops=1) Group Key: date_trunc(’day’::text, lancers_1.dtla) -> Bitmap Heap Scan on lancers lancers_1 (cost=3394.40..9510.04 rows=98 width=8) (actual time=116.777..155.883 rows=5 loops=1) Recheck Cond: ((dtla >= to_timestamp(’01/05/2016’::text, ’DD/MM/YYYY’::text)) AND (dtla < to_timestamp(’01/08/2016’::text, ’DD/MM/YYYY’::text))) Filter: (perf > 85000) Rows Removed by Filter: 132447 Heap Blocks: exact=717 -> Bitmap Index Scan on lancers_pkey (cost=0.00..3394.38 rows=133395 width=0) (actual time=113.058..113.058 rows=132452 loops=1) Index Cond: ((dtla >= to_timestamp(’01/05/2016’::text, ’DD/MM/YYYY’::text)) AND (dtla < to_timestamp(’01/08/2016’::text, ’DD/MM/YYYY’::text))) -> CTE Scan on jours_de_perf (cost=0.00..1.96 rows=98 width=8) (actual time=155.918..155.928 rows=5 loops=1) -> Limit (cost=1.97..3.64 rows=1 width=0) (actual time=0.114..0.114 rows=1 loops=5) InitPlan 2 (returns $1) -> Seq Scan on geants (cost=0.00..1.54 rows=1 width=4) (actual time=0.008..0.024 rows=1 loops=1) Filter: (idge_ch IS NULL) Rows Removed by Filter: 53 -> Index Only Scan using lancers_pkey on lancers (cost=0.43..88.96 rows=53 width=0) (actual time=0.101..0.101 rows=1 loops=5) Index Cond: ((dtla >= jours_de_perf.jour) AND (dtla < (jours_de_perf.jour + ’1 day’::interval)) AND (idge = $1)) Heap Fetches: 0 Planning time: 0.635 ms Execution time: 156.866 ms (22 lignes)


       Bonne pioche ! Nous sommes partis de 641ms. Nous avons ensuite obtenu autour de 250ms et à présent c’est 157ms. C’est de mieux en mieux mais il est temps à présent de se demander si l’indexation est adaptée. Les requêtes semblent à présent OK mais la charge associée est toujours élevée et les temps de réponse encore perfectibles.

Indexer un schéma pour la performance

      Une des recommandations en ce qui concerne les index composés est d’indexer d’abord pour l’égalité, puis pour les intervalles. Ici nous remarquons que nous effectuons souvent des recherches utilisant = sur idge (un identifiant) et >= , < pour dtla (une date). Or, la clé primaire et donc l’index portent sur (dtla, idge). Il est également recommandé d’indexer les clés étrangères dans beaucoup de cas donc nous décidons de renverser l’ordre des colonnes au niveau de la clé primaire de lancers et donc de l’index unique associé qui portera sur (idge, dtla).
      Afin de ne pas défavoriser les recherches portant exclusivement sur dtla, nous allons ajouter un index de type "BLOCK RANGE" sur cette colonne (si de telles requêtes se généralisent nous recréerons un B-Tree). Enfin, la table lancers étant essentiellemnt interrogée pour déterminer les meilleures performances, nous allons indexer (perf, dtla) :

ALTER TABLE lancers DROP CONSTRAINT lancers_pkey; ALTER TABLE lancers ADD PRIMARY KEY(idge, dtla); CREATE INDEX lancers_br1 ON lancers USING BRIN(dtla); CREATE INDEX lancers_i1 ON lancers(perf, dtla); postgres=# \di+ Liste des relations Schéma | Nom | Type | Propriétaire | Table | Taille | Description --------+--------------+-------+--------------+---------+--------+------------- public | geants_pkey | index | postgres | geants | 16 kB | public | lancers_br1 | index | postgres | lancers | 48 kB | public | lancers_i1 | index | postgres | lancers | 17 MB | public | lancers_pkey | index | postgres | lancers | 17 MB | (4 lignes)


      L’index lancers_br1 ne fait que 48ko. Il sera moins efficace qu’un index B-Tree mais ajouter un index BRIN n’a quasi aucun impact sur la volumétrie. Evaluons à présent l’impact de la nouvelle indexation sur la requête aux 2 EXISTS :

EXPLAIN ANALYZE SELECT DISTINCT(DATE_TRUNC(’day’, dtla)) FROM lancers l WHERE l.dtla >= TO_TIMESTAMP(’01/05/2016’, ’DD/MM/YYYY’) AND l.dtla < TO_TIMESTAMP(’01/08/2016’, ’DD/MM/YYYY’) AND EXISTS (SELECT 1 FROM lancers l2 where DATE_TRUNC(’day’, l.dtla) = DATE_TRUNC(’day’, l2.dtla) AND l2.perf > 85000) AND EXISTS (SELECT 1 FROM lancers l3 where DATE_TRUNC(’day’, l.dtla) = DATE_TRUNC(’day’, l3.dtla) AND l3.idge IN (SELECT idge FROM geants WHERE idge_ch IS NULL)); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=24944.33..25361.19 rows=33349 width=8) (actual time=1168.102..1168.222 rows=4 loops=1) Group Key: date_trunc(’day’::text, l.dtla) -> Hash Semi Join (cost=12340.09..24860.96 rows=33349 width=8) (actual time=317.751..1165.609 rows=5760 loops=1) Hash Cond: (date_trunc(’day’::text, l.dtla) = date_trunc(’day’::text, l3.dtla)) -> Hash Join (cost=11703.49..21127.07 rows=66698 width=16) (actual time=252.068..1135.807 rows=7173 loops=1) Hash Cond: (date_trunc(’day’::text, l.dtla) = date_trunc(’day’::text, l2.dtla)) -> Bitmap Heap Scan on lancers l (cost=1379.30..7161.20 rows=133395 width=8) (actual time=32.780..871.172 rows=132452 loops=1) Recheck Cond: ((dtla >= to_timestamp(’01/05/2016’::text, ’DD/MM/YYYY’::text)) AND (dtla < to_timestamp(’01/08/2016’::text, ’DD/MM/YYYY’::text))) Rows Removed by Index Recheck: 41046 Heap Blocks: lossy=938 -> Bitmap Index Scan on lancers_br1 (cost=0.00..1345.96 rows=133395 width=0) (actual time=0.341..0.341 rows=10240 loops=1) Index Cond: ((dtla >= to_timestamp(’01/05/2016’::text, ’DD/MM/YYYY’::text)) AND (dtla < to_timestamp(’01/08/2016’::text, ’DD/MM/YYYY’::text))) -> Hash (cost=10318.92..10318.92 rows=421 width=8) (actual time=162.823..162.823 rows=21 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> HashAggregate (cost=10314.72..10318.92 rows=421 width=8) (actual time=162.721..162.732 rows=21 loops=1) Group Key: date_trunc(’day’::text, l2.dtla) -> Seq Scan on lancers l2 (cost=0.00..10313.66 rows=421 width=8) (actual time=5.737..162.653 rows=22 loops=1) Filter: (perf > 85000) Rows Removed by Filter: 575951 -> Hash (cost=503.27..503.27 rows=10666 width=8) (actual time=16.142..16.142 rows=10692 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 546kB -> Nested Loop (cost=0.42..503.27 rows=10666 width=8) (actual time=0.078..6.593 rows=10692 loops=1) -> Seq Scan on geants (cost=0.00..1.54 rows=1 width=4) (actual time=0.009..0.030 rows=1 loops=1) Filter: (idge_ch IS NULL) Rows Removed by Filter: 53 -> Index Only Scan using lancers_pkey on lancers l3 (cost=0.42..395.07 rows=10666 width=12) (actual time=0.059..4.238 rows=10692 loops=1) Index Cond: (idge = geants.idge) Heap Fetches: 5 Planning time: 1.037 ms Execution time: 1169.464 ms (30 lignes)


      La perte de l’indexation B-Tree sur dtla est partiellement compensée par l’indexation BRIN sur cette colonne. Sans l’index BRIN nous passerions en effet à plus de 2400ms :

EXPLAIN ANALYZE SELECT DISTINCT(DATE_TRUNC(’day’, dtla)) FROM lancers l WHERE l.dtla >= TO_TIMESTAMP(’01/05/2016’, ’DD/MM/YYYY’) AND l.dtla < TO_TIMESTAMP(’01/08/2016’, ’DD/MM/YYYY’) AND EXISTS (SELECT 1 FROM lancers l2 where DATE_TRUNC(’day’, l.dtla) = DATE_TRUNC(’day’, l2.dtla) AND l2.perf > 85000) AND EXISTS (SELECT 1 FROM lancers l3 where DATE_TRUNC(’day’, l.dtla) = DATE_TRUNC(’day’, l3.dtla) AND l3.idge IN (SELECT idge FROM geants WHERE idge_ch IS NULL)); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=32416.59..32833.45 rows=33349 width=8) (actual time=2420.495..2420.645 rows=4 loops=1) Group Key: date_trunc(’day’::text, l.dtla) -> Hash Semi Join (cost=10960.79..32333.21 rows=33349 width=8) (actual time=1250.272..2417.662 rows=5760 loops=1) Hash Cond: (date_trunc(’day’::text, l.dtla) = date_trunc(’day’::text, l3.dtla)) -> Hash Join (cost=10324.19..28599.33 rows=66698 width=16) (actual time=1184.398..2385.116 rows=7173 loops=1) Hash Cond: (date_trunc(’day’::text, l.dtla) = date_trunc(’day’::text, l2.dtla)) -> Seq Scan on lancers l (cost=0.00..14633.46 rows=133395 width=8) (actual time=965.242..2122.034 rows=132452 loops=1) Filter: ((dtla >= to_timestamp(’01/05/2016’::text, ’DD/MM/YYYY’::text)) AND (dtla < to_timestamp(’01/08/2016’::text, ’DD/MM/YYYY’::text))) Rows Removed by Filter: 443521 -> Hash (cost=10318.92..10318.92 rows=421 width=8) (actual time=163.431..163.431 rows=21 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> HashAggregate (cost=10314.72..10318.92 rows=421 width=8) (actual time=163.378..163.390 rows=21 loops=1) Group Key: date_trunc(’day’::text, l2.dtla) -> Seq Scan on lancers l2 (cost=0.00..10313.66 rows=421 width=8) (actual time=5.774..163.325 rows=22 loops=1) Filter: (perf > 85000) Rows Removed by Filter: 575951 -> Hash (cost=503.27..503.27 rows=10666 width=8) (actual time=16.830..16.830 rows=10692 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 546kB -> Nested Loop (cost=0.42..503.27 rows=10666 width=8) (actual time=0.069..6.537 rows=10692 loops=1) -> Seq Scan on geants (cost=0.00..1.54 rows=1 width=4) (actual time=0.010..0.030 rows=1 loops=1) Filter: (idge_ch IS NULL) Rows Removed by Filter: 53 -> Index Only Scan using lancers_pkey on lancers l3 (cost=0.42..395.07 rows=10666 width=12) (actual time=0.050..4.269 rows=10692 loops=1) Index Cond: (idge = geants.idge) Heap Fetches: 5 Planning time: 1.009 ms Execution time: 2422.046 ms


      Dans tous les cas ce n’est pas bon du tout concernant cette requête, inutile de faire davantage pour la sauver. Pour info il serait possible de faire descendre le temps un peu sous les 300ms en recréant un index B-Tree sur dtla sans changer la requête. Il serait surtout possible d’obtenir de meilleures performances en conservant des clauses EXISTS mais en modifiant la requête. Toutefois ce n’est pas l’objet de l’article puisque nous avons déjà 2 autres écritures satisfaisantes.
      Quel est l’impact de la nouvelle indexation sur la requête avec INTERSECT à présent ?

EXPLAIN ANALYZE SELECT DATE_TRUNC(’day’, dtla) FROM lancers WHERE perf > 85000 AND dtla >= TO_TIMESTAMP(’01/05/2016’, ’DD/MM/YYYY’) AND dtla < TO_TIMESTAMP(’01/08/2016’, ’DD/MM/YYYY’) INTERSECT SELECT DATE_TRUNC(’day’, dtla) FROM lancers WHERE idge = (SELECT idge FROM geants WHERE idge_ch IS NULL) AND dtla >= TO_TIMESTAMP(’01/05/2016’, ’DD/MM/YYYY’) AND dtla < TO_TIMESTAMP(’01/08/2016’, ’DD/MM/YYYY’) ; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashSetOp Intersect (cost=0.43..214.27 rows=345 width=8) (actual time=5.708..5.710 rows=4 loops=1) -> Append (cost=0.43..207.23 rows=2815 width=8) (actual time=0.065..5.136 rows=2424 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=0.43..54.82 rows=345 width=8) (actual time=0.065..0.073 rows=5 loops=1) -> Index Only Scan using lancers_i1 on lancers (cost=0.43..51.37 rows=345 width=8) (actual time=0.062..0.070 rows=5 loops=1) Index Cond: ((perf > 85000) AND (dtla >= to_timestamp(’01/05/2016’::text, ’DD/MM/YYYY’::text)) AND (dtla < to_timestamp(’01/08/2016’::text, ’DD/MM/YYYY’::text))) Heap Fetches: 1 -> Subquery Scan on "*SELECT* 2" (cost=1.97..152.42 rows=2470 width=8) (actual time=0.258..4.815 rows=2419 loops=1) -> Index Only Scan using lancers_pkey on lancers lancers_1 (cost=1.97..127.72 rows=2470 width=8) (actual time=0.257..4.229 rows=2419 loops=1) Index Cond: ((idge = $0) AND (dtla >= to_timestamp(’01/05/2016’::text, ’DD/MM/YYYY’::text)) AND (dtla < to_timestamp(’01/08/2016’::text, ’DD/MM/YYYY’::text))) Heap Fetches: 5 InitPlan 1 (returns $0) -> Seq Scan on geants (cost=0.00..1.54 rows=1 width=4) (actual time=0.009..0.027 rows=1 loops=1) Filter: (idge_ch IS NULL) Rows Removed by Filter: 53 Planning time: 0.750 ms Execution time: 5.909 ms (16 lignes)


      5,9ms à l’exécution ! C’est très bon, les utilisateurs ont une sensation de réactivité du système. Il serait possible de s’arrêter là, nous avons ici des performances OK pour de la production transactionnelle. Cependant par curiosité quel est l’impact sur la requête avec JOIN LATERAL ?

EXPLAIN ANALYZE WITH jours_de_perf(jour) AS (SELECT DATE_TRUNC(’day’, dtla) FROM lancers WHERE perf > 85000 AND dtla >= TO_TIMESTAMP(’01/05/2016’, ’DD/MM/YYYY’) AND dtla < TO_TIMESTAMP(’01/08/2016’, ’DD/MM/YYYY’) GROUP BY DATE_TRUNC(’day’, dtla)) SELECT jour FROM jours_de_perf JOIN LATERAL (SELECT 1 FROM lancers WHERE dtla >= jour AND dtla < jour + INTERVAL ’1 DAY’ AND idge = (SELECT idge FROM geants WHERE idge_ch IS NULL) FETCH FIRST 1 ROW ONLY) l ON (TRUE); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=58.51..808.97 rows=345 width=8) (actual time=0.240..0.301 rows=4 loops=1) CTE jours_de_perf -> HashAggregate (cost=52.23..56.54 rows=345 width=8) (actual time=0.128..0.132 rows=5 loops=1) Group Key: date_trunc(’day’::text, lancers_1.dtla) -> Index Only Scan using lancers_i1 on lancers lancers_1 (cost=0.43..51.37 rows=345 width=8) (actual time=0.110..0.116 rows=5 loops=1) Index Cond: ((perf > 85000) AND (dtla >= to_timestamp(’01/05/2016’::text, ’DD/MM/YYYY’::text)) AND (dtla < to_timestamp(’01/08/2016’::text, ’DD/MM/YYYY’::text))) Heap Fetches: 1 -> CTE Scan on jours_de_perf (cost=0.00..6.90 rows=345 width=8) (actual time=0.131..0.140 rows=5 loops=1) -> Limit (cost=1.97..2.14 rows=1 width=0) (actual time=0.031..0.031 rows=1 loops=5) InitPlan 2 (returns $1) -> Seq Scan on geants (cost=0.00..1.54 rows=1 width=4) (actual time=0.008..0.025 rows=1 loops=1) Filter: (idge_ch IS NULL) Rows Removed by Filter: 53 -> Index Only Scan using lancers_pkey on lancers (cost=0.43..9.62 rows=53 width=0) (actual time=0.013..0.013 rows=1 loops=5) Index Cond: ((idge = $1) AND (dtla >= jours_de_perf.jour) AND (dtla < (jours_de_perf.jour + ’1 day’::interval))) Heap Fetches: 0 Planning time: 0.887 ms Execution time: 0.493 ms (18 lignes)

      Moins de 0,5ms ... c’est environ 12 fois mieux qu’avec la requête basée sur INTERSECT. La conjugaison optimale au niveau de l’écriture de la requête et de l’indexation donne logiquement le meilleur résultat. Il faut ici noter que la phase d’exécution est à présent plus rapide que la phase de planification de la requête qui est de 0,9ms. Si cette requête est souvent exécutée il va falloir songer à avoir UNE planification pour N exécutions mais c’est une autre histoire.
      De 641,516ms à 0,494ms nous avons divisé le temps d’exécution par 1298 au fil du travail effectué. L’évolution de la puissance des systèmes et des capacités des SGBD ne peuvent pas toujours compenser d’énormes erreurs au niveau du code SQL ou du design applicatif . Une requête est raisonnablement optimisée pour une utilisation fréquente lorsque son temps d’exécution est jugé acceptable par l’utilisateur ET ne dépend que faiblement de l’évolution de la volumétrie.

Mise à jour : 30/03/2017