[Bucardo-general] LEFT JOINs are killing performance...
Michelle Sullivan
michelle at sorbs.net
Mon Jul 26 09:35:59 UTC 2010
I saw some messages previously about working on getting rid of the left
joins to speed up replication...
Was this ever done or successful as the left joins are really killing me...
Jul 26 16:34:17 zyklon postgres[51693]: [14-1] LOG: duration:
1417601.888 ms plan:
Jul 26 16:34:17 zyklon postgres[51693]: [14-2] Unique
(cost=2016593.95..2016593.97 rows=1 width=49)
Jul 26 16:34:17 zyklon postgres[51693]: [14-3] -> Sort
(cost=2016593.95..2016593.96 rows=1 width=49)
Jul 26 16:34:17 zyklon postgres[51693]: [14-4] Sort Key:
d.rowid, d.rowid2, t.evidid, t.netid, t.delisted, t.delistedby
Jul 26 16:34:17 zyklon postgres[51693]: [14-5] -> Nested
Loop Left Join (cost=1554338.49..2016593.94 rows=1 width=49)
Jul 26 16:34:17 zyklon postgres[51693]: [14-6] Join
Filter: (t.netid = (d.rowid2)::bigint)
Jul 26 16:34:17 zyklon postgres[51693]: [14-7] ->
Merge Anti Join (cost=1554338.48..2016575.81 rows=1 width=17)
Jul 26 16:34:17 zyklon postgres[51693]: [14-8]
Merge Cond: (d.txntime = bt.txntime)
Jul 26 16:34:17 zyklon postgres[51693]: [14-9]
-> Sort (cost=1009301.82..1015917.34 rows=2646208 width=25)
Jul 26 16:34:17 zyklon postgres[51693]: [14-10]
Sort Key: d.txntime
Jul 26 16:34:17 zyklon postgres[51693]: [14-11]
-> Bitmap Heap Scan on bucardo_delta d
(cost=42877.71..668218.03 rows=2646208 width=25)
Jul 26 16:34:17 zyklon postgres[51693]: [14-12]
Recheck Cond: (tablename = 9830315::oid)
Jul 26 16:34:17 zyklon postgres[51693]: [14-13]
-> Bitmap Index Scan on
bucardo_delta_public_nets2evid_txn (cost=0.00..42216.16 rows=2646208
width=0)
Jul 26 16:34:17 zyklon postgres[51693]: [14-14]
-> Materialize (cost=545034.89..717895.20 rows=1686442 width=8)
Jul 26 16:34:17 zyklon postgres[51693]: [14-15]
-> Sort (cost=545034.89..549251.00
rows=1686442 width=8)
Jul 26 16:34:17 zyklon postgres[51693]: [14-16]
Sort Key: bt.txntime
Jul 26 16:34:17 zyklon postgres[51693]: [14-17]
-> Bitmap Heap Scan on bucardo_track
bt (cost=80259.57..347551.98 rows=1686442 width=8)
Jul 26 16:34:17 zyklon postgres[51693]: [14-18]
Recheck Cond: ((tablename =
9830315::oid) AND (targetdb = 'usmaster'::text))
Jul 26 16:34:17 zyklon postgres[51693]: [14-19]
-> Bitmap Index Scan on
bucardo_track_target (cost=0.00..79837.96 rows=1686442 width=0)
Jul 26 16:34:17 zyklon postgres[51693]: [14-20]
Index Cond: ((tablename =
9830315::oid) AND (targetdb = 'usmaster'::text))
Jul 26 16:34:17 zyklon postgres[51693]: [14-21] ->
Index Scan using nets2evid_idx2 on nets2evid t (cost=0.00..18.01 rows=1
width=32)
Jul 26 16:34:17 zyklon postgres[51693]: [14-22]
Index Cond: (t.evidid = (d.rowid)::bigint)
Jul 26 16:34:17 zyklon postgres[51693]: [14-23] STATEMENT:
Jul 26 16:34:17 zyklon postgres[51693]: [14-24]
SELECT DISTINCT d.rowid AS "BUCARDO_ID",
Jul 26 16:34:17 zyklon postgres[51693]: [14-25]
t.netblock ,hits
Jul 26 16:34:17 zyklon postgres[51693]: [14-26]
FROM bucardo.bucardo_delta d
Jul 26 16:34:17 zyklon postgres[51693]: [14-27] LEFT
JOIN public.most_recent_nets_cached t ON (t.netblock::ip4r = d.rowid::ip4r)
Jul 26 16:34:17 zyklon postgres[51693]: [14-28]
WHERE d.tablename = 9830325::oid
Jul 26 16:34:17 zyklon postgres[51693]: [14-29]
AND NOT EXISTS (
Jul 26 16:34:17 zyklon postgres[51693]: [14-30]
SELECT 1
Jul 26 16:34:17 zyklon postgres[51693]: [14-31]
FROM bucardo.bucardo_track bt
Jul 26 16:34:17 zyklon postgres[51693]: [14-32]
WHERE d.txntime = bt.txntime
Jul 26 16:34:17 zyklon postgres[51693]: [14-33]
AND bt.targetdb = 'usmaster'::text
Jul 26 16:34:17 zyklon postgres[51693]: [14-34]
AND bt.tablename = 9830325::oid
Jul 26 16:34:17 zyklon postgres[51693]: [14-35]
)
Jul 26 16:34:17 zyklon postgres[51693]: [14-36]
Jul 26 16:50:52 zyklon postgres[51693]: [15-1] LOG: duration:
972691.264 ms execute dbdpg_p72121_20:
Jul 26 16:50:52 zyklon postgres[51693]: [15-2]
SELECT DISTINCT d.rowid AS "BUCARDO_ID",
Jul 26 16:50:52 zyklon postgres[51693]: [15-3]
t.netid
,netblock,sorbsstatus,sysstatus,sorbshistory
Jul 26 16:50:52 zyklon postgres[51693]: [15-4]
FROM bucardo.bucardo_delta d
Jul 26 16:50:52 zyklon postgres[51693]: [15-5] LEFT
JOIN public.networks t ON (t.netid::int8 = d.rowid::int8)
Jul 26 16:50:52 zyklon postgres[51693]: [15-6]
WHERE d.tablename = 9830318::oid
Jul 26 16:50:52 zyklon postgres[51693]: [15-7]
AND NOT EXISTS (
Jul 26 16:50:52 zyklon postgres[51693]: [15-8]
SELECT 1
Jul 26 16:50:52 zyklon postgres[51693]: [15-9]
FROM bucardo.bucardo_track bt
Jul 26 16:50:52 zyklon postgres[51693]: [15-10]
WHERE d.txntime = bt.txntime
Jul 26 16:50:52 zyklon postgres[51693]: [15-11]
AND bt.targetdb = 'usmaster'::text
Jul 26 16:50:52 zyklon postgres[51693]: [15-12]
AND bt.tablename = 9830318::oid
Jul 26 16:50:52 zyklon postgres[51693]: [15-13]
)
Jul 26 16:50:52 zyklon postgres[51693]: [15-14]
Jul 26 16:50:58 zyklon postgres[51693]: [16-1] LOG: duration:
972691.235 ms plan:
Jul 26 16:50:58 zyklon postgres[51693]: [16-2] Unique
(cost=1030488.91..1030488.93 rows=1 width=45)
Jul 26 16:50:58 zyklon postgres[51693]: [16-3] -> Sort
(cost=1030488.91..1030488.92 rows=1 width=45)
Jul 26 16:50:58 zyklon postgres[51693]: [16-4] Sort Key:
d.rowid, t.netid, t.netblock, t.sorbsstatus, t.sysstatus, t.sorbshistory
Jul 26 16:50:58 zyklon postgres[51693]: [16-5] -> Nested
Loop Left Join (cost=900443.15..1030488.90 rows=1 width=45)
Jul 26 16:50:58 zyklon postgres[51693]: [16-6] ->
Merge Anti Join (cost=900443.15..1030478.52 rows=1 width=9)
Jul 26 16:50:58 zyklon postgres[51693]: [16-7]
Merge Cond: (d.txntime = bt.txntime)
Jul 26 16:50:58 zyklon postgres[51693]: [16-8]
-> Sort (cost=618930.96..621710.98 rows=1112010 width=17)
Jul 26 16:50:58 zyklon postgres[51693]: [16-9]
Sort Key: d.txntime
Jul 26 16:50:58 zyklon postgres[51693]: [16-10]
-> Bitmap Heap Scan on bucardo_delta d
(cost=18030.28..486353.31 rows=1112010 width=17)
Jul 26 16:50:58 zyklon postgres[51693]: [16-11]
Recheck Cond: (tablename = 9830318::oid)
Jul 26 16:50:58 zyklon postgres[51693]: [16-12]
-> Bitmap Index Scan on
bucardo_delta_public_networks_txn (cost=0.00..17752.28 rows=1112010
width=0)
Jul 26 16:50:58 zyklon postgres[51693]: [16-13]
-> Materialize (cost=281511.71..355891.96 rows=725661 width=8)
Jul 26 16:50:58 zyklon postgres[51693]: [16-14]
-> Sort (cost=281511.71..283325.86
rows=725661 width=8)
Jul 26 16:50:58 zyklon postgres[51693]: [16-15]
Sort Key: bt.txntime
Jul 26 16:50:58 zyklon postgres[51693]: [16-16]
-> Bitmap Heap Scan on bucardo_track
bt (cost=34539.56..200949.97 rows=725661 width=8)
Jul 26 16:50:58 zyklon postgres[51693]: [16-17]
Recheck Cond: ((tablename =
9830318::oid) AND (targetdb = 'usmaster'::text))
Jul 26 16:50:58 zyklon postgres[51693]: [16-18]
-> Bitmap Index Scan on
bucardo_track_target (cost=0.00..34358.15 rows=725661 width=0)
Jul 26 16:50:58 zyklon postgres[51693]: [16-19]
Index Cond: ((tablename =
9830318::oid) AND (targetdb = 'usmaster'::text))
Jul 26 16:50:58 zyklon postgres[51693]: [16-20] ->
Index Scan using "Networks_pkey" on networks t (cost=0.00..10.28 rows=1
width=36)
Jul 26 16:50:58 zyklon postgres[51693]: [16-21]
Index Cond: (t.netid = (d.rowid)::bigint)
Jul 26 16:50:58 zyklon postgres[51693]: [16-22] STATEMENT:
Jul 26 16:50:58 zyklon postgres[51693]: [16-23]
SELECT DISTINCT d.rowid AS "BUCARDO_ID",
Jul 26 16:50:58 zyklon postgres[51693]: [16-24]
t.id ,userid,userauth,usertype,userparent
Jul 26 16:50:58 zyklon postgres[51693]: [16-25]
FROM bucardo.bucardo_delta d
Jul 26 16:50:58 zyklon postgres[51693]: [16-26] LEFT
JOIN public.users t ON (t.id::int8 = d.rowid::int8)
Jul 26 16:50:58 zyklon postgres[51693]: [16-27]
WHERE d.tablename = 9830423::oid
Jul 26 16:50:58 zyklon postgres[51693]: [16-28]
AND NOT EXISTS (
Jul 26 16:50:58 zyklon postgres[51693]: [16-29]
SELECT 1
Jul 26 16:50:58 zyklon postgres[51693]: [16-30]
FROM bucardo.bucardo_track bt
Jul 26 16:50:58 zyklon postgres[51693]: [16-31]
WHERE d.txntime = bt.txntime
Jul 26 16:50:58 zyklon postgres[51693]: [16-32]
AND bt.targetdb = 'usmaster'::text
Jul 26 16:50:58 zyklon postgres[51693]: [16-33]
AND bt.tablename = 9830423::oid
Jul 26 16:50:58 zyklon postgres[51693]: [16-34]
)
It's lagging further and further behind... it just cannot seem to keep up.
Thanks,
Michelle
More information about the Bucardo-general
mailing list