Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-18526

XMLDB: In Postgres when locale (LC_COLLATE) is set to 'utf-8', LIKE queries are slow

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 1.9.4
    • Fix Version/s: None
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Affected Branches:
      MOODLE_19_STABLE

      Description

      Postgres installations may be set to the UTF-8 locale in order that collation is correct (for example, e-acute sorts with e). Ours is.

      In this mode, Postgres does not use normal indexes for any LIKE query. This has an impact particularly in mdl_context path column (mdl_context is a large table so seq scan is slow, we think it was adding ~100ms to every course page request...), but may affect other tables too.

      http://www.postgresql.org/docs/8.3/interactive/locale.html

      In order to address this problem you create a special just-for-Postgres index in addition to the normal one (which will still be used for other queries, so you need both). Here is an example:

      CREATE INDEX mdl_cont_pat_ix2 ON mdl_context (path varchar_pattern_ops);

      I am not sure whether Moodle should do anything about this however one possibility is:

      • Add an option for indexes 'SUPPORTLIKE' (default NO)
      • For indexes where this option is enabled, if on Postgres, create a second index as above in install.

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                1 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: