Project

General

Profile

Bug #14269

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

Added by Chris Duryee over 3 years ago. Updated 12 months ago.

Status:
New
Priority:
Normal
Category:
Performance
Target version:
Difficulty:
Triaged:
Yes
Bugzilla link:
Pull request:
Team Backlog:
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.

History

#1 Updated by John Mitsch over 3 years ago

  • Assignee set to Zach Huntington-Meath

#2 Updated by Eric Helms about 3 years ago

  • Legacy Backlogs Release (now unused) set to 143

#3 Updated by Chris Duryee about 3 years ago

  • Bugzilla link set to 1330756

#4 Updated by Justin Sherrill almost 3 years ago

  • Legacy Backlogs Release (now unused) changed from 143 to 114

Also available in: Atom PDF