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
Comment #1
bzrudi71 commentedClosing 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... :)