Plugins » Katello

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.