explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R7v5

Settings
# exclusive inclusive rows x rows loops node
1. 0.483 615,384.927 ↓ 325.0 325 1

Sort (cost=4,288,506.86..4,288,506.86 rows=1 width=56) (actual time=615,384.898..615,384.927 rows=325 loops=1)

  • Sort Key: (count(DISTINCT ect.email_address)) DESC
  • Sort Method: quicksort Memory: 65kB
2. 146,708.845 615,384.444 ↓ 325.0 325 1

GroupAggregate (cost=4,288,506.82..4,288,506.85 rows=1 width=56) (actual time=439,205.178..615,384.444 rows=325 loops=1)

  • Group Key: ect.event_series_name, ecta.tag_code, ecta.tag_name
3. 329,618.855 468,675.599 ↓ 78,834,045.0 78,834,045 1

Sort (cost=4,288,506.82..4,288,506.83 rows=1 width=72) (actual time=439,205.139..468,675.599 rows=78,834,045 loops=1)

  • Sort Key: ect.event_series_name, ecta.tag_code, ecta.tag_name
  • Sort Method: external merge Disk: 6,017,624kB
4. 10,195.634 139,056.744 ↓ 78,834,045.0 78,834,045 1

Nested Loop (cost=4,251,122.28..4,288,506.81 rows=1 width=72) (actual time=50,214.768..139,056.744 rows=78,834,045 loops=1)

5. 2,291.409 59,483.464 ↓ 5,336,742.0 5,336,742 1

Merge Anti Join (cost=4,251,121.59..4,288,441.36 rows=1 width=80) (actual time=50,214.727..59,483.464 rows=5,336,742 loops=1)

  • Merge Cond: ((ect.email_address)::text = (e.email_address)::text)
6. 25,686.936 31,800.800 ↓ 1.0 5,522,300 1

Sort (cost=2,360,353.88..2,373,813.24 rows=5,383,744 width=80) (actual time=27,093.900..31,800.800 rows=5,522,300 loops=1)

  • Sort Key: ect.email_address
  • Sort Method: external merge Disk: 489,488kB
7. 5,617.431 6,113.864 ↓ 1.0 5,522,300 1

Bitmap Heap Scan on eloqua_contact_transaction ect (cost=123,369.70..1,485,050.50 rows=5,383,744 width=80) (actual time=504.433..6,113.864 rows=5,522,300 loops=1)

  • Recheck Cond: ((market_code)::text = 'MC_MNFG'::text)
  • Rows Removed by Index Recheck: 7,515,135
  • Heap Blocks: exact=39,166 lossy=893,087
8. 496.433 496.433 ↓ 1.0 5,522,503 1

Bitmap Index Scan on idx_eloqua_contact_transaction_mkt_code_mdm_email_series_name (cost=0.00..122,023.77 rows=5,383,744 width=0) (actual time=496.433..496.433 rows=5,522,503 loops=1)

  • Index Cond: ((market_code)::text = 'MC_MNFG'::text)
9. 9,838.338 25,391.255 ↓ 2.5 1,876,490 1

Sort (cost=1,890,767.71..1,892,670.31 rows=761,039 width=23) (actual time=23,120.819..25,391.255 rows=1,876,490 loops=1)

  • Sort Key: e.email_address
  • Sort Method: external merge Disk: 60,192kB
10. 15,552.917 15,552.917 ↓ 2.5 1,876,490 1

Seq Scan on eloqua_contact_preference e (cost=0.00..1,807,503.29 rows=761,039 width=23) (actual time=0.925..15,552.917 rows=1,876,490 loops=1)

  • Filter: (((email_group)::text = '136'::text) AND ((consent)::text = 'NO'::text))
  • Rows Removed by Filter: 52,058,105
11. 69,377.646 69,377.646 ↑ 10.7 15 5,336,742

Index Only Scan using idx_eloqua_contact_tag_mdm_tag_code_tag_name on eloqua_contact_tag ecta (cost=0.69..63.85 rows=160 width=66) (actual time=0.009..0.013 rows=15 loops=5,336,742)

  • Index Cond: (mdm_person_id = (ect.mdm_person_id)::text)
  • Heap Fetches: 78,834,045