Re: [HACKERS] pg_dump and thousands of schemas

Tom Lane-2 wrote
> Denis <

> socsam@

> > writes:
>> I've read all the posts in thread, and as I understood in version 9.2
>> some
>> patches were applied to improve pg_dump speed. I've just installed
>> PostgreSQL 9.2.1 and I still have the same problem. I have a database
>> with
>> 2600 schemas in it. I try to dump each schema individually, but it takes
>> too
>> much time for every schema (about 30-40 seconds per schema, no matter
>> what
>> the data size is).
>
> Could you provide a test case for that? Maybe the output of pg_dump -s,
> anonymized as you see fit?
>
>> Also for each schema dump I have a slow query log entry,
>
> Could you provide EXPLAIN ANALYZE output for that query?
>
> regards, tom lane
>
>
> --
> Sent via pgsql-performance mailing list (

> pgsql-performance@

> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

Here is the output of EXPLAIN ANALYZE. It took 5 seconds but usually it
takes from 10 to 15 seconds when I am doing backup.

Sort (cost=853562.04..854020.73 rows=183478 width=219) (actual
time=5340.477..5405.604 rows=183924 loops=1)
Sort Key: c.oid
Sort Method: external merge Disk: 33048kB
-> Hash Left Join (cost=59259.80..798636.25 rows=183478 width=219)
(actual time=839.297..4971.299 rows=183924 loops=1)
Hash Cond: (c.reltoastrelid = tc.oid)
-> Hash Right Join (cost=29530.77..146976.65 rows=183478
width=183) (actual time=404.959..3261.462 rows=183924 loops=1
)
Hash Cond: ((d.classid = c.tableoid) AND (d.objid = c.oid)
AND (d.refclassid = c.tableoid))
Join Filter: (c.relkind = 'S'::"char")
-> Seq Scan on pg_depend d (cost=0.00..71403.54 rows=995806
width=20) (actual time=1.137..878.571 rows=998642 lo
ops=1)
Filter: ((objsubid = 0) AND (deptype = 'a'::"char"))
Rows Removed by Filter: 2196665
-> Hash (cost=21839.91..21839.91 rows=183478 width=175)
(actual time=402.947..402.947 rows=183924 loops=1)
Buckets: 1024 Batches: 32 Memory Usage: 876kB
-> Seq Scan on pg_class c (cost=0.00..21839.91
rows=183478 width=175) (actual time=0.017..267.614 rows=183
924 loops=1)
Filter: (relkind = ANY ('{r,S,v,c,f}'::"char"[]))
Rows Removed by Filter: 383565
-> Hash (cost=18333.79..18333.79 rows=560979 width=40) (actual
time=434.258..434.258 rows=567489 loops=1)
Buckets: 4096 Batches: 32 Memory Usage: 703kB
-> Seq Scan on pg_class tc (cost=0.00..18333.79 rows=560979
width=40) (actual time=0.003..273.418 rows=567489 lo
ops=1)
SubPlan 1
-> Seq Scan on pg_authid (cost=0.00..1.01 rows=1 width=68)
(actual time=0.001..0.001 rows=1 loops=183924)
Filter: (oid = c.relowner)
Rows Removed by Filter: 2
SubPlan 2
-> Seq Scan on pg_tablespace t (cost=0.00..1.02 rows=1
width=64) (actual time=0.001..0.001 rows=0 loops=183924)
Filter: (oid = c.reltablespace)
Rows Removed by Filter: 2
SubPlan 3
-> Function Scan on unnest x (cost=0.00..1.25 rows=100
width=32) (actual time=0.001..0.001 rows=0 loops=183924)
Total runtime: 5428.498 ms

Here is the output of "pg_dump -s" test.dump
<http://postgresql.1045698.n5.nabble.com/file/n5730877/test.dump>

--
View this message in context: http://postgresql.1045698.n5.nabble.com/pg-dump-and-thousands-of-schemas-tp5709766p5730877.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.