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

PostgreSQL performance can be poor with large temp tables

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.3.7, 2.4
    • Fix Version/s: 2.4.8, 2.5.4, 2.6.1
    • Component/s: Performance
    • Labels:

      Description

      PostgreSQL doesn't automatically create statistics for temporary tables like all other supported databases. This is due to temporary tables being session local.

      When temporary tables get large, they need ANALYZE run to produce better performing database plans.

      This issue appears as part of the investigation into MDL-29439. It also needs a more general solution when we are working with temporary tables.

      Here are the links and summary of affected DB's for Analyzing temporary tables;

      MySQL: No statistics Engine until 5.6, so I'm assuming not affecting people.
      PostgreSQL: Doesn't automatically update statistics on Temporary Tables
      Oracle: Does update statistics
      SQL Server: Does update statistics unless you disable it.

      MySQL Link: http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_persistent_statistics
      PostgreSQL Link: http://www.postgresql.org/message-id/AANLkTin5Z3ie1XBCNs=sjDL=nsbXXERVF1xVnxcE_108@mail.gmail.com
      Oracle Link: http://docs.oracle.com/cd/B19306_01/server.102/b14231/tables.htm#i1006473
      SQL Server Link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/cfa8f983-8107-4af0-91a7-b2ee915e07ea

      I am not clear whether MySQL may begin to present an issue when newer versions use on disk statistics with temporary tables.

      It is also worth considering that when performing function like restoring or large batch processing jobs, forcing statistics collection on other database may not be a bad thing. It can cause issues as ownership and privileges usually need to be considered.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              mr-russ Russell Smith
              Reporter:
              mr-russ Russell Smith
              Peer reviewer:
              Petr Skoda
              Integrator:
              Sam Hemelryk
              Tester:
              Dan Poltawski
              Participants:
              Component watchers:
              Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              2 Vote for this issue
              Watchers:
              10 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                13/Jan/14