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

PostgreSQL performance can be poor with large temp tables

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Minor
    • Resolution: Fixed
    • 2.3.7, 2.4
    • 2.4.8, 2.5.4, 2.6.1
    • Performance

    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

              mr-russ Russell Smith
              mr-russ Russell Smith
              Petr Skoda Petr Skoda
              Sam Hemelryk Sam Hemelryk
              Dan Poltawski Dan Poltawski
              Matteo Scaramuccia, David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              2 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                13/Jan/14