Re: [HACKERS] pg_dump and thousands of schemas - Mailing list pgsql-performance

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.