explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZMwR

Settings
# exclusive inclusive rows x rows loops node
1. 0.488 723,842.322 ↓ 3.7 325 1

Sort (cost=6,413,562.45..6,413,562.68 rows=89 width=56) (actual time=723,842.293..723,842.322 rows=325 loops=1)

  • Sort Key: (count(DISTINCT ect.email_address)) DESC
  • Sort Method: quicksort Memory: 65kB
2. 173,477.682 723,841.834 ↓ 3.7 325 1

GroupAggregate (cost=6,413,557.57..6,413,559.57 rows=89 width=56) (actual time=520,549.438..723,841.834 rows=325 loops=1)

  • Group Key: ect.event_series_name, ecta.tag_code, ecta.tag_name
3. 333,296.791 550,364.152 ↓ 885,499.4 78,809,450 1

Sort (cost=6,413,557.57..6,413,557.79 rows=89 width=72) (actual time=520,549.025..550,364.152 rows=78,809,450 loops=1)

  • Sort Key: ect.event_series_name, ecta.tag_code, ecta.tag_name
  • Sort Method: external merge Disk: 6,015,880kB
4. 132,353.020 217,067.361 ↓ 885,499.4 78,809,450 1

Hash Join (cost=4,293,718.90..6,413,554.69 rows=89 width=72) (actual time=74,864.222..217,067.361 rows=78,809,450 loops=1)

  • Hash Cond: ((ecta.mdm_person_id)::text = (ect.mdm_person_id)::text)
5. 9,918.088 9,918.088 ↑ 1.0 47,472,635 1

Seq Scan on eloqua_contact_tag ecta (cost=0.00..1,941,789.56 rows=47,478,756 width=66) (actual time=0.020..9,918.088 rows=47,472,635 loops=1)

6. 1,550.389 74,796.253 ↓ 5,209,443.0 5,209,443 1

Hash (cost=4,293,718.89..4,293,718.89 rows=1 width=80) (actual time=74,796.253..74,796.253 rows=5,209,443 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 256 (originally 1) Memory Usage: 3,585kB
7. 2,071.019 73,245.864 ↓ 5,209,443.0 5,209,443 1

Merge Anti Join (cost=4,256,666.35..4,293,718.89 rows=1 width=80) (actual time=65,249.968..73,245.864 rows=5,209,443 loops=1)

  • Merge Cond: ((ect.email_address)::text = (e.email_address)::text)
8. 25,810.950 47,181.911 ↓ 1.0 5,394,992 1

Sort (cost=2,384,468.74..2,397,834.73 rows=5,346,397 width=80) (actual time=43,419.124..47,181.911 rows=5,394,992 loops=1)

  • Sort Key: ect.email_address
  • Sort Method: external merge Disk: 477,808kB
9. 21,370.961 21,370.961 ↓ 1.0 5,394,992 1

Seq Scan on eloqua_contact_transaction ect (cost=0.00..1,515,505.25 rows=5,346,397 width=80) (actual time=0.022..21,370.961 rows=5,394,992 loops=1)

  • Filter: ((market_code)::text = 'MC_MNFG'::text)
  • Rows Removed by Filter: 13,089,828
10. 9,853.327 23,992.934 ↓ 2.5 1,876,490 1

Sort (cost=1,872,197.61..1,874,082.84 rows=754,091 width=23) (actual time=21,830.834..23,992.934 rows=1,876,490 loops=1)

  • Sort Key: e.email_address
  • Sort Method: external merge Disk: 60,184kB
11. 14,139.607 14,139.607 ↓ 2.5 1,876,490 1

Seq Scan on eloqua_contact_preference e (cost=0.00..1,789,743.82 rows=754,091 width=23) (actual time=0.085..14,139.607 rows=1,876,490 loops=1)

  • Filter: (((email_group)::text = '136'::text) AND ((consent)::text = 'NO'::text))
  • Rows Removed by Filter: 52,051,963