[Bucardo-general] LEFT JOINs are killing performance...

[Greg Sabino Mullane]

On Mon, Jul 26, 2010 at 11:35:59AM +0200, Michelle Sullivan wrote:
> I saw some messages previously about working on getting rid of the left
> joins to speed up replication...

Hmm..I don't think they are going away any time soon, so we should 
probably figure out what's going on to make them slow otherwise...

...
> Bitmap Heap Scan on bucardo_delta d (cost=42877.71..668218.03 rows=2646208
...
> Bitmap Index Scan on bucardo_delta_public_nets2evid_txn
>   (cost=0.00..42216.16 rows=2646208
...
> Bitmap Heap Scan on bucardo_track bt
>   (cost=80259.57..347551.98 rows=1686442

That's an awful lot of rows. Is bucardo_delta and bucardo_track being 
purged regularly? How many unreplicated rows are there for the 
table in question? bucardo_purge_delta() should empty out all the 
no longer used rows (consider it a vacuum full for bucardo_delta), 
and bucardo_compress_delta can slim down and remove duplicate rows 
(consider it a regular vacuum for bucardo_delta). Do you have 
bucardo_purge_delta() running as a cronjob?

-- 
Greg Sabino Mullane greg at endpoint.com
End Point Corporation
PGP Key: 0x14964AC8
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 163 bytes
Desc: not available
Url : https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20100726/5d56f5a4/attachment.bin