Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Queries by URI with Postgres Significantly Degrade Performance #5045

Open
mbrochstein opened this issue Oct 3, 2019 · 3 comments

Comments

@mbrochstein
Copy link

commented Oct 3, 2019

Description

When using ->uri($uri) on an ElementQuery and a postgres database, the \craft\elements\db\ElementsQuery class forces caseInsensitive=true where clause in the query, which when processed by the \craft\helpers\Db classes' static parseParam method, adds a lower() wrapper when on postgres. This has a massive query performance implication, and cannot be turned off.

Steps to reproduce

  1. Configure CraftCMS with a PostgreSQL database
  2. Look at database queries that are produced.

Additional info

  • Craft version: 3.3
  • PHP version: 7.1
  • Database driver & version: PostgreSQL 10
  • Plugins & versions: N/A
@brandonkelly

This comment has been minimized.

Copy link
Member

commented Oct 8, 2019

We do create an index on lower("uri"), which should be used on element queries with the uri param:

$this->createIndex($this->db->getIndexName(Table::ELEMENTS_SITES, ['uri', 'siteId']), Table::ELEMENTS_SITES, ['lower([[uri]])', 'siteId']);

I just verified that this element query:

$entry = Entry::find()
    ->uri('testing')
    ->one();

will in fact use that index; from the EXPLAIN:

->  Index Scan using "elements_sites_uri_siteId_idx" on elements_sites elements_sites_1  (cost=0.14..8.16 rows=1 width=12)

That said the index could be ignored if other conditions come before uri. Are you able to share your full element query?

@mbrochstein

This comment has been minimized.

Copy link
Author

commented Oct 14, 2019

@brandonkelly Sure. Here it is:

SELECT
  "elements"."id",
  "elements"."fieldLayoutId",
  "elements"."uid",
  "elements"."enabled",
  "elements"."archived",
  "elements"."dateCreated",
  "elements"."dateUpdated",
  "elements_sites"."slug",
  "elements_sites"."siteId",
  "elements_sites"."enabled" AS "enabledForSite",
  "elements_sites"."uri",
  "entries"."sectionId",
  "entries"."typeId",
  "entries"."authorId",
  "entries"."postDate",
  "entries"."expiryDate",
  "content"."id"             AS "contentId",
  "content"."title",
  "content"."field_allowJibeAccess",
  "content"."field_altText",
  "content"."field_ampEnabled",
  "content"."field_articleContentType",
  "content"."field_availableForSyndication",
  "content"."field_biography",
  "content"."field_blockHeadingPrefix",
  "content"."field_companySponsorExpiration",
  "content"."field_contentSource",
  "content"."field_credit",
  "content"."field_creditLink",
  "content"."field_description",
  "content"."field_doNotOverwrite",
  "content"."field_email",
  "content"."field_excerpt",
  "content"."field_featuredContentLabel1",
  "content"."field_featuredContentLabel2",
  "content"."field_featuredContentLabel3",
  "content"."field_featuredContentLabel4",
  "content"."field_featuredContentLabel5",
  "content"."field_firstName",
  "content"."field_isCoach",
  "content"."field_jwPlayerVideo",
  "content"."field_lastName",
  "content"."field_leadMediaCaption",
  "content"."field_legacyId",
  "content"."field_legacyUpdatedAt",
  "content"."field_listicleContentType",
  "content"."field_logoImage",
  "content"."field_writerName",
  "content"."field_primaryImage",
  "content"."field_seo",
  "content"."field_secondaryImage",
  "content"."field_shortName",
  "content"."field_sponsorCredit",
  "content"."field_sponsorDescription",
  "content"."field_sponsorLink",
  "content"."field_sponsorshipType",
  "content"."field_tertiaryImage",
  "content"."field_tilesPerFilterSection",
  "content"."field_twitterHandle",
  "content"."field_veryLegacyId",
  "content"."field_wasMigrated",
  "content"."field_writerAltText",
  "content"."field_writerTitle",
  "structureelements"."root",
  "structureelements"."lft",
  "structureelements"."rgt",
  "structureelements"."level",
  "structureelements"."structureId"
FROM (SELECT
        "elements"."id"       AS "elementsId",
        "elements_sites"."id" AS "elementsSitesId",
        "content"."id"        AS "contentId",
        "structureelements"."structureId"
      FROM "craft_elements" "elements"
        INNER JOIN "craft_entries" "entries" ON "entries"."id" = "elements"."id"
        INNER JOIN "craft_elements_sites" "elements_sites" ON "elements_sites"."elementId" = "elements"."id"
        INNER JOIN "craft_content" "content"
          ON ("content"."elementId" = "elements"."id") AND ("content"."siteId" = "elements_sites"."siteId")
        LEFT JOIN "craft_structureelements" "structureelements"
          ON ("structureelements"."elementId" = "elements"."id") AND (EXISTS(SELECT *
                                                                             FROM "craft_structures"
                                                                             WHERE ("id" =
                                                                                    "structureelements"."structureId")
                                                                                   AND ("dateDeleted" IS NULL)))
      WHERE ("elements_sites"."siteId" = 1) AND ("elements"."archived" = 'f') AND (
        (("elements"."enabled" = 't') AND ("elements_sites"."enabled" = 't')) AND
        ("entries"."postDate" <= '2019-10-03 04:55:11') AND
        (("entries"."expiryDate" IS NULL) OR ("entries"."expiryDate" > '2019-10-03 04:55:11'))) AND
            ("elements"."dateDeleted" IS NULL) AND
            (lower("elements_sites"."uri") = 'the-unique-uri') AND
            ("elements_sites"."enabled" = 't') AND ("elements"."draftId" IS NULL) AND ("elements"."revisionId" IS NULL)
      ORDER BY "postDate" DESC
      LIMIT 10) "subquery"
  INNER JOIN "craft_entries" "entries" ON "entries"."id" = "subquery"."elementsId"
  INNER JOIN "craft_elements" "elements" ON "elements"."id" = "subquery"."elementsId"
  INNER JOIN "craft_elements_sites" "elements_sites" ON "elements_sites"."id" = "subquery"."elementsSitesId"
  INNER JOIN "craft_content" "content" ON "content"."id" = "subquery"."contentId"
  LEFT JOIN "craft_structureelements" "structureelements"
    ON ("structureelements"."elementId" = "subquery"."elementsId") AND
       ("structureelements"."structureId" = "subquery"."structureId")
ORDER BY "postDate" DESC;
@mbrochstein

This comment has been minimized.

Copy link
Author

commented Oct 14, 2019

The php code is as follows:

$entryQuery = Entry::find()
    ->section($type)
    ->relatedTo($categories)
    ->relatedTo($companyRelatedArray)
    ->relatedTo($subTypeRelatedArray)
    ->relatedTo($writerRelatedArray)
    ->after($after)
    ->dateUpdated($dateUpdated)
    ->limit($limit)
    ->slug($slug)
    ->id($id)
    ->legacyId($legacyId)
    ->status($statusParam)
    ->offset(($page-1)*$limit)
    ->uri($uri);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.