Vous n'êtes pas identifié(e).
Bonjour,
Plus je test pgsql et plus je rencontre des cas un peu farfelu qui concerne des requêtes pourtant assez simple.
Je me demandais donc si je n'avais pas un problème de conf, ou autre, qui permetrai à l'optimiseur de ne pas dérailler.
pgsql 9.1
windows 32-bits, sur poste de travaille.
"effective_cache_size";"512MB"
"maintenance_work_mem";"16MB"
"shared_buffers";"512MB"
"work_mem";"1MB"
Dernier exemple en date :
création des tables :
CREATE TABLE t_scalaire_mere
(
id integer NOT NULL,
nom character varying(32),
CONSTRAINT t_scalaire_mere_pkey PRIMARY KEY (id )
)
WITH (
OIDS=FALSE
);
CREATE TABLE t_scalaire
(
id integer NOT NULL,
col_a integer,
col_b integer
)
WITH (
OIDS=FALSE
);
CREATE INDEX idx_scal_1
ON t_scalaire
USING btree
(id );Donnée :
with tmp (cnt) as (
select * from generate_series(0, 1000000))
insert into t_scalaire_mere (select cnt, cnt from tmp);
with tmp (cnt) as (
select * from generate_series(0, 5))
insert into t_scalaire (select id, id, id from t_scalaire_mere, tmp);clusterisation de la table fille :
ALTER TABLE t_scalaire CLUSTER ON idx_scal_1;Analyze des tables :
analyze t_scalaire;
analyze t_scalaire_mere;bon maintenant ce qui nous interesse, une aggrégation sur la table fille afin de pouvori rapatrier en ligne les données de la col_a ou col_b (émulation d'une table de téléphone).
J'ai de grosse disparité avec les plans dès que l'on travaille avec un peu de donnée :
1ere requete :
select a.id, a.nom, string_agg(cast(col_a as varchar), ';')
from t_scalaire_mere a
inner join t_scalaire b on a.id = b.id
where b.id < 50000
group by a.id, a.nom;explain :
"GroupAggregate (cost=82455.70..91084.08 rows=313759 width=14) (actual time=792.290..1052.650 rows=50000 loops=1)"
" Output: a.id, a.nom, string_agg(((b.col_a)::character varying)::text, ';'::text)"
" -> Sort (cost=82455.70..83240.10 rows=313759 width=14) (actual time=792.271..874.166 rows=300000 loops=1)"
" Output: a.id, a.nom, b.col_a"
" Sort Key: a.id, a.nom"
" Sort Method: external sort Disk: 7600kB"
" -> Merge Join (cost=2.00..48445.11 rows=313759 width=14) (actual time=0.023..308.454 rows=300000 loops=1)"
" Output: a.id, a.nom, b.col_a"
" Merge Cond: (a.id = b.id)"
" -> Index Scan using t_scalaire_mere_pkey on public.t_scalaire_mere a (cost=0.00..31388.37 rows=1000001 width=10) (actual time=0.008..17.888 rows=50001 loops=1)"
" Output: a.id, a.nom"
" -> Index Scan using idx_scal_1 on public.t_scalaire b (cost=0.00..10635.69 rows=313759 width=8) (actual time=0.012..120.384 rows=300000 loops=1)"
" Output: b.id, b.col_a, b.col_b"
" Index Cond: (b.id < 50000)"
"Total runtime: 1064.040 ms"La "presque même requête" (la colonne de restriction du where change) :
select a.id, a.nom, string_agg(cast(col_a as varchar), ';')
from t_scalaire_mere a
inner join t_scalaire b on a.id = b.id
where a.id < 50000
group by a.id, a.nom;explain :
"GroupAggregate (cost=237402.77..242510.04 rows=49827 width=14) (actual time=4558.865..4849.538 rows=50000 loops=1)"
" Output: a.id, a.nom, string_agg(((b.col_a)::character varying)::text, ';'::text)"
" -> Sort (cost=237402.77..238150.17 rows=298962 width=14) (actual time=4558.821..4667.590 rows=300000 loops=1)"
" Output: a.id, a.nom, b.col_a"
" Sort Key: a.id, a.nom"
" Sort Method: external merge Disk: 7592kB"
" -> Hash Join (cost=2560.17..205102.77 rows=298962 width=14) (actual time=36.205..4088.923 rows=300000 loops=1)"
" Output: a.id, a.nom, b.col_a"
" Hash Cond: (b.id = a.id)"
" -> Seq Scan on public.t_scalaire b (cost=0.00..92432.99 rows=5999999 width=8) (actual time=0.009..1672.420 rows=6000006 loops=1)"
" Output: b.col_a, b.id"
" -> Hash (cost=1693.33..1693.33 rows=49827 width=10) (actual time=36.159..36.159 rows=50000 loops=1)"
" Output: a.id, a.nom"
" Buckets: 4096 Batches: 2 Memory Usage: 828kB"
" -> Index Scan using t_scalaire_mere_pkey on public.t_scalaire_mere a (cost=0.00..1693.33 rows=49827 width=10) (actual time=0.013..19.607 rows=50000 loops=1)"
" Output: a.id, a.nom"
" Index Cond: (a.id < 50000)"
"Total runtime: 4861.120 ms"Pouf on passe en scannage de table + hash join !
Et la je dois dire que je ne comprend pas pourquoi il y a une telle différence.
Bon ceci dit, pour ce cas j'ai trouvé la parade, vu que le plan est meilleur ...
select a.id, a.nom, b.string_agg
from t_scalaire_mere a
inner join (select id, string_agg(cast(col_a as varchar), ';') from t_scalaire group by id) b on a.id = b.id
where a.id < 50000explain :
"Merge Join (cost=0.00..272766.46 rows=44350 width=42) (actual time=0.041..335.088 rows=50000 loops=1)"
" Output: a.id, a.nom, (string_agg(((t_scalaire.col_a)::character varying)::text, ';'::text))"
" Merge Cond: (t_scalaire.id = a.id)"
" -> GroupAggregate (cost=0.00..259378.97 rows=890087 width=8) (actual time=0.025..270.598 rows=50001 loops=1)"
" Output: t_scalaire.id, string_agg(((t_scalaire.col_a)::character varying)::text, ';'::text)"
" -> Index Scan using idx_scal_1 on public.t_scalaire (cost=0.00..188252.89 rows=5999999 width=8) (actual time=0.011..102.634 rows=300007 loops=1)"
" Output: t_scalaire.id, t_scalaire.col_a, t_scalaire.col_b"
" -> Index Scan using t_scalaire_mere_pkey on public.t_scalaire_mere a (cost=0.00..1693.33 rows=49827 width=10) (actual time=0.013..19.027 rows=50000 loops=1)"
" Output: a.id, a.nom"
" Index Cond: (a.id < 50000)"
"Total runtime: 343.798 ms"Dernière modification par punkoff (25/11/2011 14:44:26)
Hors ligne
Un autre exemple que j'avais remonté à un de vos membre :
Tables :
CREATE TABLE t1
(
t_id integer NOT NULL,
t_nom character varying(32),
CONSTRAINT t1_pkey PRIMARY KEY (t_id )
);
CREATE TABLE t2
(
t_id integer,
CONSTRAINT t2_t_id_fkey FOREIGN KEY (t_id)
REFERENCES t1 (t_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);
CREATE TABLE t3
(
t_id integer,
CONSTRAINT t3_t_id_fkey FOREIGN KEY (t_id)
REFERENCES t1 (t_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);insert :
INSERT INTO t1
(SELECT cnt, 'code' || cnt
FROM (SELECT generate_series AS cnt
FROM generate_series(1, 1000)) AS a);
INSERT INTO t2
(SELECT mod(generate_series, 1000) + 1
FROM generate_series(1, 2000000));
INSERT INTO t3
(SELECT mod(generate_series, 1000) + 1
FROM generate_series(1, 3000000));index + stat :
CREATE INDEX idx_2 ON t2(t_id);
CREATE INDEX idx_3 ON t3(t_id);
analyze t1;
analyze t2;
analyze t3;alors la requête qui marche bien :
SELECT t1.*, cnt2, cnt3
FROM t1
LEFT OUTER JOIN (SELECT t2.t_id, count(*) AS cnt2 FROM t2 GROUP BY t_id) AS b ON b.t_id = T1.t_id
LEFT OUTER JOIN (SELECT t3.t_id, count(*) AS cnt3 FROM t3 GROUP BY t_id) AS c ON c.t_id = T1.t_id
WHERE t1.t_id =5l'explain :
"Nested Loop Left Join (cost=94.30..11772.18 rows=1 width=27) (actual time=58.995..59.001 rows=1 loops=1)"
" Join Filter: (t3.t_id = t1.t_id)"
" -> Nested Loop Left Join (cost=39.36..4712.64 rows=1 width=19) (actual time=29.058..29.062 rows=1 loops=1)"
" Join Filter: (t2.t_id = t1.t_id)"
" -> Index Scan using t1_pkey on t1 (cost=0.00..8.27 rows=1 width=11) (actual time=0.020..0.024 rows=1 loops=1)"
" Index Cond: (t_id = 5)"
" -> GroupAggregate (cost=39.36..4704.35 rows=1 width=4) (actual time=29.023..29.023 rows=1 loops=1)"
" -> Bitmap Heap Scan on t2 (cost=39.36..4694.74 rows=1921 width=4) (actual time=1.136..27.686 rows=2000 loops=1)"
" Recheck Cond: (t_id = 5)"
" -> Bitmap Index Scan on idx_t2 (cost=0.00..38.88 rows=1921 width=0) (actual time=0.673..0.673 rows=2000 loops=1)"
" Index Cond: (t_id = 5)"
" -> GroupAggregate (cost=54.94..7059.51 rows=1 width=4) (actual time=29.919..29.919 rows=1 loops=1)"
" -> Bitmap Heap Scan on t3 (cost=54.94..7045.08 rows=2885 width=4) (actual time=1.067..28.713 rows=3000 loops=1)"
" Recheck Cond: (t_id = 5)"
" -> Bitmap Index Scan on idx_t3 (cost=0.00..54.22 rows=2885 width=0) (actual time=0.643..0.643 rows=3000 loops=1)"
" Index Cond: (t_id = 5)"
"Total runtime: 59.183 ms"et là où le plan devrait être identique mais ne l'est pas ...
SELECT t1.*, cnt2, cnt3
FROM t1
LEFT OUTER JOIN (SELECT t2.t_id, count(*) AS cnt2 FROM t2 GROUP BY t_id) AS b ON b.t_id = T1.t_id
LEFT OUTER JOIN (SELECT t3.t_id, count(*) AS cnt3 FROM t3 GROUP BY t_id) AS c ON c.t_id = T1.t_id
WHERE t1.t_id BETWEEN 5 AND 10;explain :
"Hash Right Join (cost=97208.35..97232.16 rows=6 width=27) (actual time=1789.122..1789.384 rows=6 loops=1)"
" Hash Cond: (t3.t_id = t1.t_id)"
" -> HashAggregate (cost=58275.00..58285.00 rows=1000 width=4) (actual time=1027.616..1027.823 rows=1000 loops=1)"
" -> Seq Scan on t3 (cost=0.00..43275.00 rows=3000000 width=4) (actual time=0.007..355.825 rows=3000000 loops=1)"
" -> Hash (cost=38933.27..38933.27 rows=6 width=19) (actual time=761.424..761.424 rows=6 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Merge Left Join (cost=38919.83..38933.27 rows=6 width=19) (actual time=761.398..761.409 rows=6 loops=1)"
" Merge Cond: (t1.t_id = b.t_id)"
" -> Index Scan using t1_pkey on t1 (cost=0.00..8.37 rows=6 width=11) (actual time=0.022..0.028 rows=6 loops=1)"
" Index Cond: ((t_id >= 5) AND (t_id <= 10))"
" -> Sort (cost=38919.83..38922.33 rows=1000 width=12) (actual time=761.366..761.366 rows=11 loops=1)"
" Sort Key: b.t_id"
" Sort Method: quicksort Memory: 71kB"
" -> Subquery Scan on b (cost=38850.00..38870.00 rows=1000 width=12) (actual time=760.667..760.949 rows=1000 loops=1)"
" -> HashAggregate (cost=38850.00..38860.00 rows=1000 width=4) (actual time=760.665..760.841 rows=1000 loops=1)"
" -> Seq Scan on t2 (cost=0.00..28850.00 rows=2000000 width=4) (actual time=0.073..251.489 rows=2000000 loops=1)"
"Total runtime: 1789.700 ms"Pour moi l'optimiseur n'arrive pas à gérer les stats dans ce cas là.
Le simple fait d'utiliser un BETWEEN ou IN lui force le scanange de table alors qu'il devrait, avec les stats, choisir entre un seq scan ou attaquer avec les indexs.
Dernière modification par punkoff (25/11/2011 14:39:48)
Hors ligne
Il y a une différence de plan parce que PostgreSQL ne déduit pas de a.id=b.id et b.id<50000 que a.id<50000 aussi. Il ne s'occupe de la transitivité de ce genre de conditions que pour les égalités.
Marc.
Hors ligne
Bonjour,
Dans ce cas pourquoi dans la requête 3 du 1er poste, la sous-requête ne fait qu'une sélection de 300 007 ligne pour le group agrégate ?
" -> GroupAggregate (cost=0.00..259378.97 rows=890087 width=8) (actual time=0.025..270.598 rows=50001 loops=1)"
" Output: t_scalaire.id, string_agg(((t_scalaire.col_a)::character varying)::text, ';'::text)"
" -> Index Scan using idx_scal_1 on public.t_scalaire (cost=0.00..188252.89 rows=5999999 width=8) (actual time=0.011..102.634 rows=300007 loops=1)"
" Output: t_scalaire.id, t_scalaire.col_a, t_scalaire.col_b"Cette table dispose de 6m de ligne à raison de 6 occurance par id.
S'il ne gérai pas la transitivité sur ce genre de condition il aurai dû traiter d'une façon global la table t_scalaire afin de pouvoir réaliser ensuite son merge (comme dans les exemple 1 & 2 en fait, on vois bien l'index scan sur 1m de ligne de la table t_scalaire_mere pour la 1ere requete, et le seq scan de 6m de ligne sur la table t_scalaire pour la 2eme)
Là on voit bien qu'il ne sélectionne que 300 000 occurences, ce qui correspond parfaitement à la condition a.id < 50 000 (a.id étant l'id de la table t_scalaire_mere sur lequel on fait la jointure).
Ou alors j'interprète mal le plan ?
Hors ligne
Tiens, déjà (je suis en train de regarder de plus près): «ALTER TABLE t_scalaire CLUSTER ON idx_scal_1;» ne clusterise pas la table. Ça ne fait que déclarer l'index comme clusterisant la table. Il faut encore faire un «cluster t_scalaire» pour que ça soit vraiment en cluster.
Ensuite, pour arriver aux mêmes plans que les vôtres, j'ai du abaisser random_page_cost et seq_page_cost à des valeurs < 1 (ce qui n'est valable que si les tables sont dans le cache). Comment avez-vous fait de votre côté?
Pour ce qui est de votre exemple, on est dans un cas un peu «tordu»…
On a un «merge join», qui va récupérer les clés des deux jeux de données en même temps (ce sont des scans d'index, ils fournissent donc les enregistrements en continu, pas d'un coup à la fin comme des sort ou hash): «a where a.id< 50000» d'un côté, «select id, string_agg(cast(col_a as varchar), ';') from t_scalaire group by id» de l'autre. C'est un algo de fusion tri. Ce qui fait que si une clé récupérée dans le premier jeu n'a pas d'équivalent dans le jeu b, ce n'est même pas la peine d'aller calculer son string_agg (on saute ce morceau du plan). Ce qui explique le nombre plus faible d'enregistrements récupérés dans l'index scan (en espérant être clair, c'est un peu dur à expliquer sans faire de dessin, je trouve 
).
On obtient d'ailleurs un résultat du même genre avec
select a.id, a.nom, string_agg(cast(col_a as varchar), ';')
from t_scalaire_mere a
inner join t_scalaire b on a.id = b.id
where a.id < 50000
group by a.id, a.nom;à partir du moment où on met des random_page_cost et seq_page_cost assez bas, et qu'on repart sur un merge join avec scans d'index.
Mais il n'a pas déduit de b.id<50000 et a.id=b.id que a.id<50000. Il a éliminé les enregistrements de b et évité des calculs inutiles d'aggrégat simplement parce qu'il n'a pas trouvé les enregistrements dans a, et que ce n'était donc pas la peine d'aller plus loin dans b.
La solution la plus simple reste à mon avis d'écrire explicitement a.id<50000 et b.id<50000 (je n'ai pas testé). C'est un peu agaçant, mais c'est un choix volontaire dans PostgreSQL (pour ne pas alourdir la planification avec ça, il se contente de la transitivité des égalités).
Marc.
Hors ligne
Bonjour,
ALTER TABLE t_scalaire CLUSTER ON idx_scal_1;
Hmm oui pardon j'ai recopier le code de pgadmin bêtement pour la création de la table, j'avais effectivement fait un "cluster .... using ..." à la base.
Concernant les paramètres, j'ai ceux par défaut (les seuls que j'ai changé sont indiqués dans le poste initial) :
# - Planner Cost Constants -
#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
effective_cache_size = 512MBSinon, merci pour l'explication elle est très claire.
Du coup mon 2eme exemple (poste n°2) doit être de cause identique
Dernière modification par punkoff (28/11/2011 18:20:57)
Hors ligne
Avec ces paramètres là, j'ai du mal à comprendre les plans… il devrait privilégier les parcours de table. À moins qu'il y ait des enable_* qui aient été passés à off ?
Marc.
Hors ligne
bonjour,
non.
"enable_bitmapscan";"on"
"enable_hashagg";"on"
"enable_hashjoin";"on"
"enable_indexscan";"on"
"enable_material";"on"
"enable_mergejoin";"on"
"enable_nestloop";"on"
"enable_seqscan";"on"
"enable_sort";"on"
"enable_tidscan";"on"Ceci étant dit je préfère les plans avec les merge join sur index vu qu'ils sont, dans ce cas, beaucoup plus rapide que des table scan.
Hors ligne
Tant qu'ils sont en cache, ça restera vrai. Par contre, si les données ne sont plus en cache, il est probable que les autres plans seront plus performants.
Marc.
Hors ligne