Vous n'êtes pas identifié(e).
Pages : 1
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.
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
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 ?
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.
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"Pages : 1