Calcul avec plusieurs aggrégats

[Bibi218]

Bonjour tout le monde

Je cherche a faire un peu d'analyse de donnees sur mes tables, et je commence a tomber sur des cas assez recurrents qui me posent probleme.

Admettons que j'ai 3 tables.
Une table "community" avec 1 colonne "id" et d'autres attributs
Une table "user", de meme.
Une table "community_user" qui me modelise la relation ManyToMany entre les deux.

Si je veux par exemple le nombre de moins de 30 ans par communaute, pas de soucis

Code :

SELECT community_id, count(user_id) FROM community_user, user WHERE id=user_id AND age<30 GROUP BY community_id

Par contre, comment les choses se passent si je veux calculer plusieurs aggregats avec des filtres differents. Disons renvoyer le nombre de moins de 18 ans et de plus de 60 ans dans la meme requete.

J'avais commence quelque chose avec des clauses select imbriquees dans mon from, mais je me suis vite rendu compte que mon extraction commencait apres que ces select aient ete faits en integralite. Si je rajoute un limit 10 a la fin par exemple, je dois quand meme patienter jusqu'a la fin des select.

Ainsi,

Code :

1
2
3
4
SELECT community_id, c1, c2 FROM
(SELECT community_id, count(user_id) AS "c1" FROM community_user GROUP BY community_id) AS "q1",
(SELECT community_id, count(user_id) AS "c2" FROM community_user, user WHERE id=user_id AND age<30 GROUP BY community_id) AS "q2"
WHERE q1.community_id=q2.community_id

semble vraiment inefficace !

Y a-t'il une meilleure methode pour faire cela ?
Le SELECT CASE n'est pas satisfaisant, parce que les filtres que je veux appliquer ne sont pas forcement disjoints.
Les Window Functions (je suis sous Postgresql) m'ont ete recommandees, mais je ne vois pas du tout comment elles peuvent m'aider...
Les UNION ne me semblent pas tres "propres" dans ce cas, compare aux jointures (mais vu ce que j'y connais...)

[Bibi218]

Je viens de penser a ca

Code :

1
2
3
4
5
 
SELECT id,
(SELECT COUNT(id) FROM user, community_user WHERE id=user_id AND community_id=community.id),
(SELECT COUNT(id) FROM user, community_user WHERE id=user_id AND age<30 AND community_id=community.id)
FROM community

Mais je soupconne que c'est aussi lent que mon autre solution... Qu'en pensez-vous ?

[CinePhil]

Il faut utiliser CASE et additionner ceux qui répondent à la condition au lieu de les compter.

Au passage, les jointures s'écrivent depuis 20 ans avec l'opérateur JOIN ; il serait temps de s'y mettre !

Et indente et aère ton code, il sera plus facile à lire et à débugguer.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT cu.community_id, 
	SUM (
		CASE 
			WHEN u.age < 30 THEN 1
			ELSE 0
		END
	) AS moins_de_30_ans,
	SUM (
		CASE 
			WHEN u.age > 60 THEN 1
			ELSE 0
		END
	) AS plus_de_60_ans
FROM community_user cu
INNER JOIN user u ON u.id = cu.user_id 
GROUP BY cu.community_id

[Bibi218]

Merci pour la piste. Je vais lancer un test en taille reelle sur mes tables pour voir comment les differentes approches se comportent. A ce rythme la, ca prendra tout le week-end ^^

En ce qui concerne le WHERE, c'est une mauvaise habitude que j'ai prise de regarder les requetes genererees par mon ORM (SQLAlchemy) qui au lieu de INNER JOIN utilise des WHERE partout.
Mais effectivement, c'est plus lisible de separer les clauses de filtrage de la jointure, j'en conviens

[CinePhil]

Ah ces ORM ! Quelle plaie !

[Bibi218]

Etant plutot debutant en SQL, j'etais parti du principe que l'ORM savait un peu mieux que moi ce qu'il faisait.
Ensuite je me suis rendu compte que le WHERE et INNER JOIN etaient effectivement interpretes de la meme facon par le moteur, donc je croyais que les formulations etaient juste equivalentes. Je ne savais pas qu'une ecriture etait plus recommandee que l'autre.

[CinePhil]

Les deux écritures sont équivalentes pour la jointure interne. Avec la jointure externe, il peut y avoir un piège que décrit SQLPro dans son cours sur les jointures.

[Bibi218]

Bon, ca a pris du temps, mais je voulais etre sur que rien d'autre n'interferait avec la base, donc en gros j'ai lance les requetes le week-end, sur la meme machine.

Code :

1
2
3
4
5
6
SELECT id,
    (SELECT COUNT(id) FROM user INNER JOIN community_users ON id=user_id
        WHERE community_id=community.id),
    (SELECT COUNT(id) FROM user INNER JOIN community_users ON id=user_id
        WHERE age<30 AND sex='male' AND community_id=community.id)
FROM community

Cette requete se termine apres 19h 5mins

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT community.id, 
    SUM (
        CASE 
            WHEN age < 30 AND sex='male' THEN 1
            ELSE 0
        END
    ),
    COUNT (user.id)
FROM community
INNER JOIN community_users ON community.id = community_id
INNER JOIN user ON user.id = user_id
GROUP BY community.id

Celle-la met 1j 2h 10mins, c'est a dire pratiquement 40% de temps en plus

Pour information, la table community contient 3 millions d'entrees, la table user en contient 50 millions. La table de mapping est a environ 500 millions.
Je pense avoir mis les index ou il faut, sur les cles etrangeres egalement, et dans ce cas precis les colonnes "age" et "sex" (je precise que j'ai bien mis une Enum sur cette derniere, et non pas du texte... )

[Waldar]

Si vous avez des community sans user, vos deux requêtes ne sont pas équivalentes, la seconde devrait être :

Code :

1
2
3
4
5
6
7
8
9
    SELECT community.id
         , SUM(CASE WHEN user.age < 30 AND user.sex = 'male' THEN 1 ELSE 0 END)
         , COUNT(user.id)
      FROM community
 LEFT JOIN community_users
INNER JOIN user
        ON user.id = user_id
        ON community.id = community_id
  GROUP BY community.id

Si ce n'est pas le cas, pas de soucis.

Maintenant, vos temps d'exécution sont beaucoup trop élevés.
Que donnent les plans d'exécution de vos requêtes EXPLAIN SELECT ...; ?
Quelle configuration de serveur héberge votre base de données et comment interroge-t-il le stockage ?

[Fred_34]

Dans ta première requête, cette partie :

Code :

1
2
(SELECT COUNT(id) FROM user INNER JOIN community_users ON id=user_id
        WHERE community_id=community.id)

n'est elle pas équivalente à ceci ?:

Code :

(SELECT COUNT(user_id) FROM community_users WHERE community_id=community.id)

Edit:
C'est surement équivalent, mais en relisant tes messages, je pense que ce n'est pas le but puisque tu essayais de comparer le temps d’exécution des deux méthodes...

[CinePhil]

je precise que j'ai bien mis une Enum sur cette derniere, et non pas du texte...

Mauvaise idée !
Il est bien plus long pour le SGBD d'évaluer 'male' et 'female' qu'un simple chiffre !

Comme tu parles de ENUM, je suppose que tu utilises MySQL qui n'est pas non plus la meilleure idée avec des tables aussi grosses !

Donne la description exacte et complète de tes tables (Résultat de SHOW CREATE TABLE la_table si tu utilises MySQL) ainsi que le plan d'exécution comme l'a demandé Waldar (Résultat de EXPLAIN texte_de_la_requête si tu utilises MySQL).

As-tu essayé la mienne ?

[Fred_34]

Envoyé par Waldar

Si vous avez des community sans user, vos deux requêtes ne sont pas équivalentes, la seconde devrait être :

Code :

Si ce n'est pas le cas, pas de soucis.

Si ce n'est pas le cas, vous pouvez surement enlever une jointure :

Code :

1
2
3
4
5
6
7
    SELECT community_users.community_id
         , SUM(CASE WHEN user.age < 30 AND user.sex = 'male' THEN 1 ELSE 0 END)
         , COUNT(user.id)
      FROM community_users
INNER JOIN user
        ON user.id = user_id
  GROUP BY community_users.community_id

[Bibi218]

Beaucoup de reponses d'un seul coup, merci ! Aussi je prends tout dans l'ordre !

Envoyé par Waldar

Si vous avez des community sans user, vos deux requêtes ne sont pas équivalentes, la seconde devrait être :

Code :

Si ce n'est pas le cas, pas de soucis.

Maintenant, vos temps d'exécution sont beaucoup trop élevés.
Que donnent les plans d'exécution de vos requêtes EXPLAIN SELECT ...; ?
Quelle configuration de serveur héberge votre base de données et comment interroge-t-il le stockage ?

Je n'ai pas de communautes sans utilisateurs, donc le probleme ne se pose pas je dirais.
Pour ce qui est de ma configuration materielle, c'est une base Postgresql qui tourne sur ma machine de bureau (QuadCore i5, 8Go de RAM, local HD 7200tr/min) sans rien de particulier. C'est un petit projet personnel, donc pas les moyens d'avoir un serveur dedie avec SSD et toutes les petites options sympathiques !

Envoyé par CinePhil

Mauvaise idée !
Il est bien plus long pour le SGBD d'évaluer 'male' et 'female' qu'un simple chiffre !

Comme tu parles de ENUM, je suppose que tu utilises MySQL qui n'est pas non plus la meilleure idée avec des tables aussi grosses !

Donne la description exacte et complète de tes tables (Résultat de SHOW CREATE TABLE la_table si tu utilises MySQL) ainsi que le plan d'exécution comme l'a demandé Waldar (Résultat de EXPLAIN texte_de_la_requête si tu utilises MySQL).

As-tu essayé la mienne ?

Je croyais que les Enums dans Postgresql etaient justement la dans un soucis de performances, et etaient plus elegantes qu'un mapping en dur entre entiers et strings.

Pour ce qui est de mon schema, voici celui qui est reduit a mes 3 tables.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
CREATE TABLE community
(
  id serial NOT NULL,
  name text NOT NULL,
  category_id integer NOT NULL,
  last_visit timestamp WITH time zone NOT NULL,
  CONSTRAINT community_pkey PRIMARY KEY (id ),
  CONSTRAINT community_category_id_fk FOREIGN KEY (category_id)
      REFERENCES category (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
 
CREATE INDEX ix_community_category_id
  ON community
  USING btree
  (category_id );
 
CREATE INDEX ix_community_last_visit
  ON community
  USING btree
  (last_visit );
 
--------------------------------------------------
 
CREATE TABLE user
(
  id serial NOT NULL,
  profile_name text,
  name text,
  sex sex,
  age integer,
  birthday date,
  last_visit timestamp WITH time zone,
  location_id integer,
  hometown_id integer,
  work_id integer,
  valid BOOLEAN NOT NULL,
  CONSTRAINT user_pkey PRIMARY KEY (id ),
  CONSTRAINT user_hometown_id_fk FOREIGN KEY (hometown_id)
      REFERENCES place (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT user_location_id_fk FOREIGN KEY (location_id)
      REFERENCES place (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT user_work_id_fk FOREIGN KEY (work_id)
      REFERENCES work (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
 
CREATE INDEX ix_user_age
  ON user
  USING btree
  (age );
 
CREATE INDEX ix_user_hometown_id
  ON user
  USING btree
  (hometown_id );
 
CREATE INDEX ix_user_last_visit
  ON user
  USING btree
  (last_visit );
 
CREATE INDEX ix_user_location_id
  ON user
  USING btree
  (location_id );
 
CREATE INDEX ix_user_sex
  ON user
  USING btree
  (sex );
 
CREATE INDEX ix_user_valid
  ON user
  USING btree
  (valid );
 
CREATE INDEX ix_user_work_id
  ON user
  USING btree
  (work_id );
 
--------------------------------------------------
 
CREATE TABLE user_communities__community_users
(
  community_id integer NOT NULL,
  user_id integer NOT NULL,
  CONSTRAINT user_communities__community_users_pkey PRIMARY KEY (community_id , user_id ),
  CONSTRAINT community_users_fk FOREIGN KEY (community_id)
      REFERENCES community (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT user_communities_fk FOREIGN KEY (user_id)
      REFERENCES user (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
 
CREATE INDEX ix_user_communities__community_users_community_id
  ON user_communities__community_users
  USING btree
  (community_id );
 
CREATE INDEX ix_user_communities__community_users_user_id
  ON user_communities__community_users
  USING btree
  (user_id );

Envoyé par Fred_34

Si ce n'est pas le cas, vous pouvez surement enlever une jointure :

Code :

Le but etait de garder la jointure au cas ou je voudrais rajouter une condition sur "community", comme la categorie par exemple.

[Bibi218]

pour ce qui est des plans d'execution :

pour la requete

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT community.id, 
    SUM (
        CASE 
            WHEN age < 30 AND sex='female' THEN 1
            ELSE 0
        END
    ),
    COUNT (
        user.id
    )
FROM community
INNER JOIN
user_communities__community_users ON community.id = community_id
INNER JOIN
user ON user.id = user_id
GROUP BY community.id

j'obtiens

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
"GroupAggregate  (cost=185445412.85..201250011.61 rows=2768883 width=16)"
"  ->  Merge Join  (cost=185445412.85..194636413.18 rows=526872768 width=16)"
"        Merge Cond: (community.id = user_communities__community_users.community_id)"
"        ->  Sort  (cost=467819.39..474741.60 rows=2768883 width=4)"
"              Sort Key: community.id"
"              ->  Seq Scan on community  (cost=0.00..57968.83 rows=2768883 width=4)"
"        ->  Materialize  (cost=184937781.61..187572145.45 rows=526872768 width=16)"
"              ->  Sort  (cost=184937781.61..186254963.53 rows=526872768 width=16)"
"                    Sort Key: user_communities__community_users.community_id"
"                    ->  Hash Join  (cost=1781904.65..54587634.85 rows=526872768 width=16)"
"                          Hash Cond: (user_communities__community_users.user_id = user.id)"
"                          ->  Seq Scan on user_communities__community_users  (cost=0.00..7601473.68 rows=526872768 width=8)"
"                          ->  Hash  (cost=872599.62..872599.62 rows=52310562 width=12)"
"                                ->  Seq Scan on user  (cost=0.00..872599.62 rows=52310562 width=12)"

Pour

Code :

1
2
3
4
SELECT id,
(SELECT COUNT(id) FROM user, user_communities__community_users WHERE id=user_id AND community_id=community.id),
(SELECT COUNT(id) FROM user, user_communities__community_users WHERE id=user_id AND age<30 AND sex='female' AND community_id=community.id)
FROM community

j'ai

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
"Seq Scan on community  (cost=0.00..2239730798282.68 rows=2768883 width=4)"
"  SubPlan 1"
"    ->  Aggregate  (cost=404415.58..404415.59 rows=1 width=4)"
"          ->  Nested Loop  (cost=0.00..404353.84 rows=24696 width=4)"
"                ->  Index Scan using ix_user_communities__community_users_community_id on user_communities__community_users  (cost=0.00..59978.63 rows=24696 width=4)"
"                      Index Cond: (community_id = community.id)"
"                ->  Index Scan using user_pkey on user  (cost=0.00..13.93 rows=1 width=4)"
"                      Index Cond: (id = public.user_communities__community_users.user_id)"
"  SubPlan 2"
"    ->  Aggregate  (cost=404477.64..404477.65 rows=1 width=4)"
"          ->  Nested Loop  (cost=0.00..404477.32 rows=126 width=4)"
"                ->  Index Scan using ix_user_communities__community_users_community_id on user_communities__community_users  (cost=0.00..59978.63 rows=24696 width=4)"
"                      Index Cond: (community_id = community.id)"
"                ->  Index Scan using user_pkey on user  (cost=0.00..13.94 rows=1 width=4)"
"                      Index Cond: (id = public.user_communities__community_users.user_id)"
"                      Filter: ((age < 30) AND (sex = 'female'::sex))"

[Bibi218]

Envoyé par Waldar

Maintenant, vos temps d'exécution sont beaucoup trop élevés.
Que donnent les plans d'exécution de vos requêtes EXPLAIN SELECT ...; ?
Quelle configuration de serveur héberge votre base de données et comment interroge-t-il le stockage ?

Je m'excuse de revenir sur cette phrase en particulier, mais n'ayant pas beaucoup d'experience dans le domaine des bases de donnees, je me demande.
Quel est l'ordre de grandeur auquel je devrais m'attendre pour ce genre de requete ? Parce qu'a par exemple 10000 lectures par seconde (ce qui est assez rapide je trouve), il me faudrait plus de 12 heures rien que pour lister le contenu de user_communities__community_users. Bien sur je me doute que les index et autres accelerent grandement les performances, mais dans la mesure ou j'ai du mal a savoir a quoi m'attendre, c'est difficile de pouvoir dire si mes requetes sont lentes ou pas

Pour ce qui est des 2 explain, je soupconne que la premiere requete fait un join sur user_communities__community_users dans son integralite, alors que la seconde ne traite que des sous-parties, ce qui devrait etre bien plus leger sur des tables de cette taille. J'ai bon ?

[SQLpro]

Les temps de réponse d'une SGBDR ne sont absolument pas linéaires à la volumétrie. En effet :
1) du fait des index les traitement sont plutôt de cout logarithmique
2) le changement de volumétrie induit souvent un changement de plan de requête
Pour le 2, par exemple une jointure à faible volumétrie se traduira par un algorithme de boucle imbriquées, alors qu'avec une forte volumétrie cela va conduire à une fusion ou bien un hachage.

A +