"Dans notre progiciel, un écran permet de croiser des données entre un annuaire local et un annuaire global, en recherchant les premières lettres du nom des 2 côtés. Les index n'étaient pas utilisés au départ et nous avons ajouté varchar_pattern_ops mais ils ne sont toujours pas utilisés, même en baissant random_page_cost. Sur Oracle, ils l'étaient et dans le plan il y avait CONCATENATION, comme s'il cherchait dans chaque annuaire puis présentait le résultat global."
Réponse :
En effet, Oracle peut prendre l'initiative de transformer une requête avec des prédicats OR via cet opérateur CONCATENATION ou autre technique similaire, comme l'explique ici Jonathan Lewis, https://www.red-gate.com/simple-talk/da ... ormations/
PostgreSQL ne va pas prendre ce type d'initative mais il est trivial d'indiquer directement ce que nous voulons dès l'écriture de la requête avec un opérateur ensembliste, inutile de compter sur une transformation. Exemple avec 2 tables dans le thème des clans de géants, indexées de manière à faire des recherches en like 'chaine%' pour les noms :
Code : Tout sélectionner
create table geants_interclans(idgc integer generated by default as identity primary key, idgu uuid unique, nom_geant text, nom_usage_geant text, devise text);
create table geants(idg integer generated by default as identity primary key, idgu uuid references geants_interclans(idgu), nom text, surnom text, taille integer, actif boolean);
create index on geants_interclans(nom_geant text_pattern_ops);
create index on geants_interclans(nom_usage_geant text_pattern_ops);
create index on geants(nom text_pattern_ops);
create index on geants(surnom text_pattern_ops);
create index on geants(idgu);Code : Tout sélectionner
explain select idgc, idgc, idg, nom_geant, nom_usage_geant, nom, surnom
from geants_interclans gi join geants g
on (gi.idgu = g.idgu)
where
((nom_geant like 'GOLAR%' or nom_usage_geant like 'GOLAR%' )
OR
(nom like 'GOLAR%' or surnom like 'GOLAR%' ) )
AND
actif;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=3777.35..28438.62 rows=3297 width=140)
Workers Planned: 2
-> Parallel Hash Join (cost=2777.35..27108.92 rows=1374 width=140)
Hash Cond: (gi.idgu = g.idgu)
Join Filter: ((gi.nom_geant ~~ 'GOLAR%'::text) OR (gi.nom_usage_geant ~~ 'GOLAR%'::text) OR (g.nom ~~ 'GOLAR%'::text) OR (g.surnom ~~ 'GOLAR%'::text))
-> Parallel Seq Scan on geants_interclans gi (cost=0.00..22327.67 rows=416667 width=84)
-> Parallel Hash (cost=2115.24..2115.24 rows=52969 width=84)
-> Parallel Seq Scan on geants g (cost=0.00..2115.24 rows=52969 width=84)
Filter: actif
(9 lignes)Code : Tout sélectionner
explain select idgc, idg, nom_geant, nom_usage_geant, nom, surnom
from geants_interclans gi join geants g
on (gi.idgu = g.idgu)
where
(nom_geant like 'GOLAR%' or nom_usage_geant like 'GOLAR%' ) and actif
UNION
select idgc, idg, nom_geant, nom_usage_geant, nom, surnom
from geants_interclans gi join geants g
on (gi.idgu = g.idgu)
where
(nom like 'GOLAR%' or surnom like 'GOLAR%') and actif
;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=32383.95..32417.23 rows=3328 width=136)
Group Key: gi.idgc, g.idg, gi.nom_geant, gi.nom_usage_geant, g.nom, g.surnom
-> Gather (cost=1083.31..32334.03 rows=3328 width=136)
Workers Planned: 2
-> Parallel Append (cost=83.31..31001.23 rows=1386 width=136)
-> Parallel Hash Join (cost=19922.10..22176.37 rows=1036 width=136)
Hash Cond: (g.idgu = gi.idgu)
-> Parallel Seq Scan on geants g (cost=0.00..2115.24 rows=52969 width=84)
Filter: actif
-> Parallel Hash (cost=19820.18..19820.18 rows=8153 width=84)
-> Parallel Bitmap Heap Scan on geants_interclans gi (cost=765.30..19820.18 rows=8153 width=84)
Recheck Cond: ((nom_geant ~~ 'GOLAR%'::text) OR (nom_usage_geant ~~ 'GOLAR%'::text))
Filter: ((nom_geant ~~ 'GOLAR%'::text) OR (nom_usage_geant ~~ 'GOLAR%'::text))
-> BitmapOr (cost=765.30..765.30 rows=19467 width=0)
-> Bitmap Index Scan on geants_interclans_nom_geant_idx (cost=0.00..399.43 rows=10300 width=0)
Index Cond: ((nom_geant ~>=~ 'GOLAR'::text) AND (nom_geant ~<~ 'GOLAS'::text))
-> Bitmap Index Scan on geants_interclans_nom_usage_geant_idx (cost=0.00..356.10 rows=9167 width=0)
Index Cond: ((nom_usage_geant ~>=~ 'GOLAR'::text) AND (nom_usage_geant ~<~ 'GOLAS'::text))
-> Nested Loop (cost=83.31..8798.36 rows=921 width=136)
-> Parallel Bitmap Heap Scan on geants g_1 (cost=82.89..1697.98 rows=921 width=84)
Recheck Cond: ((nom ~~ 'GOLAR%'::text) OR (surnom ~~ 'GOLAR%'::text))
Filter: (actif AND ((nom ~~ 'GOLAR%'::text) OR (surnom ~~ 'GOLAR%'::text)))
-> BitmapOr (cost=82.89..82.89 rows=1727 width=0)
-> Bitmap Index Scan on geants_nom_idx (cost=0.00..49.22 rows=880 width=0)
Index Cond: ((nom ~>=~ 'GOLAR'::text) AND (nom ~<~ 'GOLAS'::text))
-> Bitmap Index Scan on geants_surnom_idx (cost=0.00..32.89 rows=847 width=0)
Index Cond: ((surnom ~>=~ 'GOLAR'::text) AND (surnom ~<~ 'GOLAS'::text))
-> Index Scan using geants_interclans_idgu_key on geants_interclans gi_1 (cost=0.42..7.71 rows=1 width=84)
Index Cond: (idgu = g_1.idgu)
(29 lignes)Code : Tout sélectionner
with generator(idgu, nom_geant, nom_usage_geant, devise) as(
select gen_random_uuid(),
case when random() < 0.01 then 'GOLAR' else md5(random()::text) end,
md5(random()::text) end,
case when random() < 0.01 then 'GOLAR' else md5(random()::text) end
from generate_series(1,1000000))
insert into geants_interclans(idgu, nom_geant, nom_usage_geant, devise)
select idgu,
nom_geant,
case when random() < 0.9 then nom_geant else nom_usage_geant end,
devise
from generator;
with rgen(idgc) as (select (random()*1000000)::integer from generate_series(1, 100000))
, generator(idgu, nom, surnom, taille, actif) as(
select idgu,
case when random() < 0.9 then nom_geant else md5(random()::text) end,
case when random() < 0.9 then nom_usage_geant else md5(random()::text) end,
200+random()*200,
case when random() < 0.9 then true else false end
from
geants_interclans gc, rgen
where gc.idgc = rgen.idgc)
insert into geants(idgu, nom, surnom, taille, actif) select idgu, nom, surnom, taille,actif
from generator;