>
> I was having this same problem this morning, after the completed copy of a
> XX
> million row table from a 7.3.8 database to a 7.4.8.  The copy took 20
> hours
> and there were hundreds of thousands of transactions during that time.
>
> As you describe (but far worse on my old hardware), queries of sl_log_1
> were
> taking nearly ten minutes.  No vacuuming or analyzing made a difference
> and
> the queries were "de-optimized" into sequential scans.  In novice's
> desperation, I tried adding a new index on only the log_xid column.  The
> queries began using this index in favor of sequential scans and the
> subscriber is now catching up.
>
> This solution makes little sense, but it certainly worked for my
> particular
> scenario.

No, that does make some sense.

The XID index could lead to filtering out some sizable proportion of the
rows, which would indeed help.

I could see sl_log_1.log_origin often being essentially useless as a
criterion, as queries would often pick *all* values in that column.

Ditto for sl_log_1.log_tableid, as you'll normally be drawing *all* the
tables.

sl_log_1.log_cmdtype isn't a selection criterion at all, and ditto for
sl_log_1.log_cmddata.

I suppose that in version 8.1, there would be merit to putting an index
each on log_xid and log_actionseq, bitmap indexing then doing some good.

I find it unremarkable that people find some value in adding some
additional indices.  The set of indexes has always looked pretty
minimalist to me.

Methinks it warrants documenting the "possibles," and it's worth pointing
out that if adding an index gets rid of this problem, that is WAY cheaper
than redesigning the code that generates the queries.

If there's any way of generating some replicable test scenarios that do
indeed exercise the above form of slow query, then it would be great to do
some testing with some extra indices to see which make a difference.