explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g9EK

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 207,341.714 ↑ 1.0 25 1

Limit (cost=5,614,568.01..5,614,568.07 rows=25 width=158) (actual time=207,341.699..207,341.714 rows=25 loops=1)

2. 0.847 207,341.702 ↑ 8.0 25 1

Sort (cost=5,614,568.01..5,614,568.51 rows=200 width=158) (actual time=207,341.697..207,341.702 rows=25 loops=1)

  • Sort Key: ag_and_new.doy
  • Sort Method: top-N heapsort Memory: 28kB
3. 7,829.730 207,340.855 ↓ 8.9 1,783 1

HashAggregate (cost=5,614,549.37..5,614,562.37 rows=200 width=158) (actual time=207,334.402..207,340.855 rows=1,783 loops=1)

  • Group Key: ag_and_new.doy
4. 2,987.365 199,511.125 ↓ 179.8 3,782,781 1

Merge Left Join (cost=5,609,927.42..5,613,602.57 rows=21,040 width=158) (actual time=180,463.041..199,511.125 rows=3,782,781 loops=1)

  • Merge Cond: (ag_and_new.publisher_id = publisher_groups_assoc.publisher_id)
5. 1,747.902 195,673.374 ↓ 5,514.3 3,782,781 1

Nested Loop Left Join (cost=5,609,927.14..5,612,411.20 rows=686 width=166) (actual time=180,463.008..195,673.374 rows=3,782,781 loops=1)

6. 3,206.120 186,359.910 ↓ 5,514.3 3,782,781 1

Merge Left Join (cost=5,609,926.86..5,612,168.39 rows=686 width=174) (actual time=180,462.993..186,359.910 rows=3,782,781 loops=1)

  • Merge Cond: (ag_and_new.publisher_id = pub.id)
7. 8,207.337 182,328.382 ↓ 5,514.3 3,782,781 1

Sort (cost=5,609,663.02..5,609,664.74 rows=686 width=166) (actual time=180,460.720..182,328.382 rows=3,782,781 loops=1)

  • Sort Key: ag_and_new.publisher_id
  • Sort Method: external merge Disk: 346,680kB
8. 2,251.879 174,121.045 ↓ 5,514.3 3,782,781 1

Nested Loop Left Join (cost=5,605,557.24..5,609,630.71 rows=686 width=166) (actual time=119,046.128..174,121.045 rows=3,782,781 loops=1)

9. 5,871.911 164,303.604 ↓ 5,514.3 3,782,781 1

Nested Loop Left Join (cost=5,605,556.95..5,609,118.56 rows=686 width=161) (actual time=119,046.118..164,303.604 rows=3,782,781 loops=1)

10. 3,990.713 154,648.912 ↓ 5,514.3 3,782,781 1

Nested Loop Left Join (cost=5,605,556.67..5,608,557.80 rows=686 width=156) (actual time=119,046.108..154,648.912 rows=3,782,781 loops=1)

11. 2,872.524 143,092.637 ↓ 5,514.3 3,782,781 1

Nested Loop Left Join (cost=5,605,556.26..5,605,929.26 rows=686 width=140) (actual time=119,046.087..143,092.637 rows=3,782,781 loops=1)

12. 3,236.830 132,654.551 ↓ 5,514.3 3,782,781 1

Hash Join (cost=5,605,555.98..5,605,619.16 rows=686 width=144) (actual time=119,046.049..132,654.551 rows=3,782,781 loops=1)

  • Hash Cond: (ag_and_new.campaign_id = c.id)
13. 21,469.970 129,407.952 ↓ 5,380.4 3,787,829 1

HashAggregate (cost=5,604,151.23..5,604,191.71 rows=704 width=232) (actual time=119,036.263..129,407.952 rows=3,787,829 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
  • Filter: ((COALESCE((sum(ag_and_new.total_clicks))::bigint, 0::bigint) <> 0) OR (COALESCE((sum(ag_and_new.total_conversions))::bigint, 0::bigint) <> 0))
  • Rows Removed by Filter: 384,066
14. 2,301.458 107,937.982 ↓ 593.0 4,172,479 1

Subquery Scan on ag_and_new (cost=5,529,989.37..5,603,869.79 rows=7,036 width=232) (actual time=100,139.732..107,937.982 rows=4,172,479 loops=1)

  • Filter: ((ag_and_new.doy >= 20,140,101::bigint) AND (ag_and_new.doy < 20190925::bigint))
  • Rows Removed by Filter: 73
15. 2,325.850 105,636.524 ↓ 3.0 4,172,552 1

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

16. 14,274.892 103,310.674 ↓ 3.0 4,172,587 1

Sort (cost=5,529,989.37..5,533,507.49 rows=1,407,246 width=112) (actual time=100,139.712..103,310.674 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_tr (...)
  • Sort Method: external merge Disk: 367,152kB
17. 1,738.370 89,035.782 ↓ 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,255.949..89,035.782 rows=4,172,587 loops=1)

18. 26,231.238 87,253.324 ↓ 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,255.948..87,253.324 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_ (...)
19. 61,022.086 61,022.086 ↓ 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,255.907..61,022.086 rows=13,977,893 loops=1)

  • Sort Key: reports_transactions_rollup_daily.advertiser_id, reports_transactions_rollup_daily.publisher_id, reports_transactions_rollup_daily.publi (...)
  • Sort Method: external merge Disk: 1,291,440kB
  • -> Seq Scan on reports_transactions_rollup_daily (cost=0.00..347801.45 rows=13,976,445 width=112) (actual time=0.038..5887.688 rows=13,977,893 loops (...)
20. 0.422 44.088 ↑ 11.4 842 1

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

21. 5.230 43.666 ↑ 11.4 842 1

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

  • Group Key: engine_transactions_rollup_queue.advertiser_id, engine_transactions_rollup_queue.publisher_id, engine_transactions_rollup_queue.parent_ (...)
22. 4.463 38.436 ↑ 1.8 5,286 1

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

  • Sort Key: engine_transactions_rollup_queue.advertiser_id, engine_transactions_rollup_queue.publisher_id, engine_transactions_rollup_queue.pa (...)
  • Sort Method: quicksort Memory: 934kB
23. 33.973 33.973 ↑ 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.363..33.973 rows=5,286 loops=1)

24. 2.168 9.769 ↑ 1.1 7,249 1

Hash (cost=1,306.16..1,306.16 rows=7,887 width=16) (actual time=9.769..9.769 rows=7,249 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 340kB
25. 7.601 7.601 ↑ 1.1 7,249 1

Seq Scan on campaigns c (cost=0.00..1,306.16 rows=7,887 width=16) (actual time=0.006..7.601 rows=7,249 loops=1)

  • Filter: (currency_id = 1)
  • Rows Removed by Filter: 189
26. 7,565.562 7,565.562 ↑ 1.0 1 3,782,781

Index Scan using advertisers_pkey on advertisers a (cost=0.28..0.44 rows=1 width=12) (actual time=0.001..0.002 rows=1 loops=3,782,781)

  • Index Cond: (c.advertiser_id = id)
27. 7,565.562 7,565.562 ↑ 1.0 1 3,782,781

Index Scan using users_pkey on users au (cost=0.41..3.82 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=3,782,781)

  • Index Cond: (a.manager_id = id)
28. 3,782.781 3,782.781 ↑ 1.0 1 3,782,781

Index Scan using user_profiles_user_id_idx on user_profiles aup (cost=0.29..0.81 rows=1 width=21) (actual time=0.001..0.001 rows=1 loops=3,782,781)

  • Index Cond: (au.id = user_id)
29. 7,565.562 7,565.562 ↑ 1.0 1 3,782,781

Index Scan using advertiser_contact_pkey on contacts auc (cost=0.29..0.74 rows=1 width=21) (actual time=0.001..0.002 rows=1 loops=3,782,781)

  • Index Cond: (id = au.primary_contact_id)
30. 807.319 825.408 ↓ 356.5 3,789,440 1

Materialize (cost=263.83..2,468.50 rows=10,631 width=8) (actual time=2.266..825.408 rows=3,789,440 loops=1)

31. 5.688 18.089 ↑ 1.2 9,177 1

Merge Left Join (cost=263.83..2,441.92 rows=10,631 width=8) (actual time=2.260..18.089 rows=9,177 loops=1)

  • Merge Cond: (pub.id = publisher_channels.publisher_id)
32. 4.249 4.249 ↑ 1.2 9,177 1

Index Only Scan using users_pkey on users pub (cost=0.41..2,059.76 rows=10,631 width=8) (actual time=0.017..4.249 rows=9,177 loops=1)

  • Heap Fetches: 410
33. 1.004 8.152 ↑ 1.1 1,750 1

Materialize (cost=263.42..332.36 rows=1,858 width=8) (actual time=2.240..8.152 rows=1,750 loops=1)

34. 3.701 7.148 ↑ 1.1 1,750 1

GroupAggregate (cost=263.42..309.14 rows=1,858 width=16) (actual time=2.237..7.148 rows=1,750 loops=1)

  • Group Key: publisher_channels.publisher_id
35. 2.293 3.447 ↑ 1.0 2,994 1

Sort (cost=263.42..270.92 rows=2,999 width=16) (actual time=2.222..3.447 rows=2,994 loops=1)

  • Sort Key: publisher_channels.publisher_id
  • Sort Method: quicksort Memory: 237kB
36. 1.154 1.154 ↑ 1.0 2,994 1

Seq Scan on publisher_channels (cost=0.00..90.22 rows=2,999 width=16) (actual time=0.018..1.154 rows=2,994 loops=1)

  • Filter: (channel_type = 'secondary'::channel_publisher)
  • Rows Removed by Filter: 1,281
37. 7,565.562 7,565.562 ↑ 1.0 1 3,782,781

Index Scan using publisher_channels_publisher_id_idx on publisher_channels ppc (cost=0.28..0.34 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=3,782,781)

  • Index Cond: (pub.id = publisher_id)
  • Filter: (channel_type = 'primary'::channel_publisher)
  • Rows Removed by Filter: 1
38. 832.516 850.386 ↓ 609.1 3,736,154 1

Materialize (cost=0.29..889.39 rows=6,134 width=8) (actual time=0.031..850.386 rows=3,736,154 loops=1)

39. 12.479 17.870 ↑ 1.0 6,094 1

GroupAggregate (cost=0.29..812.71 rows=6,134 width=12) (actual time=0.028..17.870 rows=6,094 loops=1)

  • Group Key: publisher_groups_assoc.publisher_id
40. 5.391 5.391 ↑ 1.0 14,303 1

Index Only Scan using publisher_groups_assoc_pkey on publisher_groups_assoc (cost=0.29..511.09 rows=14,320 width=12) (actual time=0.019..5.391 rows=14,303 loops=1)

  • Heap Fetches: 825
Planning time : 2.112 ms
Execution time : 207,632.908 ms