Différence entre un like sans % ou _ et un =

La norme SQL évolue : tirez le meilleur d'un SGBD qui la respecte en écrivant du SQL moderne avec en complément PL/pgSQL, php, java etc.
Répondre
Phil
Administrateur du site
Messages : 291
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Différence entre un like sans % ou _ et un =

Message par Phil »

Merci à Lionel pour sa question :

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)
Cdlt. Phil - pgphil.ovh
Répondre