Bonjour tout le monde !

Aujourd'hui, j'ai une demande concernant PostgreSQL que je ne connais pas trop, mais dont je constate que c'est bien différent de MySQL avec lequel j'ai l'habitude.

Voici une requête et le nombre d'enregistrements dans chacune des tables concernées.

SELECT
				count(t1."Id") AS Number
	FROM
				"my_schema"."table1" t1
		INNER JOIN
				"my_schema"."table2" t2
			ON
					t1."table2_id" = t2."Id"
				AND	t2."IsDeleted" = false
		INNER JOIN
				"my_schema"."table3" t3
			ON
					t2."table3_id" = t3."Id"
				AND	t3."IsDeleted" = false
		INNER JOIN
				"my_schema"."table4" t4
			ON
					t2."table4_id" = t4."Id"
				AND	t4."IsDeleted" = false
		INNER JOIN "my_schema"."table5" t5
			ON
					t4."table5_id" = t5."Id"
				AND	t5."IsDeleted" = false
		INNER JOIN
				"my_schema"."table6" t6
			ON
					t2."table6_id" = t6."Id"
				AND	t6."IsDeleted" = false
		INNER JOIN
				"my_schema"."table7" t7
			ON
					t1."table7_id" = t7. "Id"
				AND	t7."IsDeleted" = false
				AND	t7."IsActive" = true
		INNER JOIN
				"my_schema"."table8" t8
			ON
					t1."table8_id" = t8."Id"
				AND	t8."IsDeleted" = false
				AND	t8."un champ texte" = 'du texte'
				AND	t8."un champ date" < ?
	WHERE
			(t1."un champ texte" IN ('un texte', 'un autre texte'))
		AND	(t1."IsDeleted" = false)

-- table1 : 2'600'000 lignes
-- table2 : 1'800'000 lignes
-- table3 : 1'600'000 lignes
-- table4 :   205'000 lignes
-- table5 :   200'000 lignes
-- table6 : 1'005'000 lignes
-- table7 :       500 lignes
-- table8 :        10 lignes

La requête prend actuellement une vingtaine de secondes à retourner le résultat.

J'ai tenté de ne conserver que les jointures sur les tables 7 et 8, je descends à 6 secondes.

Je ne suis donc pas familier de PostgreSQL, ni des performances qu'on pourrait attendre de ce genre de requête avec ce volume de données. La seule chose que je me permets d'extrapoler est que c'est une simple requête qui compte et que, ayant en vue la même (cette fois obligatoirement avec toutes les tables) mais pour sélectionner des éléments, le temps de requête risque d'être plus conséquent, et ça me semble un peu long quand même.

J'ai mis des index sur les colonnes de clés étrangères (je ne peux pas mettre une vraie contrainte, la table est de toute manière en lecture seule et alimentée sans prendre cela en compte), mais apparemment cela n'impacte pas vraiment le temps de requête — je n'ai pas mesuré avant/après, je pensais qu'il y aurait de toute manière amélioration.

Du peu que j'ai compris, mettre des index à tort et à travers peut se révéler contre-productif, et j'en ai peut-être déjà mis là où il n'y aurait pas nécessairement besoin.

J'ai pris le temps d'utilser EXPLAIN ANALYSE, mais je ne saurais même pas comment utiliser le résultat de l'équivalent MySQL, donc ça ne m'avance malheureusement pas trop.

Du coup, trois questions déjà :

• au vu du volume de données, est-ce que 20 secondes est quelque chose de normal ?
• si non, est-ce que d'ajouter des index précis serait pertinent ?
  Le cas échéant, lesquels ?

Merci d'avance

Bonjour,

Quelles sont les cardinalités des colonnes en jeu ?

Où sont les index actuellement ?

Sur les colonnes booléennes en as-tu mis ? Je pense que cela peut valoir le coup.

Même six secondes c'est beaucoup avec cette volumétrie, tu peux optimiser tes index, surtout si tu as peux de sollicitations en écriture. 

-
Edité par Benzouye 25 mars 2020 à 10:37:08

Qu'entends-tu exactement par « cardinalités des colonnes en jeux » ?
J'aurais envie de répondre ceci :

- table1 [1, 1] <--> [0, n] table2
- table2 [0, 1] <--> [1, n] table3
- table2 [0, 1] <--> [0, n] table4
- table4 [1, 1] <--> [1, n] table5
- table2 [1, 1] <--> [0, n] table6
- table1 [1, 1] <--> [0, n] table7
- table1 [1, 1] <--> [0, n] table8

J'ai des index sur les colonnes contenant les IDs étrangers et j'ai tenté d'ajouter des index sur les paires (IsDeleted, Id) — dans cet ordre précisément, en pensant que ça pourrait aider sur les jointures, mais apparemment cela ne change pas grand chose. Tu préconiserais de mettre deux index séparés ?
Je n'ai pas mis d'index sur table7."IsActive", je me disais que vu le volume, le gain ne serait pas le plus pertinent.

Peux-tu retirer tous les index ( sauf clé primaire et clé étrangère), exécuter ta requête entière avec EXPLAIN et nous poster le retour du plan d'exécution ?

Est-ce que cela te convient ?

Malheureusement je ne peux pas retirer les index sur les paires de colonnes aussi facilement, donc là c'est avec eux. Je reviendrai quand ç'aura été possible de les enlever.

QUERY PLAN
Aggregate  (cost=813052.50..813052.51 rows=1 width=8) (actual time=42260.536..42260.536 rows=1 loops=1)
  ->  Hash Join  (cost=695828.57..812888.12 rows=65753 width=19) (actual time=36346.635..42206.032 rows=8103 loops=1)
        Hash Cond: (t4.table5_id = t5."Id")
        ->  Gather  (cost=667416.88..782079.83 rows=65753 width=38) (actual time=36197.798..42041.087 rows=8103 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              ->  Parallel Hash Join  (cost=666416.88..774504.53 rows=27397 width=38) (actual time=36189.790..42021.693 rows=2701 loops=3)
                    Hash Cond: (t6."Id" = t2."table6_id")
                    ->  Parallel Seq Scan on "table6" t6  (cost=0.00..103592.98 rows=438052 width=19) (actual time=0.803..5745.585 rows=350648 loops=3)
                          Filter: (NOT "IsDeleted")
                          Rows Removed by Filter: 144
                    ->  Parallel Hash  (cost=666074.15..666074.15 rows=27419 width=57) (actual time=36185.834..36185.835 rows=2701 loops=3)
                          Buckets: 131072  Batches: 1  Memory Usage: 1824kB
                          ->  Parallel Hash Join  (cost=542702.82..666074.15 rows=27419 width=57) (actual time=29354.655..36184.251 rows=2701 loops=3)
                                Hash Cond: (t3."Id" = t2."table3_id")
                                ->  Parallel Seq Scan on "table3" t3  (cost=0.00..120801.42 rows=654842 width=19) (actual time=3.044..6922.156 rows=523790 loops=3)
                                      Filter: (NOT "IsDeleted")
                                ->  Parallel Hash  (cost=542359.22..542359.22 rows=27488 width=76) (actual time=29132.287..29132.287 rows=2702 loops=3)
                                      Buckets: 131072  Batches: 1  Memory Usage: 1952kB
                                      ->  Parallel Hash Join  (cost=324241.05..542359.22 rows=27488 width=76) (actual time=29098.498..29124.315 rows=2702 loops=3)
                                            Hash Cond: (t1."table2_id" = t2."Id")
                                            ->  Hash Join  (cost=65.65..213276.39 rows=171706 width=38) (actual time=585.173..11131.754 rows=2745 loops=3)
                                                  Hash Cond: (t1."table7_id" = t7."Id")
                                                  ->  Hash Join  (cost=2.23..212711.12 rows=189536 width=57) (actual time=584.744..11129.906 rows=2746 loops=3)
                                                        Hash Cond: (t1."table8_id" = t8."Id")
                                                        ->  Parallel Seq Scan on "table1" t1  (cost=0.00..211513.88 rows=321367 width=76) (actual time=0.473..11090.814 rows=223491 loops=3)
                                                              Filter: ((NOT "IsDeleted") AND ("un champ texte" = ANY ('{un texte,un autre texte}'::text[])))
                                                              Rows Removed by Filter: 660537
                                                        ->  Hash  (cost=2.15..2.15 rows=6 width=19) (actual time=0.021..0.022 rows=6 loops=3)
                                                              Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                              ->  Seq Scan on "table8" t8  (cost=0.00..2.15 rows=6 width=19) (actual time=0.014..0.018 rows=6 loops=3)
                                                                    Filter: ((NOT "IsDeleted") AND ("un champ date" < 'une date'::timestamp with time zone) AND ("un champ texte" = 'du texte'::text))
                                                                    Rows Removed by Filter: 4
                                                  ->  Hash  (cost=57.89..57.89 rows=443 width=19) (actual time=0.341..0.341 rows=445 loops=3)
                                                        Buckets: 1024  Batches: 1  Memory Usage: 31kB
                                                        ->  Seq Scan on "table7" t7  (cost=0.00..57.89 rows=443 width=19) (actual time=0.016..0.241 rows=445 loops=3)
                                                              Filter: ((NOT "IsDeleted") AND "IsActive")
                                                              Rows Removed by Filter: 44
                                            ->  Parallel Hash  (cost=321268.47..321268.47 rows=115354 width=76) (actual time=17959.213..17959.213 rows=91191 loops=3)
                                                  Buckets: 65536  Batches: 16  Memory Usage: 2432kB
                                                  ->  Parallel Hash Join  (cost=10220.35..321268.47 rows=115354 width=76) (actual time=17812.088..17908.471 rows=91191 loops=3)
                                                        Hash Cond: (t2."table4_id" = t4."Id")
                                                        ->  Parallel Seq Scan on "table2" t2  (cost=0.00..290275.39 rows=717225 width=76) (actual time=4.948..17553.217 rows=572374 loops=3)
                                                              Filter: (NOT "IsDeleted")
                                                              Rows Removed by Filter: 2560
                                                        ->  Parallel Hash  (cost=8458.27..8458.27 rows=86727 width=38) (actual time=145.822..145.822 rows=69211 loops=3)
                                                              Buckets: 65536  Batches: 8  Memory Usage: 2368kB
                                                              ->  Parallel Seq Scan on "table4" t4  (cost=0.00..8458.27 rows=86727 width=38) (actual time=0.006..99.926 rows=69211 loops=3)
                                                                    Filter: (NOT "IsDeleted")
        ->  Hash  (cost=24665.86..24665.86 rows=203986 width=19) (actual time=147.653..147.654 rows=203187 loops=1)
              Buckets: 65536  Batches: 4  Memory Usage: 3050kB
              ->  Seq Scan on "table5" t5  (cost=0.00..24665.86 rows=203986 width=19) (actual time=0.011..97.962 rows=203187 loops=1)
                    Filter: (NOT "IsDeleted")
                    Rows Removed by Filter: 1
Planning Time: 14.880 ms
Execution Time: 42273.191 ms

-
Edité par Ymox 25 mars 2020 à 12:39:36

Ce qui me paraît bizarre, c'est qu'il n'y a aucune référence à d'éventuels index ... comme si aucun des index n'étaient utilisés pour cette requête ...

T'est-il possible de nous faire un dump SQL de ta base en l'état (au moins les CREATE TABLE des 8 tables en jeu) ?

Ymox a écrit:

je ne peux pas retirer les index sur les paires de colonnes aussi facilement,

Cela est chelou aussi ... Tu as un erreur ? Le DROP INDEX ne fonctionne pas ?

Benzouye a écrit:

T'est-il possible de nous faire un dump SQL de ta base en l'état (au moins les CREATE TABLE des 8 tables en jeu) ?

Je peux probablement fournir une structure anonymisée et épurée, mais pas de données ni la liste complète des colonnes. En revanche, je peux en fournir le nombre pour chaque table.

Benzouye a écrit:

Cela est chelou aussi ...

Pas tant que ça, c'est une table gérée par quelqu'un d'autre, je n'y ai qu'un accès limité, apparemment qu'en lecture. Donc je passe par quelqu'un d'autre pour manipuler ces index.

Edit

En attendant, j'aimerais préciser que les IDs sont des chaînes de 18 caractères, et non des entiers, et apparemment toutes dans des champs text.

-
Edité par Ymox 26 mars 2020 à 12:14:16

Ne t'embêtes pas avec le dump, ce n'est pas grave.

Les clés primaires en TEXT ... pas glop ... ça peut en effet flinguer les temps de traitement vu les volumes ...

Dans un premier temps les passer en CHAR(18) est-il envisageable ? Dans un second temps, tout reprendre avec des séquences ...

Je te conseille quand même la lecture du site use-the-index pour approfondir l'usage des index et leur optimisation.

-
Edité par Benzouye 26 mars 2020 à 16:27:24

Alors :

• passer en CHAR(18) n'est pas possible ;
• mettre des index sur les booléens ne semble pas impacter grand chose ;
• histoire de vraiment tenter, j'ai mis des index comme je l'aurais fait avec MySQL. Résultat : on est plutôt autour des 25 secondes.

Entre temps, je me suis rendu compte qu'il me fallait un DISTINCT dans l'histoire, je sais que ça coûte aussi.

Donc je vais prendre le temps de bien regarder le site que tu as mentionné et voir si je trouve d'autres pistes.

Ymox a écrit:

• passer en CHAR(18) n'est pas possible ;

Aïe ... c'est chaud de maintenir cette conception ... dette technique quand tu nous tiens

Ymox a écrit:

• mettre des index sur les booléens ne semble pas impacter grand chose ;

En fait c'est normal, sur un booléen tu as une cardinalité de 2 donc peu d'intérêt sur l'index ...

Après, c'est fou que ce soit la jointure t4->t5 qui soit la plus gourmande du plan d'exécution alors que ce ne sont pas les plus gros volumes ...

Ce qui peut être intéressant à regarder c'est l'impact de la clause WHERE texte IN(...) car elle est placée sur la table la plus volumineuse. Si tu la retires l'impact est-il sensible ? Si oui, il faudrait peut-être regarder pour faire de la recherche FULLTEXT ...

-
Edité par Benzouye 27 mars 2020 à 15:54:08

Ce qui peut être intéressant à regarder c'est l'impact de la clause WHERE texte IN(...) car elle est placée sur la table la plus volumineuse. Si tu la retires l'impact est-il sensible ?

Je passe à 36 secondes. Là aussi, c'est un champ text. En fait, les types ne sont pas super variés : text, double precision boolean et timestamp with time zone (une joie de travailler avec ces derniers…)