explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QQfb

Settings
# exclusive inclusive rows x rows loops node
1. 0.484 723,705.937 ↓ 3.9 325 1

Sort (cost=6,389,931.56..6,389,931.77 rows=84 width=56) (actual time=723,705.905..723,705.937 rows=325 loops=1)

  • Sort Key: (count(DISTINCT ect.email_address)) DESC
  • Sort Method: quicksort Memory: 65kB
2. 174,524.505 723,705.453 ↓ 3.9 325 1

GroupAggregate (cost=6,389,926.98..6,389,928.87 rows=84 width=56) (actual time=519,320.587..723,705.453 rows=325 loops=1)

  • Group Key: ect.event_series_name, ecta.tag_code, ecta.tag_name
3. 333,935.599 549,180.948 ↓ 938,202.9 78,809,047 1

Sort (cost=6,389,926.98..6,389,927.19 rows=84 width=72) (actual time=519,320.052..549,180.948 rows=78,809,047 loops=1)

  • Sort Key: ect.event_series_name, ecta.tag_code, ecta.tag_name
  • Sort Method: external merge Disk: 6,015,856kB
4. 133,423.676 215,245.349 ↓ 938,202.9 78,809,047 1

Hash Join (cost=4,270,239.67..6,389,924.30 rows=84 width=72) (actual time=72,640.136..215,245.349 rows=78,809,047 loops=1)

  • Hash Cond: ((ecta.mdm_person_id)::text = (ect.mdm_person_id)::text)
5. 9,247.432 9,247.432 ↑ 1.0 47,471,112 1

Seq Scan on eloqua_contact_tag ecta (cost=0.00..1,941,667.12 rows=47,471,112 width=66) (actual time=0.020..9,247.432 rows=47,471,112 loops=1)

6. 1,541.481 72,574.241 ↓ 5,209,432.0 5,209,432 1

Hash (cost=4,270,239.66..4,270,239.66 rows=1 width=80) (actual time=72,574.241..72,574.241 rows=5,209,432 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 256 (originally 1) Memory Usage: 3,585kB
7. 2,066.328 71,032.760 ↓ 5,209,432.0 5,209,432 1

Merge Anti Join (cost=4,233,977.15..4,270,239.66 rows=1 width=80) (actual time=62,984.883..71,032.760 rows=5,209,432 loops=1)

  • Merge Cond: ((ect.email_address)::text = (e.email_address)::text)
8. 25,746.245 45,421.674 ↓ 1.0 5,394,981 1

Sort (cost=2,352,650.45..2,365,556.89 rows=5,162,579 width=80) (actual time=41,631.271..45,421.674 rows=5,394,981 loops=1)

  • Sort Key: ect.email_address
  • Sort Method: external merge Disk: 477,808kB
9. 19,675.429 19,675.429 ↓ 1.0 5,394,981 1

Seq Scan on eloqua_contact_transaction ect (cost=0.00..1,514,867.65 rows=5,162,579 width=80) (actual time=0.007..19,675.429 rows=5,394,981 loops=1)

  • Filter: ((market_code)::text = 'MC_MNFG'::text)
  • Rows Removed by Filter: 13,084,912
10. 9,799.596 23,544.758 ↓ 2.4 1,876,490 1

Sort (cost=1,881,326.32..1,883,266.35 rows=776,015 width=23) (actual time=21,353.605..23,544.758 rows=1,876,490 loops=1)

  • Sort Key: e.email_address
  • Sort Method: external merge Disk: 60,184kB
11. 13,745.162 13,745.162 ↓ 2.4 1,876,490 1

Seq Scan on eloqua_contact_preference e (cost=0.00..1,796,315.83 rows=776,015 width=23) (actual time=128.906..13,745.162 rows=1,876,490 loops=1)

  • Filter: (((email_group)::text = '136'::text) AND ((consent)::text = 'NO'::text))
  • Rows Removed by Filter: 52,051,493
Planning time : 13.843 ms
Execution time : 724,443.262 ms