EXPLAIN ANALYSE SELECT Po.PuppetName AS PuppetName, Po.PuppeteerLogin AS PuppeteerLogin, Po.RealmName AS RealmName, Re.RealmPublic AS RealmPublic, Re.RealmVerified AS RealmVerified FROM ethereal.Post Po, ethereal.Puppet Ch, ethereal.Realm Re WHERE Po.PuppeteerLogin = Ch.PuppeteerLogin AND Po.RealmName = Re.RealmName AND Po.PostTimestamp > (LOCALTIMESTAMP - INTERVAL '10 minutes') AND Po.PuppetName IS NOT NULL GROUP BY Po.PuppeteerLogin, Po.PuppetName, Po.RealmName, Re.RealmPublic, Re.RealmVerified; -- Pre changes Group (cost=298025.66..322310.42 rows=161898 width=77) (actual time=4241.28..4329.68 rows=74 loops=1) -> Sort (cost=298025.66..302073.12 rows=1618985 width=77) (actual time=4241.23..4253.68 rows=14420 loops=1) Sort Key: po.puppeteerlogin, po.puppetname, po.realmname, re.realmpublic, re.realmverified -> Merge Join (cost=24174.23..44794.94 rows=1618985 width=77) (actual time=3199.66..3891.09 rows=14420 loops=1) Merge Cond: ("outer".puppeteerlogin = "inner".puppeteerlogin) -> Sort (cost=5964.67..6056.93 rows=36906 width=10) (actual time=366.18..427.94 rows=36318 loops=1) Sort Key: ch.puppeteerlogin -> Seq Scan on puppet ch (cost=0.00..3165.06 rows=36906 width=10) (actual time=0.02..133.52 rows=36456 loops=1) -> Sort (cost=18209.57..18310.85 rows=40513 width=67) (actual time=2832.70..2945.77 rows=14411 loops=1) Sort Key: po.puppeteerlogin -> Hash Join (cost=30.30..15109.11 rows=40513 width=67) (actual time=2822.23..2830.93 rows=392 loops=1) Hash Cond: ("outer".realmname = "inner".realmname) -> Seq Scan on post po (cost=0.00..14369.84 rows=40513 width=41) (actual time=2820.88..2826.30 rows=392 loops=1) Filter: ((posttimestamp > (('now'::text)::timestamp(6) without time zone - '00:10'::interval)) AND (puppetname IS NOT NULL)) -> Hash (cost=29.84..29.84 rows=184 width=26) (actual time=1.25..1.25 rows=0 loops=1) -> Seq Scan on realm re (cost=0.00..29.84 rows=184 width=26) (actual time=0.02..0.90 rows=179 loops=1) Total runtime: 4419.10 msec (17 rows) -- Post changes Group (cost=100293106.79..100316406.96 rows=155334 width=77) (actual time=1029.10..1159.25 rows=99 loops=1) -> Sort (cost=100293106.79..100296990.15 rows=1553344 width=77) (actual time=1025.98..1047.32 rows=24730 loops=1) Sort Key: po.puppeteerlogin, po.puppetname, po.realmname, re.realmpublic, re.realmverified -> Merge Join (cost=100031106.45..100050913.48 rows=1553344 width=77) (actual time=453.60..839.30 rows=24730 loops=1) Merge Cond: ("outer".puppeteerlogin = "inner".puppeteerlogin) -> Sort (cost=25124.25..25229.58 rows=42133 width=67) (actual time=17.07..17.67 rows=631 loops=1) Sort Key: po.puppeteerlogin -> Hash Join (cost=120.06..21887.85 rows=42133 width=67) (actual time=2.32..14.25 rows=631 loops=1) Hash Cond: ("outer".realmname = "inner".realmname) -> Index Scan using idxpost_timestamp on post po (cost=0.00..21030.46 rows=42133 width=41) (actual time=0.15..7.05 rows=631 loops=1) Index Cond: (posttimestamp > (('now'::text)::timestamp(6) without time zone - '00:10'::interval)) Filter: (puppetname IS NOT NULL) -> Hash (cost=119.61..119.61 rows=181 width=26) (actual time=2.01..2.01 rows=0 loops=1) -> Index Scan using pkrealm on realm re (cost=0.00..119.61 rows=181 width=26) (actual time=0.05..1.55 rows=181 loops=1) -> Sort (cost=100005982.20..100006073.27 rows=36428 width=10) (actual time=435.39..506.81 rows=58859 loops=1) Sort Key: ch.puppeteerlogin -> Seq Scan on puppet ch (cost=100000000.00..100003222.28 rows=36428 width=10) (actual time=0.03..184.55 rows=36428 loops=1) Total runtime: 1175.33 msec (21 rows) EXPLAIN ANALYSE SELECT Po.PuppetName AS PuppetName, Po.PuppeteerLogin AS PuppeteerLogin, Po.RealmName AS RealmName, Re.RealmPublic AS RealmPublic, Re.RealmVerified AS RealmVerified FROM ethereal.Post Po, ethereal.Puppet Ch, ethereal.Realm Re WHERE Po.PostIDNumber > (SELECT MIN(PostIDNumber) FROM ethereal.Post WHERE PostTimeStamp > (LOCALTIMESTAMP - INTERVAL '10 minutes')) AND Po.PuppeteerLogin = Ch.PuppeteerLogin AND Po.RealmName = Re.RealmName AND Po.PuppetName IS NOT NULL GROUP BY Po.PuppeteerLogin, Po.PuppetName, Po.RealmName, Re.RealmPublic, Re.RealmVerified; -- Pre changes Group (cost=297096.47..321381.23 rows=161898 width=77) (actual time=2108.70..2179.12 rows=76 loops=1) InitPlan -> Aggregate (cost=14473.08..14473.08 rows=1 width=4) (actual time=1141.58..1141.58 rows=1 loops=1) -> Seq Scan on post (cost=0.00..14369.84 rows=41297 width=4) (actual time=1136.50..1140.95 rows=413 loops=1) Filter: (posttimestamp > (('now'::text)::timestamp(6) without time zone - '00:10'::interval)) -> Sort (cost=297096.47..301143.93 rows=1618985 width=77) (actual time=2108.55..2121.85 rows=15302 loops=1) Sort Key: po.puppeteerlogin, po.puppetname, po.realmname, re.realmpublic, re.realmverified -> Merge Join (cost=23245.04..43865.75 rows=1618985 width=77) (actual time=1782.43..2003.58 rows=15302 loops=1) Merge Cond: ("outer".puppeteerlogin = "inner".puppeteerlogin) -> Sort (cost=5964.67..6056.93 rows=36906 width=10) (actual time=415.56..449.62 rows=36318 loops=1) Sort Key: ch.puppeteerlogin -> Seq Scan on puppet ch (cost=0.00..3165.06 rows=36906 width=10) (actual time=0.02..135.58 rows=36456 loops=1) -> Sort (cost=17280.38..17381.66 rows=40513 width=67) (actual time=1362.15..1376.53 rows=15293 loops=1) Sort Key: po.puppeteerlogin -> Hash Join (cost=30.30..14179.92 rows=40513 width=67) (actual time=1354.41..1360.42 rows=411 loops=1) Hash Cond: ("outer".realmname = "inner".realmname) -> Seq Scan on post po (cost=0.00..13440.65 rows=40513 width=41) (actual time=1353.11..1355.96 rows=411 loops=1) Filter: ((postidnumber > $0) AND (puppetname IS NOT NULL)) -> Hash (cost=29.84..29.84 rows=184 width=26) (actual time=1.20..1.20 rows=0 loops=1) -> Seq Scan on realm re (cost=0.00..29.84 rows=184 width=26) (actual time=0.02..0.88 rows=179 loops=1) Total runtime: 2192.72 msec (21 rows) -- Post changes Group (cost=100292759.45..100316059.61 rows=155334 width=77) (actual time=876.30..969.77 rows=84 loops=1) InitPlan -> Aggregate (cost=21137.57..21137.57 rows=1 width=4) (actual time=3.98..3.98 rows=1 loops=1) -> Index Scan using idxpost_timestamp on post (cost=0.00..21030.46 rows=42846 width=4) (actual time=0.14..3.26 rows=538 loops=1) Index Cond: (posttimestamp > (('now'::text)::timestamp(6) without time zone - '00:10'::interval)) -> Sort (cost=100292759.45..100296642.81 rows=1553344 width=77) (actual time=875.32..893.11 rows=20378 loops=1) Sort Key: po.puppeteerlogin, po.puppetname, po.realmname, re.realmpublic, re.realmverified -> Merge Join (cost=100030759.11..100050566.13 rows=1553344 width=77) (actual time=436.22..708.88 rows=20378 loops=1) Merge Cond: ("outer".puppeteerlogin = "inner".puppeteerlogin) -> Sort (cost=24776.90..24882.24 rows=42133 width=67) (actual time=26.54..27.01 rows=524 loops=1) Sort Key: po.puppeteerlogin -> Hash Join (cost=120.06..21540.50 rows=42133 width=67) (actual time=5.93..24.30 rows=524 loops=1) Hash Cond: ("outer".realmname = "inner".realmname) -> Index Scan using pkpost on post po (cost=0.00..20683.11 rows=42133 width=41) (actual time=4.08..17.10 rows=524 loops=1) Index Cond: (postidnumber > $0) Filter: (puppetname IS NOT NULL) -> Hash (cost=119.61..119.61 rows=181 width=26) (actual time=1.68..1.68 rows=0 loops=1) -> Index Scan using pkrealm on realm re (cost=0.00..119.61 rows=181 width=26) (actual time=0.04..1.28 rows=181 loops=1) -> Sort (cost=100005982.20..100006073.27 rows=36428 width=10) (actual time=408.56..456.91 rows=54202 loops=1) Sort Key: ch.puppeteerlogin -> Seq Scan on puppet ch (cost=100000000.00..100003222.28 rows=36428 width=10) (actual time=0.01..162.22 rows=36428 loops=1) Total runtime: 984.75 msec (22 rows)