Wow, I was just starting to try and improve our initial subscription
performance in slony 1.0.2 (I know, I know....) by figuring out what
indexes to drop, saving off appropriate create statements, etc.  - but
your way is much cooler! It looks like we'll have the opportunity to
upgrade to 1.1 in the next few months as part of our own app upgrade, so
maybe I can just avoid all this work ;-)

One question: the code in the patch seems to assume that relhasindex
should always be set back to 't' - is this safe because of the slony
primary key requirement for replicated tables, i.e., we don't have to
worry about setting relhasindex='t' on a table that actually had no
indexes to begin with?

Thanks!

- DAP 

-----Original Message-----
From: slony1-general-bounces at gborg.postgresql.org
[mailto:slony1-general-bounces at gborg.postgresql.org] On Behalf Of Darcy
Buskermolen
Sent: Thursday, July 14, 2005 12:39 PM
To: slony1-general at gborg.postgresql.org
Subject: Re: [Slony1-general] [PATCH] subscribe set performance
enhancements

On Thursday 14 July 2005 09:23, Christopher Browne wrote:
> Darcy Buskermolen wrote:
> >Hello,
> >
> >I have implemented an experimental patch that avoids index 
> >maintainance  during the SUBSCRIBE SET phase of replication.  This
work is based apon
> > theory and findings of Jan.   My initial testing shows that
subscribing
> > a large heavily hit pgbench (scalling factor of 100 with 50 
> >concurrent
> > clients) database can subscribe set in the same amount of time as it

> >takes to pg_dump | psql.
> >
> >Those of you who are interested can find the patch for 1.1 and CVS 
> >HEAD at http://www.dbitech.ca/slony/patches/subscription-perf-patch .

> >I make no  firm claim that, at this time, this patch will act as 
> >intended for  everybody.  It would help us in getting this patch 
> >fully ironed out and  applied to the main tree, if those of you who 
> >do try this patch would  report your findings back to the list, 
> >wether it be yes it works or I got  an error.
>
> It didn't work as provided; the 'oid' reference in the second line of 
> the query was ambiguous.
>
> Use 'where pg_catalog.pg_class.oid' instead of 'where oid' in the two 
> updates on pg_catalog.pg_class, and that turns out better...
>
> +#if 1
> +                 "update pg_catalog.pg_class set relhasindex = 'f'
> FROM  %s.sl_table "
> +                 "where pg_catalog.pg_class.oid =
%s.sl_table.tab_reloid "
> +                 "and %s.slon_quote_input('\"' ||
> %s.sl_table.tab_nspname || '\".\"' || %s.sl_table.tab_relname || '\"')

> = '%s'; "
> +#endif

Good catch Christopher.   I have updated the patch to reflect this
change.

--
Darcy Buskermolen
Wavefire Technologies Corp.

http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759
_______________________________________________
Slony1-general mailing list
Slony1-general at gborg.postgresql.org
http://gborg.postgresql.org/mailman/listinfo/slony1-general