explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GPM3

Settings
# exclusive inclusive rows x rows loops node
1. 0.616 1,720,657.360 ↓ 336.0 336 1

Sort (cost=3,672,216.26..3,672,216.26 rows=1 width=56) (actual time=1,720,657.324..1,720,657.360 rows=336 loops=1)

  • Sort Key: (count(DISTINCT ect.email_address)) DESC
  • Sort Method: quicksort Memory: 66kB
2. 161,720.350 1,720,656.744 ↓ 336.0 336 1

GroupAggregate (cost=3,672,216.22..3,672,216.25 rows=1 width=56) (actual time=1,534,375.473..1,720,656.744 rows=336 loops=1)

  • Group Key: ect.event_series_name, ecta.tag_code, ecta.tag_name
3. 391,841.764 1,558,936.394 ↓ 85,241,636.0 85,241,636 1

Sort (cost=3,672,216.22..3,672,216.23 rows=1 width=72) (actual time=1,534,375.434..1,558,936.394 rows=85,241,636 loops=1)

  • Sort Key: ect.event_series_name, ecta.tag_code, ecta.tag_name
  • Sort Method: external merge Disk: 6,506,976kB
4. 15,585.494 1,167,094.630 ↓ 85,241,636.0 85,241,636 1

Nested Loop (cost=1,904,352.22..3,672,216.21 rows=1 width=72) (actual time=32,979.329..1,167,094.630 rows=85,241,636 loops=1)

5. 3,252.273 183,766.898 ↓ 5,561,737.0 5,561,737 1

Merge Anti Join (cost=1,904,351.53..3,672,210.73 rows=1 width=80) (actual time=32,972.891..183,766.898 rows=5,561,737 loops=1)

  • Merge Cond: ((ect.email_address)::text = (e.email_address)::text)
6. 144,615.801 144,615.801 ↓ 1.1 5,747,596 1

Index Scan using idx_eloqua_contact_transaction_email_address on eloqua_contact_transaction ect (cost=0.56..1,743,712.65 rows=5,453,392 width=80) (actual time=8.946..144,615.801 rows=5,747,596 loops=1)

  • Filter: ((market_code)::text = 'MC_MNFG'::text)
  • Rows Removed by Filter: 13,104,536
7. 12,980.476 35,898.824 ↓ 2.4 1,876,490 1

Sort (cost=1,904,350.96..1,906,267.19 rows=766,490 width=23) (actual time=32,963.937..35,898.824 rows=1,876,490 loops=1)

  • Sort Key: e.email_address
  • Sort Method: external merge Disk: 60,192kB
8. 22,918.348 22,918.348 ↓ 2.4 1,876,490 1

Seq Scan on eloqua_contact_preference e (cost=0.00..1,820,450.58 rows=766,490 width=23) (actual time=1,340.185..22,918.348 rows=1,876,490 loops=1)

  • Filter: (((email_group)::text = '136'::text) AND ((consent)::text = 'NO'::text))
  • Rows Removed by Filter: 52,258,591
9. 967,742.238 967,742.238 ↑ 10.7 15 5,561,737

Index Only Scan using idx_eloqua_contact_tag_mdm_tag_code_tag_name on eloqua_contact_tag ecta (cost=0.69..3.88 rows=161 width=66) (actual time=0.112..0.174 rows=15 loops=5,561,737)

  • Index Cond: (mdm_person_id = (ect.mdm_person_id)::text)
  • Heap Fetches: 85,241,636
Planning time : 37.440 ms
Execution time : 1,721,453.611 ms