Bas comme vs iLIKE
Comment fonctionne la performance des deux composants de requête comparer?
INFÉRIEUR COMME
... LOWER(description) LIKE '%abcde%' ...
iLIKE
... description iLIKE '%abcde%' ...
2 réponses
la réponse dépend de nombreux facteurs comme la version Postgres, l'encodage et la localisation - LC_COLLATE en particulier.
l'expression nue lower(description) LIKE '%abc%' est typiquement un peu plus rapide que description ILIKE '%abc%' , et l'une ou l'autre est un peu plus rapide que l'expression régulière équivalente: description ~* 'abc' . Ceci est important pour les scans séquentiels où l'expression doit être évaluée pour chaque ligne testée.
Mais pour les grandes tables comme vous le démontrez dans votre réponse, on utiliserait certainement un index. Pour les motifs arbitraires (non seulement ancrés à gauche), je suggère un indice trigramme en utilisant le module additionnel pg_trgm . Ensuite nous parlons de millisecondes au lieu de secondes et la différence entre les expressions ci-dessus est annulée.
GIN et les index GiST (à l'aide de la gin_trgm_ops ou gist_trgm_ops opérateur de classes) support LIKE ( ~~ ), ILIKE ( ~~* ), ~ , ~* (et un peu plus de variantes). Avec un index trigramme GIN sur description (généralement plus grand que GiST, mais plus rapide pour les lectures), votre requête utiliserait description ILIKE 'case_insensitive_pattern' .
Related:
- PostgreSQL COMME les performances des requêtes variations
- Similaire des chaînes UTF-8 pour le champ de saisie semi-automatique
les Bases de filtrage dans Postgres:
quand on travaille avec ledit index trigramme c'est typiquement plus pratique pour travailler avec:
description ILIKE '%abc%'
ou avec l'opérateur regexp non sensible à la casse (sans % jokers):
description ~* 'abc'
un index sur (description) ne supporte pas les requêtes sur lower(description) comme:
lower(description) LIKE '%abc%'
et vice versa.
avec prédicats sur lower(description) exclusivement , l'indice d'expression est l'option légèrement meilleure.
dans tous les autres cas, un index sur (description) est préférable car il supporte à la fois sensible à la casse et insensible à la casse des prédicats.
selon mes tests ( ten de chaque requête), LOWER LIKE est d'environ 17% plus rapide que iLIKE .
explication
j'ai créé un million de lignes contiennent des données de texte mélangées au hasard:
require 'securerandom'
inserts = []
1000000.times do |i|
inserts << "(1, 'fake', '#{SecureRandom.urlsafe_base64(64)}')"
end
sql = "insert into books (user_id, title, description) values #{inserts.join(', ')}"
ActiveRecord::Base.connection.execute(sql)
Vérifier le nombre de lignes:
my_test_db=# select count(id) from books ;
count
---------
1000009
(Oui, j'ai neuf lignes supplémentaires d'autres tests - pas un problème.)
exemple de requête et de résultats:
my_test_db=# SELECT "books".* FROM "books" WHERE "books"."published" = 'f'
my_test_db=# and (LOWER(description) LIKE '%abcde%') ;
id | user_id | title | description | published
---------+---------+-------+----------------------------------------------------------------------------------------+------
1232322 | 1 | fake | 5WRGr7oCKABcdehqPKsUqV8ji61rsNGS1TX6pW5LJKrspOI_ttLNbaSyRz1BwTGQxp3OaxW7Xl6fzVpCu9y3fA | f
1487103 | 1 | fake | J6q0VkZ8-UlxIMZ_MFU_wsz_8MP3ZBQvkUo8-2INiDIp7yCZYoXqRyp1Lg7JyOwfsIVdpPIKNt1uLeaBCdelPQ | f
1817819 | 1 | fake | YubxlSkJOvmQo1hkk5pA1q2mMK6T7cOdcU3ADUKZO8s3otEAbCdEcmm72IOxiBdaXSrw20Nq2Lb383lq230wYg | f
résultats pour inférieur comme
my_test_db=# EXPLAIN ANALYZE SELECT "books".* FROM "books" WHERE "books"."published" = 'f' and (LOWER(description) LIKE '%abcde%') ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on books (cost=0.00..32420.14 rows=1600 width=117) (actual time=938.627..4114.038 rows=3 loops=1)
Filter: ((NOT published) AND (lower(description) ~~ '%abcde%'::text))
Rows Removed by Filter: 1000006
Total runtime: 4114.098 ms
résultats pour iLIKE
my_test_db=# EXPLAIN ANALYZE SELECT "books".* FROM "books" WHERE "books"."published" = 'f' and (description iLIKE '%abcde%') ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on books (cost=0.00..29920.11 rows=100 width=117) (actual time=1147.612..4986.771 rows=3 loops=1)
Filter: ((NOT published) AND (description ~~* '%abcde%'::text))
Rows Removed by Filter: 1000006
Total runtime: 4986.831 ms
de la Base de données info divulgation
Postgres version:
my_test_db=# select version();
version
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.2.4 on x86_64-apple-darwin12.4.0, compiled by i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 2336.11.00), 64-bit
paramètre de Classement:
my_test_db=# select datcollate from pg_database where datname = 'my_test_db';
datcollate
-------------
en_CA.UTF-8
définition de la Table:
my_test_db=# \d books
Table "public.books"
Column | Type | Modifiers
-------------+-----------------------------+-------------------------------------------------------
id | integer | not null default nextval('books_id_seq'::regclass)
user_id | integer | not null
title | character varying(255) | not null
description | text | not null default ''::text
published | boolean | not null default false
Indexes:
"books_pkey" PRIMARY KEY, btree (id)