Database::queryTableInformation can be optimize, here is an example of the SQL generated by Database::queryTableInformation:

LOG:  statement: SELECT column_name, data_type, column_default FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'variable' AND (data_type = 'bytea' OR (numeric_precision IS NOT NULL AND column_default::text LIKE '%nextval%'));
LOG:  duration: 14.278 ms

compared with an equivalent (I think) query against PostgreSQL system catalog tables:

LOG:  statement: SELECT pg_attribute.attname, pg_type.typname, pg_attrdef.adsrc
      FROM pg_namespace
      JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
      JOIN pg_attribute ON pg_attribute.attrelid = pg_class.oid
      JOIN pg_type ON pg_type.oid = pg_attribute.atttypid
      LEFT JOIN pg_attrdef ON pg_attrdef.oid   = pg_attribute.attrelid
                          AND pg_attrdef.adnum = pg_attribute.attnum
    WHERE pg_namespace.nspname = 'public'
      AND pg_class.relname = 'variable'
      AND pg_class.relkind IN ('r', 'v')
      AND (pg_type.typname = 'bytea' OR (pg_attribute.atttypmod <> -1 AND pg_attrdef.adsrc LIKE '%nextval'));
LOG:  duration: 2.249 ms

The problem is originally reported by nathanweeks.

Comments

bzrudi71’s picture

Status: Active » Closed (duplicate)

Closing as a duplicate in favor of #1079762: DatabaseSchema_pgsql::queryTableInformation() is slow as it's the older issue and moved the information given here over... :)