Hannu Krosing wrote:

> Perhaps you could change the index on sl_log_1 to something more useful.
>
>Currently the index is on 
>
>create index sl_log_1_idx1 on @NAMESPACE at .sl_log_1
>    (log_origin, log_xid @NAMESPACE at .xxid_ops, log_actionseq);
>
>which, on postgresql 7.4, is useful only in case there is only one set
>subscribed from the master, 
>
>And in 99.9% of cases (that is when ther is more than 1 xid), the index
>is not used for sorting, that is log_actionseq in the index is mostly
>dead weight.
>
>Also, as slon forces use of indexscan, it actually forces an indexscan
>on constant column ( @NAMESPACE at .sl_log_1.log_origin ) which performs
>even worse than plain seqscan.
>
>Of course these problems manifest themselves in a really bad way only on
>postgresql 7.4, meaning that they will eventually go away even without
>doing anything.
>
>In any case mention the index issue in readme, perhaps recommending
>using an index like this:
>
>create index sl_log_1_idx1 on @NAMESPACE at .sl_log_1
>    (log_xid @NAMESPACE at .xxid_ops);
>
>instead, which should be at least somewhat useful in most cases actually
>used by slon, and does not force pessimal plans in some.
>  
>
I will indeed see about adding this, pronto.

I just had the entertainment of discovering a node (with 3 sets) where
the database, being somewhat uninteresting, had been down for 3 weeks.

I thought I'd add this index "for grins", as I was watching the log
queries run typically for 6-8 seconds.

As soon as the index was created, the following became typical:

DEBUG2 remoteWorkerThread_2: SYNC 480236 processing
DEBUG2 remoteWorkerThread_2: syncing set 3 with 1 table(s) from provider 1
DEBUG2 remoteWorkerThread_2: syncing set 1 with 74 table(s) from provider 1
DEBUG2 remoteWorkerThread_2: syncing set 2 with 1 table(s) from provider 1
DEBUG2 remoteHelperThread_2_1: 0.014 seconds delay for first row
DEBUG2 remoteHelperThread_2_1: 0.023 seconds until close cursor

I'd say that's a worthwhile index to have around :-).