Project

General

Profile

Actions

Bug #14269

open

scoped search queries use 'ilike' instead of 'like'

Added by Chris Duryee about 8 years ago. Updated over 5 years ago.

Status:
New
Priority:
Normal
Category:
Performance
Target version:
Difficulty:
Triaged:
Fixed in Releases:
Found in Releases:

Description

Postgresql allows for 'ilike' which is case-insensitive like. However, 'ilike' cannot use indexes, resulting in slow queries.

For example:

SELECT  DISTINCT "katello_errata"."id", katello_errata.updated AS alias_0, katello_errata.id AS alias_1 FROM "katello_errata" LEFT OUTER JOIN "katello_erratum_cves" ON "katello_erratum_cves"."erratum_id" = "katello_errata"."id" LEFT OUTER JOIN "katello_erratum_bugzillas" ON "katello_erratum_bugzillas"."erratum_id" = "katello_errata"."id" LEFT OUTER JOIN "katello_erratum_packages" ON "katello_erratum_packages"."erratum_id" = "katello_errata"."id" WHERE (("katello_errata"."errata_id" ILIKE '%sadfasdf%' OR "katello_errata"."severity" ILIKE '%sadfasdf%' OR "katello_errata"."errata_type" ILIKE '%sadfasdf%' OR "katello_erratum_cves"."cve_id" ILIKE '%sadfasdf%' OR "katello_erratum_bugzillas"."bug_id" ILIKE '%sadfasdf%' OR "katello_erratum_packages"."nvrea" ILIKE '%sadfasdf%' OR "katello_erratum_packages"."name" ILIKE '%sadfasdf%')) AND "katello_errata"."id" IN (SELECT DISTINCT "katello_errata"."id" FROM "katello_errata" INNER JOIN "katello_repository_errata" ON "katello_repository_errata"."erratum_id" = "katello_errata"."id" WHERE "katello_repository_errata"."repository_id" IN (SELECT "katello_repositories"."id" FROM "katello_repositories"  WHERE (katello_repositories.id in (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,1,36,37,38,39,40,41,43,44,45,46,47,48,49,50,51,42,27,28,29,30,31,32,33,34,35,52,53,54,55,56,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,81,57,26,80,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,102,104,115,105,109,111,106,108,110,112,113,114,107,101,103,128,127,129,125,123,124,120,122,117,121,116,118,132,138,135,130,142,137,134,131,141,126,136,139,133,140,119,143) or katello_repositories.id in (NULL) or katello_repositories.id in (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,1,36,37,38,39,40,41,43,44,45,46,47,48,49,50,51,42,27,28,29,30,31,32,33,34,35,52,53,54,55,56,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,81,57,26,80,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,102,104,115,105,109,111,106,108,110,112,113,114,107,101,103,128,127,129,125,123,124,120,122,117,121,116,118,132,138,135,130,142,137,134,131,141,126,136,139,133,140,119,143))) AND "katello_repository_errata"."repository_id" IN (SELECT "katello_repositories"."id" FROM "katello_repositories"  WHERE (katello_repositories.id in (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,1,36,37,38,39,40,41,43,44,45,46,47,48,49,50,51,42,27,28,29,30,31,32,33,34,35,52,53,54,55,56,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,81,57,26,80,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,102,104,115,105,109,111,106,108,110,112,113,114,107,101,103,128,127,129,125,123,124,120,122,117,121,116,118,132,138,135,130,142,137,134,131,141,126,136,139,133,140,119,143) or katello_repositories.id in (NULL) or katello_repositories.id in (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,1,36,37,38,39,40,41,43,44,45,46,47,48,49,50,51,42,27,28,29,30,31,32,33,34,35,52,53,54,55,56,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,81,57,26,80,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,102,104,115,105,109,111,106,108,110,112,113,114,107,101,103,128,127,129,125,123,124,120,122,117,121,116,118,132,138,135,130,142,137,134,131,141,126,136,139,133,140,119,143)) AND "katello_repositories"."environment_id" IN (2)))  ORDER BY katello_errata.updated desc, katello_errata.id DESC LIMIT 20 OFFSET 0;

This query is from erratum search, it takes 3.1 seconds. If the ILIKE statements are changed to LIKE, it takes 0.4 seconds.

There are a couple of ways to go about fixing this but one way would be to call lower() on both sides of the comparisons, which would let us use LIKE. There is some discussion in http://grokbase.com/t/postgresql/pgsql-hackers/12cwwmjgs9/ilike-vs-indices about the pros and cons of this (mostly related to locale concerns), but I think for the types of data Katello works with, I think a "lowercase everything when searching" approach is OK.

Actions #1

Updated by John Mitsch about 8 years ago

  • Assignee set to Zach Huntington-Meath
Actions #2

Updated by Eric Helms almost 8 years ago

  • translation missing: en.field_release set to 143
Actions #3

Updated by Chris Duryee almost 8 years ago

  • Bugzilla link set to 1330756
Actions #4

Updated by Justin Sherrill over 7 years ago

  • translation missing: en.field_release changed from 143 to 114
Actions

Also available in: Atom PDF