Database::tableExists optimization for PostgreSQL

Created on 6 November 2014, over 9 years ago

Updated 23 June 2024, 6 days ago

Database::tableExists appears to be called repeatedly during normal use of a Drupal site. In PostgreSQL, this is implemented by querying the information_schema.tables view, which in our PostgreSQL 9.2 environment completes in the low-single-digit milliseconds; e.g.:

LOG:  duration: 5.150 ms  statement: SELECT 1 FROM information_schema.tables WHERE (table_catalog = 'drupal') AND  (table_schema = 'public') AND (table_name = 'node');

An equivalent query using the pg_tables view (see attached schema.inc.patch) is typically several times faster, however:

LOG:  duration: 0.966 ms  statement: SELECT 1 FROM pg_tables WHERE  (schemaname = 'public') AND (tablename = 'node');

(Note that in PostgreSQL, the information_schema.tables view essentiallydefines table_catalog as current_database(), so it should be unnecessary to specify it)

While several milliseconds would normally be unnoticeable to a user, I recently encountered a case where the performance of Database::tableExists significantly impacted the performance of the Tripal → module during an operation where Drupal nodes are created for records in an external Chado schema. During this operation, which can take hours depending on the number of records in the Chado schema, roughly a third of the total run time was spent in Database::tableExists.

There is precedent for this optimization: the MySQL version of Database::tableExists has been overridden as well to bypass MySQL's relatively-slow information_schema.

📌 Task

Status

Fixed

Version

7.0 ⚰️

Component

PostgreSQL driver →

Last updated 6 days ago

No maintainer

Created by

🇺🇸United States nathanweeks

Live updates comments and jobs are added and updated live.