performance - queryset - LOW LIKE vs iLIKE

objects.filter django

Question

¿Cómo se compara el rendimiento de los siguientes dos componentes de consulta?

Más bajo como

... LOWER(description) LIKE ''%abcde%'' ...

me gusta

... description iLIKE ''%abcde%'' ...

Answer 1

La respuesta depende de muchos factores como la versión de Postgres, la codificación y la configuración regional, en particular, LC_COLLATE .

La expresión simple lower(description) LIKE ''%abc%'' suele ser un poco más rápida que la description ILIKE ''%abc%'' , y es un poco más rápida que la expresión regular equivalente: description ~* ''abc'' . Esto es importante para exploraciones secuenciales en las que la expresión debe evaluarse para cada fila probada.

Pero para tablas grandes como usted demuestra en su respuesta, ciertamente se usaría un índice. Para patrones arbitrarios (no solo anclados a la izquierda) sugiero un índice de trigrama usando el módulo adicional pg_trgm . Luego hablamos de milisegundos en lugar de segundos y la diferencia entre las expresiones anteriores se anula.

Los índices GIN y GiST (utilizando las clases de operador gin_trgm_ops o gist_trgm_ops ) admiten LIKE ( ~~ ), ILIKE ( ~~* ), ~ , ~* (y algunas variantes más) por igual. Con un índice GIN de trigrama en la description (generalmente más grande que GiST, pero más rápido para las lecturas), su consulta usará la description ILIKE ''case_insensitive_pattern'' .

Relacionado:

• Variaciones en el rendimiento de la consulta de PostgreSQL LIKE
• Cadenas UTF-8 similares para autocompletar el campo

Fundamentos para la coincidencia de patrones en Postgres:

• Coincidencia de patrones con LIKE, SIMILAR TO o expresiones regulares en PostgreSQL

Cuando se trabaja con dicho índice de trigrama, normalmente es más práctico trabajar con:

description ILIKE ''%abc%''

O con el operador de expresión regular que no distingue entre mayúsculas y minúsculas (sin % comodines):

description ~* ''abc''

Un índice en (description) no admite consultas en la parte lower(description) como:

lower(description) LIKE ''%abc%''

Y viceversa.

Con los predicados en la parte lower(description) exclusivamente , el índice de expresión es la opción ligeramente mejor.

En todos los demás casos, es preferible un índice en (description) ya que admite tanto predicados sensibles a mayúsculas como minúsculas.

Answer 2

Según mis pruebas ( diez de cada consulta), LOWER LIKE es aproximadamente un 17% más rápido que iLIKE .

Explicación

Creé que un millón de filas contienen algunos datos aleatorios de texto mixto:

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)

Verifique el número de filas:

my_test_db=# select count(id) from books ;
count
---------
1000009

(Sí, tengo nueve filas adicionales de otras pruebas, no hay problema).

Ejemplo de consulta y resultados:

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

Resultados para LOWER LIKE

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

Resultados para 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

Información de la base de datos de divulgación

Versión de Postgres:

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

Configuración de la colación:

my_test_db=# select datcollate from pg_database where datname = ''my_test_db'';
datcollate
-------------
en_CA.UTF-8

Definición de la tabla:

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)