Hi,
pg_dump takes O(N) time dumping just one table (or a few) explicitly
specified with a -t parameter. It thus becomes painfully slow on a database
with very many tables.
(The use case is copying a few tables over to a test DB, from a large
production data warehouse.)
The three queries taking O(N) time are listed below. AFAICT each of these
queries could be filtered by table name/OID, at least when the number of
tables matching the -t parameters is small, allowing pg_dump to complete in
seconds rather than minutes.
SELECT c.tableoid, c.oid, c.relname, c.relacl, c.relkind, c.relnamespace,
(SELECT rolname FROM pg_catalog.pg_roles WHERE oid = c.relowner) AS
rolname, c.relchecks, c.relhastriggers, c.relhasindex, c.relhasrules,
c.relhasoids, c.relfrozenxid, tc.oid AS toid, tc.relfrozenxid AS
tfrozenxid, c.relpersistence, CASE WHEN c.reloftype <> 0 THEN
c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, d.refobjid AS
owning_tab, d.refobjsubid AS owning_col, (SELECT spcname FROM pg_tablespace
t WHERE t.oid = c.reltablespace) AS reltablespace,
array_to_string(c.reloptions, ', ') AS reloptions,
array_to_string(array(SELECT 'toast.' || x FROM unnest(tc.reloptions) x),
', ') AS toast_reloptions FROM pg_class c LEFT JOIN pg_depend d ON
(c.relkind = 'S' AND d.classid = c.tableoid AND d.objid = c.oid AND
d.objsubid = 0 AND
d.refclassid = c.tableoid AND d.deptype = 'a') LEFT JOIN pg_class tc ON
(c.reltoastrelid = tc.oid) WHERE c.relkind in ('r', 'S', 'v', 'c', 'f')
ORDER BY c.oid
SELECT tableoid, oid, typname, typnamespace, '{=U}' AS typacl, (SELECT
rolname FROM pg_catalog.pg_roles WHERE oid = typowner) AS rolname,
typinput::oid AS typinput, typoutput::oid AS typoutput, typelem, typrelid,
CASE WHEN typrelid = 0 THEN ' '::"char" ELSE (SELECT relkind FROM pg_class
WHERE oid = typrelid) END AS typrelkind, typtype, typisdefined, typname[0]
= '_' AND typelem != 0 AND (SELECT typarray FROM pg_type te WHERE oid =
pg_type.typelem) = oid AS isarray FROM pg_type
SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend WHERE
deptype != 'p' AND deptype != 'e' ORDER BY 1,2
Cheers,
Gulli
