EXPLAIN (ANALYZE, BUFFERS)
SELECT c.table_schema,
c.table_name,
c.column_name,
c.data_type,
c.udt_name,
c.character_maximum_length,
c.is_nullable,
c.column_default,
c.numeric_precision,
c.numeric_scale,
c.is_identity,
c.identity_generation,
c.ordinal_position
FROM information_schema.columns c
JOIN information_schema.tables t
ON c.table_schema = t.table_schema
AND c.table_name = t.table_name
WHERE c.table_schema IN ('mock_db_1')
AND t.table_type = 'BASE TABLE'
ORDER BY c.table_schema, c.table_name, c.ordinal_position;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1509.10..1509.11 rows=1 width=432) (actual time=128792.260..128796.189 rows=5304 loops=1)
Sort Key: ((c.relname)::information_schema.sql_identifier), ((a.attnum)::information_schema.cardinal_number)
Sort Method: quicksort Memory: 1770kB
Buffers: shared hit=1710447
-> Nested Loop Left Join (cost=146.42..1509.09 rows=1 width=432) (actual time=22.900..128641.952 rows=5304 loops=1)
Buffers: shared hit=1710447
-> Nested Loop Left Join (cost=146.01..1506.30 rows=1 width=1060) (actual time=20.211..126137.271 rows=5304 loops=1)
Join Filter: (dep.refobjsubid = a.attnum)
Buffers: shared hit=1697967
-> Nested Loop (cost=145.73..1504.92 rows=1 width=1064) (actual time=18.951..125574.356 rows=5304 loops=1)
Join Filter: (c.relname = c_1.relname)
Rows Removed by Join Filter: 3813576
Buffers: shared hit=1681697
-> Nested Loop Left Join (cost=1.48..259.97 rows=1 width=128) (actual time=0.402..167.673 rows=720 loops=1)
Buffers: shared hit=1533
-> Hash Join (cost=1.07..258.89 rows=1 width=132) (actual time=0.311..60.444 rows=720 loops=1)
Hash Cond: (c_1.relnamespace = nc_1.oid)
Join Filter: ((CASE WHEN (nc_1.oid = pg_my_temp_schema()) THEN 'LOCAL TEMPORARY'::text WHEN (c_1.relkind = ANY ('{r,p}'::"char"[])) THEN 'BASE TABLE'::text WHEN (c_1.relkind = 'v'::"char") THEN 'VIEW'::text WHEN (c_1.relkind = 'f'::"char") THEN 'FOREIGN'::text ELSE NULL::text END)::text = 'BASE TABLE'::text)
Buffers: shared hit=93
-> Seq Scan on pg_class c_1 (cost=0.00..256.07 rows=655 width=73) (actual time=0.156..20.250 rows=931 loops=1)
Filter: ((relkind = ANY ('{r,v,f,p}'::"char"[])) AND (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
Rows Removed by Filter: 2715
Buffers: shared hit=92
-> Hash (cost=1.06..1.06 rows=1 width=68) (actual time=0.077..0.691 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on pg_namespace nc_1 (cost=0.00..1.06 rows=1 width=68) (actual time=0.048..0.053 rows=1 loops=1)
Filter: ((NOT pg_is_other_temp_schema(oid)) AND (nspname = 'mock_db_1'::name))
Rows Removed by Filter: 4
Buffers: shared hit=1
-> Nested Loop (cost=0.41..1.07 rows=1 width=4) (actual time=0.111..0.111 rows=0 loops=720)
Buffers: shared hit=1440
-> Index Scan using pg_type_oid_index on pg_type t_1 (cost=0.28..0.91 rows=1 width=8) (actual time=0.087..0.087 rows=0 loops=720)
Index Cond: (oid = c_1.reloftype)
Buffers: shared hit=1440
-> Index Only Scan using pg_namespace_oid_index on pg_namespace nt_1 (cost=0.13..0.15 rows=1 width=4) (never executed)
Index Cond: (oid = t_1.typnamespace)
Heap Fetches: 0
-> Hash Join (cost=144.25..1239.30 rows=452 width=1064) (actual time=0.218..168.553 rows=5304 loops=720)
Hash Cond: (t.typnamespace = nt.oid)
Buffers: shared hit=1680164
-> Hash Left Join (cost=143.16..1235.77 rows=452 width=1004) (actual time=0.202..154.947 rows=5304 loops=720)
Hash Cond: ((a.attrelid = ad.adrelid) AND (a.attnum = ad.adnum))
Buffers: shared hit=1680163
-> Hash Left Join (cost=143.14..1233.38 rows=452 width=972) (actual time=0.198..143.430 rows=5304 loops=720)
Hash Cond: (t.typbasetype = bt.oid)
Join Filter: (t.typtype = 'd'::"char")
Buffers: shared hit=1680163
-> Nested Loop (cost=1.65..1084.54 rows=452 width=838) (actual time=0.171..131.892 rows=5304 loops=720)
Buffers: shared hit=1680079
-> Nested Loop (cost=1.36..1030.92 rows=452 width=475) (actual time=0.159..100.086 rows=5304 loops=720)
Buffers: shared hit=1679761
-> Hash Join (cost=1.07..152.80 rows=233 width=136) (actual time=0.059..12.043 rows=720 loops=720)
Hash Cond: (c.relnamespace = nc.oid)
Buffers: shared hit=66241
-> Seq Scan on pg_class c (cost=0.00..146.69 rows=931 width=76) (actual time=0.050..9.690 rows=931 loops=720)
Filter: (relkind = ANY ('{r,v,f,p}'::"char"[]))
Rows Removed by Filter: 2715
Buffers: shared hit=66240
-> Hash (cost=1.06..1.06 rows=1 width=68) (actual time=0.245..0.248 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on pg_namespace nc (cost=0.00..1.06 rows=1 width=68) (actual time=0.016..0.018 rows=1 loops=1)
Filter: ((NOT pg_is_other_temp_schema(oid)) AND (nspname = 'mock_db_1'::name))
Rows Removed by Filter: 4
Buffers: shared hit=1
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute a (cost=0.29..3.75 rows=2 width=343) (actual time=0.044..0.107 rows=7 loops=518400)
Index Cond: ((attrelid = c.oid) AND (attnum > 0))
Filter: ((NOT attisdropped) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
Buffers: shared hit=1613520
-> Memoize (cost=0.29..0.34 rows=1 width=367) (actual time=0.002..0.002 rows=1 loops=3818880)
Cache Key: a.atttypid
Cache Mode: logical
Hits: 3818774 Misses: 106 Evictions: 0 Overflows: 0 Memory Usage: 39kB
Buffers: shared hit=318
-> Index Scan using pg_type_oid_index on pg_type t (cost=0.28..0.33 rows=1 width=367) (actual time=0.016..0.016 rows=1 loops=106)
Index Cond: (oid = a.atttypid)
Buffers: shared hit=318
-> Hash (cost=115.78..115.78 rows=2057 width=138) (actual time=16.900..16.904 rows=2057 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 378kB
Buffers: shared hit=84
-> Hash Join (cost=1.09..115.78 rows=2057 width=138) (actual time=0.155..12.995 rows=2057 loops=1)
Hash Cond: (bt.typnamespace = nbt.oid)
Buffers: shared hit=84
-> Seq Scan on pg_type bt (cost=0.00..103.57 rows=2057 width=78) (actual time=0.047..7.497 rows=2057 loops=1)
Buffers: shared hit=83
-> Hash (cost=1.04..1.04 rows=4 width=68) (actual time=0.037..0.038 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on pg_namespace nbt (cost=0.00..1.04 rows=4 width=68) (actual time=0.012..0.018 rows=5 loops=1)
Buffers: shared hit=1
-> Hash (cost=0.00..0.00 rows=1 width=38) (actual time=0.022..0.024 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Seq Scan on pg_attrdef ad (cost=0.00..0.00 rows=1 width=38) (actual time=0.021..0.021 rows=0 loops=1)
-> Hash (cost=1.04..1.04 rows=4 width=68) (actual time=0.032..0.033 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on pg_namespace nt (cost=0.00..1.04 rows=4 width=68) (actual time=0.010..0.015 rows=5 loops=1)
Buffers: shared hit=1
-> Nested Loop (cost=0.29..1.37 rows=1 width=8) (actual time=0.094..0.094 rows=0 loops=5304)
Join Filter: (dep.objid = seq.seqrelid)
Buffers: shared hit=16270
-> Index Scan using pg_depend_reference_index on pg_depend dep (cost=0.29..1.35 rows=1 width=12) (actual time=0.055..0.061 rows=1 loops=5304)
Index Cond: ((refclassid = '1259'::oid) AND (refobjid = c.oid))
Filter: ((classid = '1259'::oid) AND (deptype = 'i'::"char"))
Rows Removed by Filter: 3
Buffers: shared hit=16270
-> Seq Scan on pg_sequence seq (cost=0.00..0.00 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=5004)
-> Nested Loop (cost=0.41..0.48 rows=1 width=4) (actual time=0.039..0.039 rows=0 loops=5304)
Buffers: shared hit=12480
-> Index Scan using pg_collation_oid_index on pg_collation co (cost=0.28..0.30 rows=1 width=72) (actual time=0.019..0.019 rows=0 loops=5304)
Index Cond: (oid = a.attcollation)
Buffers: shared hit=11232
-> Index Scan using pg_namespace_oid_index on pg_namespace nco (cost=0.13..0.17 rows=1 width=68) (actual time=0.032..0.032 rows=0 loops=624)
Index Cond: (oid = co.collnamespace)
Filter: ((nspname <> 'pg_catalog'::name) OR (co.collname <> 'default'::name))
Rows Removed by Filter: 1
Buffers: shared hit=1248
Planning:
Buffers: shared hit=84
Planning Time: 47.428 ms
Execution Time: 128801.257 ms
(122 rows)
Time: 129003.590 ms (02:09.004)
Get-tables statements using
information_schemahas low performace,It is better to usepg_catalog.this is just for one schema whith 1000 tables: