Salut Philippe,
Je me demande si, avec les SGBD récent, la where clause
Champ = « Valeur »
N’est pas exactement similaire à
Champ like « Valeur »
En terme de performance, s’il n’y a pas de % ni _ dans la valeur…
Tu en penses quoi ?
Réponse :
Si la colonne n’est pas indexée aucune différence, il y aura balayage complet.
Si la colonne est indexée aucune différence non plus avec PostgreSQL , même dans le cas où une variable est utilisée. Le planner connait la valeur de la variable à l’exécution et, en l’absence de % ou de _, transforme en fait le like en = . Les coûts et temps d’exécution sont donc au final similaires.
Démo :
Code : Tout sélectionner
SELECT version();
version
------------------------------------------------------------------------------------------
PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 ligne)
explain analyze select * from t2 where c22 = '2838ea77605a880f6fa9b7d3f71947d6';
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on t2 (cost=0.00..20834.00 rows=1 width=37) (actual time=0.027..279.078 rows=1 loops=1)
Filter: ((c22)::text = '2838ea77605a880f6fa9b7d3f71947d6'::text)
Rows Removed by Filter: 999999
Planning time: 0.147 ms
Execution time: 279.109 ms
(5 lignes)
Temps : 279,655 ms
explain analyze select * from t2 where c22 like '2838ea77605a880f6fa9b7d3f71947d6';
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on t2 (cost=0.00..20834.00 rows=1 width=37) (actual time=0.032..303.143 rows=1 loops=1)
Filter: ((c22)::text ~~ '2838ea77605a880f6fa9b7d3f71947d6'::text)
Rows Removed by Filter: 999999
Planning time: 0.118 ms
Execution time: 303.179 ms
(5 lignes)
Temps : 303,717 ms
create index t2_i1 on t2(c22);
CREATE INDEX
Temps : 9579,956 ms
explain analyze select * from t2 where c22 = '2838ea77605a880f6fa9b7d3f71947d6';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Index Scan using t2_i1 on t2 (cost=0.42..8.44 rows=1 width=37) (actual time=0.109..0.111 rows=1 loops=1)
Index Cond: ((c22)::text = '2838ea77605a880f6fa9b7d3f71947d6'::text)
Planning time: 0.266 ms
Execution time: 0.154 ms
(4 lignes)
explain analyze select * from t2 where c22 like '2838ea77605a880f6fa9b7d3f71947d6';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Index Scan using t2_i1 on t2 (cost=0.42..8.45 rows=1 width=37) (actual time=0.049..0.051 rows=1 loops=1)
Index Cond: ((c22)::text = '2838ea77605a880f6fa9b7d3f71947d6'::text)
Filter: ((c22)::text ~~ '2838ea77605a880f6fa9b7d3f71947d6'::text)
Planning time: 0.157 ms
Execution time: 0.098 ms
(5 lignes)
PREPARE sel01 (character varying) AS select * from t2 where c22 = $1;
PREPARE
EXPLAIN ANALYZE EXECUTE sel01('2838ea77605a880f6fa9b7d3f71947d6');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Index Scan using t2_i1 on t2 (cost=0.42..8.44 rows=1 width=37) (actual time=0.034..0.036 rows=1 loops=1)
Index Cond: ((c22)::text = '2838ea77605a880f6fa9b7d3f71947d6'::text)
Execution time: 0.070 ms
(3 lignes)
PREPARE sel02 (character varying) AS select * from t2 where c22 like $1;
PREPARE
EXPLAIN ANALYZE EXECUTE sel02('2838ea77605a880f6fa9b7d3f71947d6');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Index Scan using t2_i1 on t2 (cost=0.42..8.45 rows=1 width=37) (actual time=0.041..0.043 rows=1 loops=1)
Index Cond: ((c22)::text = '2838ea77605a880f6fa9b7d3f71947d6'::text)
Filter: ((c22)::text ~~ '2838ea77605a880f6fa9b7d3f71947d6'::text)
Execution time: 0.079 ms
(4 lignes)
EXPLAIN ANALYZE EXECUTE sel02('d4fea81c6b313e6438f457c194b02fd3');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Index Scan using t2_i1 on t2 (cost=0.42..8.45 rows=1 width=37) (actual time=0.133..0.135 rows=1 loops=1)
Index Cond: ((c22)::text = 'd4fea81c6b313e6438f457c194b02fd3'::text)
Filter: ((c22)::text ~~ 'd4fea81c6b313e6438f457c194b02fd3'::text)
Execution time: 0.178 ms
(4 lignes)