explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M2e2

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 131,434.695 ↑ 1.0 1 1

Limit (cost=6,113,910.09..6,113,910.10 rows=1 width=4) (actual time=131,434.694..131,434.695 rows=1 loops=1)

2. 971.589 131,434.692 ↑ 1.0 1 1

Aggregate (cost=6,113,910.09..6,113,910.10 rows=1 width=4) (actual time=131,434.692..131,434.692 rows=1 loops=1)

3. 2,477.693 130,463.103 ↓ 29.6 4,171,947 1

Hash Left Join (cost=6,050,232.18..6,113,558.28 rows=140,725 width=4) (actual time=116,385.963..130,463.103 rows=4,171,947 loops=1)

  • Hash Cond: (ag_and_new.campaign_id = c.id)
4. 9,198.835 127,972.161 ↓ 29.6 4,171,947 1

GroupAggregate (cost=6,048,383.64..6,107,136.18 rows=140,725 width=232) (actual time=116,372.696..127,972.161 rows=4,171,947 loops=1)

  • Group Key: ag_and_new.advertiser_id, ag_and_new.publisher_id, ag_and_new.publisher_pid, ag_and_new.campaign_id, ag_and_new.doy
5. 11,284.826 118,773.326 ↓ 3.0 4,172,552 1

Sort (cost=6,048,383.64..6,051,901.75 rows=1,407,246 width=232) (actual time=116,372.663..118,773.326 rows=4,172,552 loops=1)

  • Sort Key: ag_and_new.advertiser_id, ag_and_new.publisher_id, ag_and_new.publisher_pid, ag_and_new.campaign_id, ag_and_new.doy
  • Sort Method: external merge Disk: 363,544kB
6. 2,224.459 107,488.500 ↓ 3.0 4,172,552 1

Subquery Scan on ag_and_new (cost=5,529,989.37..5,596,833.56 rows=1,407,246 width=232) (actual time=99,966.772..107,488.500 rows=4,172,552 loops=1)

7. 2,321.391 105,264.041 ↓ 3.0 4,172,552 1

Unique (cost=5,529,989.37..5,582,761.10 rows=1,407,246 width=112) (actual time=99,966.769..105,264.041 rows=4,172,552 loops=1)

8. 14,147.565 102,942.650 ↓ 3.0 4,172,587 1

Sort (cost=5,529,989.37..5,533,507.49 rows=1,407,246 width=112) (actual time=99,966.767..102,942.650 rows=4,172,587 loops=1)

  • Sort Key: reports_transactions_rollup_daily.publisher_id, reports_transactions_rollup_daily.publisher_pid, reports_transactions_rollup_daily.campaign_id, reports_transactions_rollup_daily.advertiser_id, ((sum(rep (...)
  • Sort Method: external merge Disk: 367,152kB
9. 1,695.403 88,795.085 ↓ 3.0 4,172,587 1

Append (cost=4,442,885.97..5,222,737.29 rows=1,407,246 width=112) (actual time=48,419.446..88,795.085 rows=4,172,587 loops=1)

10. 26,023.608 87,054.149 ↓ 3.0 4,171,745 1

GroupAggregate (cost=4,442,885.97..5,201,108.13 rows=1,397,645 width=112) (actual time=48,419.444..87,054.149 rows=4,171,745 loops=1)

  • Group Key: reports_transactions_rollup_daily.advertiser_id, reports_transactions_rollup_daily.publisher_id, reports_transactions_rollup_daily.publisher_pid, reports_transactions_rollup_daily.campaign_ (...)
11. 55,172.759 61,030.541 ↓ 1.0 13,977,893 1

Sort (cost=4,442,885.97..4,477,827.08 rows=13,976,445 width=112) (actual time=48,419.402..61,030.541 rows=13,977,893 loops=1)

  • Sort Key: reports_transactions_rollup_daily.advertiser_id, reports_transactions_rollup_daily.publisher_id, reports_transactions_rollup_daily.publisher_pid, reports_transactions_rollup_daily.camp (...)
  • Sort Method: external merge Disk: 1,291,440kB
12. 5,857.782 5,857.782 ↓ 1.0 13,977,893 1

Seq Scan on reports_transactions_rollup_daily (cost=0.00..347,801.45 rows=13,976,445 width=112) (actual time=0.025..5,857.782 rows=13,977,893 loops=1)

13. 0.370 45.533 ↑ 11.4 842 1

Subquery Scan on *SELECT* 2 (cost=4,050.98..7,652.71 rows=9,601 width=66) (actual time=38.716..45.533 rows=842 loops=1)

14. 5.348 45.163 ↑ 11.4 842 1

GroupAggregate (cost=4,050.98..7,556.70 rows=9,601 width=66) (actual time=38.712..45.163 rows=842 loops=1)

  • Group Key: engine_transactions_rollup_queue.advertiser_id, engine_transactions_rollup_queue.publisher_id, engine_transactions_rollup_queue.parent_publisher_id, engine_transactions_rollup_queue.c (...)
15. 4.614 39.815 ↑ 1.8 5,286 1

Sort (cost=4,050.98..4,075.04 rows=9,621 width=66) (actual time=38.685..39.815 rows=5,286 loops=1)

  • Sort Key: engine_transactions_rollup_queue.advertiser_id, engine_transactions_rollup_queue.publisher_id, engine_transactions_rollup_queue.parent_publisher_id, engine_transactions_rollup_qu (...)
  • Sort Method: quicksort Memory: 934kB
16. 35.201 35.201 ↑ 1.8 5,286 1

Seq Scan on engine_transactions_rollup_queue (cost=0.00..3,414.46 rows=9,621 width=66) (actual time=0.371..35.201 rows=5,286 loops=1)

17. 2.670 13.249 ↑ 1.1 7,438 1

Hash (cost=1,747.38..1,747.38 rows=8,093 width=12) (actual time=13.249..13.249 rows=7,438 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 320kB
18. 5.313 10.579 ↑ 1.1 7,438 1

Hash Left Join (cost=329.94..1,747.38 rows=8,093 width=12) (actual time=2.570..10.579 rows=7,438 loops=1)

  • Hash Cond: (c.advertiser_id = a.id)
19. 2.714 2.714 ↑ 1.1 7,438 1

Seq Scan on campaigns c (cost=0.00..1,285.93 rows=8,093 width=16) (actual time=0.008..2.714 rows=7,438 loops=1)

20. 1.008 2.552 ↑ 1.0 3,375 1

Hash (cost=287.75..287.75 rows=3,375 width=12) (actual time=2.552..2.552 rows=3,375 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 155kB
21. 1.544 1.544 ↑ 1.0 3,375 1

Seq Scan on advertisers a (cost=0.00..287.75 rows=3,375 width=12) (actual time=0.004..1.544 rows=3,375 loops=1)

Planning time : 0.744 ms
Execution time : 131,713.507 ms