On N, 2005-09-01 at 21:48 -0400, cbbrowne at ca.afilias.info wrote:

> 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.

Sure it's cheaper. Mostly solved the probleme here too. tnx.

The index condition still looks quite bogus (same thing OR'd 4 times,
once for each set), and it still has to do the sort on log_actionseq,
but it does already make a huge difference in performance :)

 Sort  (cost=12.90..12.91 rows=1 width=207)
   Sort Key: log_actionseq
   ->  Index Scan using sl_log_1_idx2_hu, sl_log_1_idx2_hu,
sl_log_1_idx2_hu, sl_log_1_idx2_hu on sl_log_1  (cost=0.00..12.89 rows=1
width=207)
         Index Cond: (
   ((log_xid < '1349053093') AND (log_xid >= '1349052761')) 
OR ((log_xid < '1349053093') AND (log_xid >= '1349052761')) 
OR ((log_xid < '1349053093') AND (log_xid >= '1349052761')) 
OR ((log_xid < '1349053093') AND (log_xid >= '1349052761'))
                     )

> 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.

A simple test scenario is creating 2 sets on the same origin node
subscribing them to same subscriber node, subscribing one of the sets to
a 3rd node and then not running slon on that 3rd mnode for some time to
make sl_log_1 big enough to make bad plans to take long enought to be
visible;

-- 
Hannu Krosing <hannu at skype.net>