explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FuJj

Settings
# exclusive inclusive rows x rows loops node
1. 0.045 85,706.619 ↓ 100.0 100 1

Limit (cost=18,194.6..18,194.61 rows=1 width=3,646) (actual time=85,706.568..85,706.619 rows=100 loops=1)

  • Buffers: shared hit=81408 read=2145 dirtied=2
2. 41.717 85,706.574 ↓ 100.0 100 1

Sort (cost=18,194.6..18,194.61 rows=1 width=3,646) (actual time=85,706.566..85,706.574 rows=100 loops=1)

  • Sort Key: creatives_adgroupcreativeset.date_created DESC
  • Sort Method: top-N heapsort Memory: 826kB
  • Buffers: shared hit=81408 read=2145 dirtied=2
3. 524.671 85,664.857 ↓ 2,883.0 2,883 1

Nested Loop (cost=17,644.8..18,194.59 rows=1 width=3,646) (actual time=154.112..85,664.857 rows=2,883 loops=1)

  • Buffers: shared hit=81405 read=2145 dirtied=2
4. 934.716 84,750.981 ↓ 2,883.0 2,883 1

Nested Loop (cost=17,644.8..18,022.03 rows=1 width=3,920) (actual time=151.848..84,750.981 rows=2,883 loops=1)

  • Buffers: shared hit=15337 read=2093 dirtied=2
5. 17.377 610.002 ↓ 2,883.0 2,883 1

Nested Loop (cost=8.46..85.58 rows=1 width=3,152) (actual time=8.384..610.002 rows=2,883 loops=1)

  • Buffers: shared hit=15331 read=990 dirtied=2
6. 11.020 563.795 ↓ 2,883.0 2,883 1

Nested Loop (cost=8.18..77.28 rows=1 width=1,944) (actual time=8.023..563.795 rows=2,883 loops=1)

  • Buffers: shared hit=6684 read=988 dirtied=2
7. 5.791 214.559 ↓ 1,262.0 1,262 1

Nested Loop (cost=7.76..76.67 rows=1 width=1,821) (actual time=7.293..214.559 rows=1,262 loops=1)

  • Buffers: shared hit=1029 read=539 dirtied=2
8. 0.577 14.379 ↓ 247.0 247 1

Nested Loop (cost=7.35..73.35 rows=1 width=845) (actual time=6.697..14.379 rows=247 loops=1)

  • Buffers: shared hit=23 read=115
9. 1.312 1.312 ↑ 1.0 1 1

Index Scan using applications_application_uuid_d3693c92_like on applications_application applications_application (cost=0.28..8.29 rows=1 width=149) (actual time=1.311..1.312 rows=1 loops=1)

  • Index Cond: ((uuid)::text = 'Zh3TFBK3MbHu6G5zVyNJ3G'::text)
  • Buffers: shared read=3
10. 0.944 12.490 ↓ 5.3 381 1

Nested Loop (cost=7.07..64.16 rows=72 width=696) (actual time=0.873..12.49 rows=381 loops=1)

  • Buffers: shared hit=23 read=112
11. 0.019 1.826 ↓ 12.0 12 1

Nested Loop (cost=4.56..26.08 rows=1 width=159) (actual time=0.812..1.826 rows=12 loops=1)

  • Buffers: shared hit=4 read=10
12. 0.440 0.481 ↑ 1.0 2 1

Bitmap Heap Scan on accounts_certificate accounts_certificate (cost=4.29..9.48 rows=2 width=4) (actual time=0.059..0.481 rows=2 loops=1)

  • Heap Blocks: exact=2
  • Buffers: shared read=4
13. 0.041 0.041 ↑ 1.0 2 1

Bitmap Index Scan on accounts_certificate_26b2345e (cost=0..4.29 rows=2 width=0) (actual time=0.041..0.041 rows=2 loops=1)

  • Index Cond: (organization_id = 267)
  • Buffers: shared read=2
14. 1.326 1.326 ↓ 6.0 6 2

Index Scan using accounts_adaccount_d5307900 on accounts_adaccount accounts_adaccount (cost=0.27..8.29 rows=1 width=159) (actual time=0.163..0.663 rows=6 loops=2)

  • Index Cond: (certificate_id = accounts_certificate.id)
  • Filter: (is_enabled AND is_enabled)
  • Buffers: shared hit=4 read=6
15. 9.420 9.720 ↑ 1.8 32 12

Bitmap Heap Scan on campaigns_campaign campaigns_campaign (cost=2.51..37.51 rows=56 width=537) (actual time=0.081..0.81 rows=32 loops=12)

  • Heap Blocks: exact=96
  • Buffers: shared hit=19 read=102
16. 0.300 0.300 ↑ 1.8 32 12

Bitmap Index Scan on campaigns_campaign_931b4fdf (cost=0..2.5 rows=56 width=0) (actual time=0.025..0.025 rows=32 loops=12)

  • Index Cond: (ad_account_id = accounts_adaccount.id)
  • Buffers: shared hit=15 read=10
17. 194.389 194.389 ↑ 1.2 5 247

Index Scan using campaigns_adgroup_f14acec3 on campaigns_adgroup campaigns_adgroup (cost=0.42..3.26 rows=6 width=976) (actual time=0.288..0.787 rows=5 loops=247)

  • Index Cond: (campaign_id = campaigns_campaign.id)
  • Buffers: shared hit=1006 read=424 dirtied=2
18. 338.216 338.216 ↑ 1.0 2 1,262

Index Scan using creatives_adgroupcreativeset_22ff94c4 on creatives_adgroupcreativeset creatives_adgroupcreativeset (cost=0.42..0.59 rows=2 width=123) (actual time=0.189..0.268 rows=2 loops=1,262)

  • Index Cond: (group_id = campaigns_adgroup.id)
  • Filter: ((date_removed IS NULL) AND (date_removed IS NULL))
  • Buffers: shared hit=5655 read=449
19. 28.830 28.830 ↑ 1.0 1 2,883

Index Scan using applications_applicationdetail_pkey on applications_applicationdetail applications_applicationdetail (cost=0.28..8.29 rows=1 width=1,208) (actual time=0.01..0.01 rows=1 loops=2,883)

  • Index Cond: (id = applications_application.actual_version_id)
  • Buffers: shared hit=8647 read=2
20. 81,949.275 83,206.263 ↓ 4.9 2,418 2,883

Aggregate (cost=17,636.34..17,925.33 rows=494 width=772) (actual time=0.059..28.861 rows=2,418 loops=2,883)

  • Buffers: shared hit=6 read=1103
21. 1,146.318 1,256.988 ↓ 4.9 2,418 2,883

Sort (cost=17,636.34..17,637.58 rows=494 width=580) (actual time=0.039..0.436 rows=2,418 loops=2,883)

  • Sort Key: (COALESCE(t191202_131811_cs_apple_data_7afbc6fcfd714775aa645aa2cc3fbcab.ad_group_creative_set_id, tracker_ad_group_creative_sets_data.ad_group_creative_set_id, t191202_131811_cs_cohorts_data_7afbc6fcfd714775aa645aa2cc3fbcab.ad_group_creative_set_id))
  • Sort Method: quicksort Memory: 477kB
  • Buffers: shared hit=6 read=1103
22. 1.026 110.670 ↓ 6.9 3,402 1

Hash Join (cost=17,595.55..17,614.24 rows=494 width=580) (actual time=108.483..110.67 rows=3,402 loops=1)

  • Buffers: shared hit=3 read=1103
23. 1.067 107.487 ↓ 6.9 3,402 1

Hash Join (cost=17,337.31..17,354.69 rows=494 width=424) (actual time=106.305..107.487 rows=3,402 loops=1)

  • Buffers: shared read=1084
24. 0.938 1.481 ↑ 2.6 193 1

Aggregate (cost=611.14..622.25 rows=494 width=164) (actual time=1.348..1.481 rows=193 loops=1)

  • Buffers: shared read=23
25. 0.319 0.543 ↓ 2.8 1,529 1

Bitmap Heap Scan on tracker_ad_group_creative_sets_data tracker_ad_group_creative_sets_data (cost=19.43..602.89 rows=550 width=35) (actual time=0.254..0.543 rows=1,529 loops=1)

  • Heap Blocks: exact=14
  • Buffers: shared read=23
26. 0.224 0.224 ↓ 2.8 1,529 1

Bitmap Index Scan on tracker_ad_group_creative_sets_data_org_day_key (cost=0..19.29 rows=550 width=0) (actual time=0.224..0.224 rows=1,529 loops=1)

  • Index Cond: ((organization_id = 267) AND (day_tap >= '2019-06-15 00:00:00'::timestamp without time zone) AND (day_tap <= '2019-07-15 23:59:59'::timestamp without time zone))
  • Buffers: shared read=9
27. 1.302 104.939 ↓ 17.0 3,401 1

Hash (cost=16,723.67..16,723.67 rows=200 width=260) (actual time=104.938..104.939 rows=3,401 loops=1)

  • Buffers: shared read=1061
28. 0.651 103.637 ↓ 17.0 3,401 1

Subquery Scan on t191202_131811_cs_apple_data_7afbc6fcfd714775aa645aa2cc3fbcab (cost=16,715.67..16,723.67 rows=200 width=260) (actual time=98.377..103.637 rows=3,401 loops=1)

  • Buffers: shared read=1061
29. 47.710 102.986 ↓ 17.0 3,401 1

Aggregate (cost=16,715.67..16,721.67 rows=200 width=260) (actual time=98.376..102.986 rows=3,401 loops=1)

  • Buffers: shared read=1061
30. 6.999 55.276 ↓ 6.4 61,440 1

Append (cost=0.42..16,498.59 rows=9,648 width=64) (actual time=3.857..55.276 rows=61,440 loops=1)

  • Buffers: shared read=1061
31. 1.637 1.637 ↓ 0.0 0 1

Index Scan using apple_ad_group_creative_sets_recent_new_org_day_key on apple_ad_group_creative_sets_recent apple_ad_group_creative_sets_recent (cost=0.42..8.44 rows=1 width=65) (actual time=1.637..1.637 rows=0 loops=1)

  • Index Cond: ((organization_id = 267) AND (day_tap >= '2019-06-15 00:00:00'::timestamp without time zone) AND (day_tap <= '2019-07-15 23:59:59'::timestamp without time zone))
  • Buffers: shared read=3
32. 46.640 46.640 ↓ 6.4 61,440 1

Index Scan using apple_ad_group_creative_sets_history_new_org_day_key on apple_ad_group_creative_sets_history apple_ad_group_creative_sets_history (cost=0.43..16,345.43 rows=9,647 width=64) (actual time=2.217..46.64 rows=61,440 loops=1)

  • Index Cond: ((organization_id = 267) AND (day_tap >= '2019-06-15 00:00:00'::timestamp without time zone) AND (day_tap <= '2019-07-15 23:59:59'::timestamp without time zone))
  • Buffers: shared read=1058
33. 0.065 2.157 ↓ 3.1 193 1

Hash (cost=257.46..257.46 rows=62 width=164) (actual time=2.157..2.157 rows=193 loops=1)

  • Buffers: shared hit=3 read=19
34. 0.036 2.092 ↓ 3.1 193 1

Subquery Scan on t191202_131811_cs_cohorts_data_7afbc6fcfd714775aa645aa2cc3fbcab (cost=251.72..257.46 rows=62 width=164) (actual time=1.106..2.092 rows=193 loops=1)

  • Buffers: shared hit=3 read=19
35. 0.359 2.056 ↓ 3.1 193 1

Aggregate (cost=251.72..256.84 rows=62 width=164) (actual time=1.105..2.056 rows=193 loops=1)

  • Buffers: shared hit=3 read=19
36. 0.498 1.697 ↓ 3.1 194 1

Aggregate (cost=251.72..253.12 rows=62 width=40) (actual time=1.095..1.697 rows=194 loops=1)

  • Buffers: shared hit=3 read=19
37. 0.591 1.199 ↓ 24.7 1,530 1

Sort (cost=251.72..251.88 rows=62 width=13) (actual time=1.085..1.199 rows=1,530 loops=1)

  • Sort Key: tracker_ad_group_creative_sets_revenue.ad_group_creative_set_id, tracker_ad_group_creative_sets_revenue.cell
  • Sort Method: quicksort Memory: 120kB
  • Buffers: shared hit=3 read=19
38. 0.382 0.608 ↓ 24.7 1,530 1

Bitmap Heap Scan on tracker_ad_group_creative_sets_revenue tracker_ad_group_creative_sets_revenue (cost=20.18..249.88 rows=62 width=13) (actual time=0.261..0.608 rows=1,530 loops=1)

  • Heap Blocks: exact=10
  • Buffers: shared hit=3 read=19
39. 0.226 0.226 ↓ 24.7 1,530 1

Bitmap Index Scan on tracker_ad_group_creative_sets_revenue_org_day_key_cell (cost=0..20.16 rows=62 width=0) (actual time=0.226..0.226 rows=1,530 loops=1)

  • Index Cond: ((organization_id = 267) AND (day_tap >= '2019-06-15 00:00:00'::timestamp without time zone) AND (day_tap <= '2019-07-15 23:59:59'::timestamp without time zone) AND (cell <= 30))
  • Buffers: shared hit=3 read=9
40. 389.205 389.205 ↑ 2.9 1,139 2,883

Seq Scan on creatives_creativeset creatives_creativeset (cost=0..131.12 rows=3,312 width=126) (actual time=0.008..0.135 rows=1,139 loops=2,883)

  • Buffers: shared hit=66068 read=52
Planning time : 45.948 ms
Execution time : 85,707.763 ms