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

PostgreSQL performance can be poor with large temp tables

    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.

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Votes:
                  2 Vote for this issue
                  Watchers:
                  11 Start watching this issue

                  Dates

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