Search generates horrible sql queries

System Information
  • Strapi Version: 3.3.4
  • Operating System: linux
  • Database: postgres

Hi,

Could someone please point me in the right direction when trying to at least locally optimize the search queries being used in the Admin gui? When searching for a collection type strapi executes the following query:

select distinct “someEntity”.*, “someEntity”.“someField1” as “_strapi_tmp_someEntity_internalName” from “someEntity” where (“someEntity”.“someField1”::text ILIKE ? or “someEntity”.“someField2”::text ILIKE ? or “someEntity”.“someField3”::text ILIKE ? or “someEntity”.“someField4”::text ILIKE ? or “someEntity”.“someField5”::text ILIKE ? or “someEntity”.“someField6”::text ILIKE ? or “someEntity”.“someField7”::text ILIKE ? or “someEntity”.“someField8”::text ILIKE ? or “someEntity”.“someField9”::text ILIKE ? or “someEntity”.“someField10”::text ILIKE ? or “someEntity”.“someField11”::text ILIKE ? or “someEntity”.“someField12”::text ILIKE ? or “someEntity”.“someField13”::text ILIKE ? or “someEntity”.“someField14”::text ILIKE ? or “someEntity”.“someField15”::text ILIKE ? or “someEntity”.“someField16”::text ILIKE ? or “someEntity”.“someField17”::text ILIKE ?) order by “_strapi_tmp_someEntity_someField1” asc limit ?

And when searching for an image to link to a collection type this one is executed:

select distinct “upload_file”.*, “upload_file”.“updated_at” as “_strapi_tmp_upload_file_updated_at” from “upload_file” where (“upload_file”.“name”::text ILIKE ? or “upload_file”.“alternativeText”::text ILIKE ? or “upload_file”.“caption”::text ILIKE ? or “upload_file”.“hash”::text ILIKE ? or “upload_file”.“ext”::text ILIKE ? or “upload_file”.“mime”::text ILIKE ? or “upload_file”.“url”::text ILIKE ? or “upload_file”.“previewUrl”::text ILIKE ? or “upload_file”.“provider”::text ILIKE ? or “upload_file”.“id”::text ILIKE ?) order by “_strapi_tmp_upload_file_updated_at” desc limit ?

As a long term solution I’d suggest adding a property on collection type giving users the option to set if said field should be included in the above search (for instance upload_file.hash and upload_file.previewUrl would be two candidates to remove) but as a more short term I’d like to try to just limit the fields as our database server is going nuts when 20-ish users are searching at the same time. I’ve tried finding where the actual query is being built but react isn’t really my got tech so I haven’t been able to do so.

Found it and potentially fixed it in added a way to have a less expensive search by jonmol · Pull Request #9316 · strapi/strapi · GitHub