I can do a pg_dump and load the data back in much faster than the SUBSCRIBE SET runs. Why is that?
Slony-I depends on there being an already existant index on the primary key, and leaves all indexes alone whilst using the PostgreSQL COPY command to load the data. Further hurting performance, the COPY SET event (an event that the subscription process generates) starts by deleting the contents of tables, which leaves the table full of dead tuples. When you use pg_dump to dump the contents of a database, and then load that, creation of indexes is deferred until the very end. It is much more efficient to create indexes against the entire table, at the end, than it is to build up the index incrementally as each row is added to the table.
