explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T3v5

Settings
# exclusive inclusive rows x rows loops node
1. 2.508 64,489.631 ↓ 356.0 356 1

Sort (cost=5,815.97..5,815.97 rows=1 width=2,450) (actual time=64,489.585..64,489.631 rows=356 loops=1)

  • Sort Key: vimb_mediaplan.xid NULLS FIRST
  • Sort Method: quicksort Memory: 1348kB
2.          

Initplan (forSort)

3. 0.176 2.956 ↑ 1.0 1 1

Aggregate (cost=323.70..323.71 rows=1 width=32) (actual time=2.956..2.956 rows=1 loops=1)

4. 0.389 2.780 ↓ 375.8 1,503 1

Hash Left Join (cost=248.73..323.69 rows=4 width=8) (actual time=1.309..2.780 rows=1,503 loops=1)

  • Hash Cond: (account_6.parent_id = account_7.id)
  • Filter: ((account.id = 2000001) OR (account_5.id = 2000001) OR (account_6.id = 2000001) OR (account_7.id = 2000001))
  • Rows Removed by Filter: 15
5. 0.365 1.988 ↑ 1.0 1,518 1

Hash Left Join (cost=165.82..236.77 rows=1,518 width=32) (actual time=0.902..1.988 rows=1,518 loops=1)

  • Hash Cond: (account_5.parent_id = account_6.id)
6. 0.342 1.181 ↑ 1.0 1,518 1

Hash Left Join (cost=82.91..149.87 rows=1,518 width=24) (actual time=0.457..1.181 rows=1,518 loops=1)

  • Hash Cond: (account.parent_id = account_5.id)
7. 0.390 0.390 ↑ 1.0 1,518 1

Seq Scan on account (cost=0.00..62.96 rows=1,518 width=16) (actual time=0.003..0.390 rows=1,518 loops=1)

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 78
8. 0.214 0.449 ↑ 1.0 1,596 1

Hash (cost=62.96..62.96 rows=1,596 width=16) (actual time=0.449..0.449 rows=1,596 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 91kB
9. 0.235 0.235 ↑ 1.0 1,596 1

Seq Scan on account account_5 (cost=0.00..62.96 rows=1,596 width=16) (actual time=0.002..0.235 rows=1,596 loops=1)

10. 0.215 0.442 ↑ 1.0 1,596 1

Hash (cost=62.96..62.96 rows=1,596 width=16) (actual time=0.442..0.442 rows=1,596 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 91kB
11. 0.227 0.227 ↑ 1.0 1,596 1

Seq Scan on account account_6 (cost=0.00..62.96 rows=1,596 width=16) (actual time=0.002..0.227 rows=1,596 loops=1)

12. 0.199 0.403 ↑ 1.0 1,596 1

Hash (cost=62.96..62.96 rows=1,596 width=8) (actual time=0.403..0.403 rows=1,596 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 79kB
13. 0.204 0.204 ↑ 1.0 1,596 1

Seq Scan on account account_7 (cost=0.00..62.96 rows=1,596 width=8) (actual time=0.002..0.204 rows=1,596 loops=1)

14. 58.447 64,484.167 ↓ 356.0 356 1

Nested Loop Left Join (cost=3,159.38..5,492.25 rows=1 width=2,450) (actual time=3,280.973..64,484.167 rows=356 loops=1)

  • Join Filter: (vimb_mediaplan_1.id = vimb_mediaplan.id)
  • Rows Removed by Join Filter: 410904
15. 2.038 5,246.416 ↓ 356.0 356 1

Nested Loop (cost=623.02..2,704.10 rows=1 width=2,210) (actual time=2,975.325..5,246.416 rows=356 loops=1)

16. 6.364 5,237.258 ↓ 356.0 356 1

Nested Loop (cost=623.02..2,700.74 rows=1 width=2,152) (actual time=2,974.761..5,237.258 rows=356 loops=1)

  • Join Filter: (vimb_section.id = vimb_position.section_id)
  • Rows Removed by Join Filter: 39016
17. 0.009 1.478 ↓ 4.0 4 1

Nested Loop (cost=0.27..21.89 rows=1 width=218) (actual time=0.054..1.478 rows=4 loops=1)

18. 1.093 1.093 ↓ 4.0 4 1

Seq Scan on vimb_site (cost=0.00..13.59 rows=1 width=143) (actual time=0.036..1.093 rows=4 loops=1)

  • Filter: ((deleted IS FALSE) AND is_visible AND (owner_id = 91) AND ((network)::text = 'VIDEO'::text))
  • Rows Removed by Filter: 369
19. 0.376 0.376 ↑ 1.0 1 4

Index Scan using vimb_section_site_id_fkey_idx on vimb_section (cost=0.27..8.29 rows=1 width=75) (actual time=0.092..0.094 rows=1 loops=4)

  • Index Cond: (site_id = vimb_site.id)
20. 56.536 5,229.416 ↓ 95.6 9,843 4

Hash Join (cost=622.75..2,677.55 rows=103 width=1,934) (actual time=288.351..1,307.354 rows=9,843 loops=4)

  • Hash Cond: (project.account_id = vimb_advertiser.id)
21. 59.244 5,172.380 ↓ 95.6 9,843 4

Nested Loop (cost=539.84..2,594.37 rows=103 width=1,742) (actual time=288.219..1,293.095 rows=9,843 loops=4)

22. 65.564 5,073.764 ↓ 94.6 9,843 4

Nested Loop (cost=539.57..2,563.83 rows=104 width=1,646) (actual time=288.209..1,268.441 rows=9,843 loops=4)

23. 30.640 4,968.828 ↓ 90.3 9,843 4

Nested Loop (cost=539.29..2,530.97 rows=109 width=1,531) (actual time=288.199..1,242.207 rows=9,843 loops=4)

24. 175.740 4,859.444 ↓ 82.7 9,843 4

Nested Loop (cost=539.01..2,495.01 rows=119 width=1,394) (actual time=288.188..1,214.861 rows=9,843 loops=4)

25. 273.876 1,353.404 ↓ 130.0 55,505 4

Nested Loop (cost=538.58..1,434.18 rows=427 width=919) (actual time=22.741..338.351 rows=55,505 loops=4)

  • Join Filter: (project.id = vimb_mediaplan.project_id)
26. 148.932 269.384 ↓ 127.9 25,317 4

Hash Join (cost=538.29..1,202.51 rows=198 width=330) (actual time=22.692..67.346 rows=25,317 loops=4)

  • Hash Cond: (vimb_order.project_id = project.id)
27. 30.064 30.064 ↑ 1.0 25,319 4

Seq Scan on vimb_order (cost=0.00..597.74 rows=25,319 width=66) (actual time=0.040..7.516 rows=25,319 loops=4)

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 355
28. 25.612 90.388 ↓ 126.0 25,318 4

Hash (cost=535.78..535.78 rows=201 width=264) (actual time=22.597..22.597 rows=25,318 loops=4)

  • Buckets: 16384 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3969kB
29. 48.508 64.776 ↓ 126.0 25,318 4

Bitmap Heap Scan on project (cost=44.93..535.78 rows=201 width=264) (actual time=4.223..16.194 rows=25,318 loops=4)

  • Recheck Cond: (account_id = ANY ($0))
  • Filter: (deleted IS FALSE)
  • Heap Blocks: exact=3716
30. 16.268 16.268 ↓ 126.0 25,318 4

Bitmap Index Scan on project_account_id_deleted_fkey_idx (cost=0.00..44.88 rows=201 width=0) (actual time=4.066..4.067 rows=25,318 loops=4)

  • Index Cond: ((account_id = ANY ($0)) AND (deleted = false))
31. 810.144 810.144 ↑ 1.5 2 101,268

Index Scan using vimb_mediaplan_project_id_fkey_idx on vimb_mediaplan (cost=0.29..1.13 rows=3 width=589) (actual time=0.005..0.008 rows=2 loops=101,268)

  • Index Cond: (project_id = vimb_order.project_id)
  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 0
32. 3,330.300 3,330.300 ↓ 0.0 0 222,020

Index Scan using vimb_fragment_mediaplan_id_deleted_fkey_idx on vimb_fragment (cost=0.42..2.47 rows=1 width=475) (actual time=0.015..0.015 rows=0 loops=222,020)

  • Index Cond: ((mediaplan_id = vimb_mediaplan.id) AND (deleted = false))
  • Filter: ((deleted IS FALSE) AND (start_date <= '2018-12-18'::date) AND (end_date >= '2018-12-01'::date))
  • Rows Removed by Filter: 5
33. 78.744 78.744 ↑ 1.0 1 39,372

Index Scan using vimb_position_pkey on vimb_position (cost=0.28..0.30 rows=1 width=137) (actual time=0.002..0.002 rows=1 loops=39,372)

  • Index Cond: (id = vimb_fragment.position_id)
  • Filter: (deleted IS FALSE)
34. 39.372 39.372 ↑ 1.0 1 39,372

Index Scan using vimb_product_pkey on vimb_product (cost=0.28..0.30 rows=1 width=115) (actual time=0.001..0.001 rows=1 loops=39,372)

  • Index Cond: (id = vimb_mediaplan.product_id)
  • Filter: (deleted IS FALSE)
35. 39.372 39.372 ↑ 1.0 1 39,372

Index Scan using advertiser_pkey on advertiser (cost=0.28..0.29 rows=1 width=96) (actual time=0.001..0.001 rows=1 loops=39,372)

  • Index Cond: (id = vimb_mediaplan.advertiser_id)
  • Filter: (deleted IS FALSE)
36. 0.208 0.500 ↑ 1.0 1,596 1

Hash (cost=62.96..62.96 rows=1,596 width=192) (actual time=0.500..0.500 rows=1,596 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 384kB
37. 0.292 0.292 ↑ 1.0 1,596 1

Seq Scan on account vimb_advertiser (cost=0.00..62.96 rows=1,596 width=192) (actual time=0.007..0.292 rows=1,596 loops=1)

38. 7.120 7.120 ↑ 1.0 1 356

Seq Scan on vimb_site_owner (cost=0.00..3.35 rows=1 width=58) (actual time=0.010..0.020 rows=1 loops=356)

  • Filter: ((deleted IS FALSE) AND (id = 91))
  • Rows Removed by Filter: 107
39. 58,780.489 59,179.304 ↓ 15.0 1,155 356

GroupAggregate (cost=2,536.36..2,786.42 rows=77 width=232) (actual time=0.647..166.234 rows=1,155 loops=356)

  • Group Key: vimb_mediaplan_1.id
40.          

Initplan (forGroupAggregate)

41. 0.235 3.655 ↑ 1.0 1 1

Aggregate (cost=323.70..323.71 rows=1 width=32) (actual time=3.655..3.655 rows=1 loops=1)

42. 0.469 3.420 ↓ 375.8 1,503 1

Hash Left Join (cost=248.73..323.69 rows=4 width=8) (actual time=1.649..3.420 rows=1,503 loops=1)

  • Hash Cond: (account_2.parent_id = account_3.id)
  • Filter: ((account_1.id = 2000001) OR (account_1_1.id = 2000001) OR (account_2.id = 2000001) OR (account_3.id = 2000001))
  • Rows Removed by Filter: 15
43. 0.414 2.473 ↑ 1.0 1,518 1

Hash Left Join (cost=165.82..236.77 rows=1,518 width=32) (actual time=1.155..2.473 rows=1,518 loops=1)

  • Hash Cond: (account_1_1.parent_id = account_2.id)
44. 0.449 1.550 ↑ 1.0 1,518 1

Hash Left Join (cost=82.91..149.87 rows=1,518 width=24) (actual time=0.641..1.550 rows=1,518 loops=1)

  • Hash Cond: (account_1.parent_id = account_1_1.id)
45. 0.475 0.475 ↑ 1.0 1,518 1

Seq Scan on account account_1 (cost=0.00..62.96 rows=1,518 width=16) (actual time=0.011..0.475 rows=1,518 loops=1)

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 78
46. 0.245 0.626 ↑ 1.0 1,596 1

Hash (cost=62.96..62.96 rows=1,596 width=16) (actual time=0.626..0.626 rows=1,596 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 91kB
47. 0.381 0.381 ↑ 1.0 1,596 1

Seq Scan on account account_1_1 (cost=0.00..62.96 rows=1,596 width=16) (actual time=0.002..0.381 rows=1,596 loops=1)

48. 0.249 0.509 ↑ 1.0 1,596 1

Hash (cost=62.96..62.96 rows=1,596 width=16) (actual time=0.509..0.509 rows=1,596 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 91kB
49. 0.260 0.260 ↑ 1.0 1,596 1

Seq Scan on account account_2 (cost=0.00..62.96 rows=1,596 width=16) (actual time=0.002..0.260 rows=1,596 loops=1)

50. 0.256 0.478 ↑ 1.0 1,596 1

Hash (cost=62.96..62.96 rows=1,596 width=8) (actual time=0.478..0.478 rows=1,596 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 79kB
51. 0.222 0.222 ↑ 1.0 1,596 1

Seq Scan on account account_3 (cost=0.00..62.96 rows=1,596 width=8) (actual time=0.003..0.222 rows=1,596 loops=1)

52. 195.077 395.160 ↓ 63.3 4,876 356

Sort (cost=2,212.65..2,212.84 rows=77 width=130) (actual time=0.575..1.110 rows=4,876 loops=356)

  • Sort Key: vimb_mediaplan_1.id
  • Sort Method: quicksort Memory: 1852kB
53. 3.564 200.083 ↓ 133.7 10,294 1

Hash Join (cost=577.77..2,210.24 rows=77 width=130) (actual time=45.074..200.083 rows=10,294 loops=1)

  • Hash Cond: (vimb_site_1.owner_id = vimb_site_owner_1.id)
54. 4.075 196.481 ↓ 100.9 10,294 1

Hash Join (cost=573.66..2,205.85 rows=102 width=138) (actual time=45.028..196.481 rows=10,294 loops=1)

  • Hash Cond: (vimb_section_1.site_id = vimb_site_1.id)
55. 3.968 192.229 ↓ 87.2 10,294 1

Hash Join (cost=557.88..2,189.76 rows=118 width=116) (actual time=44.846..192.229 rows=10,294 loops=1)

  • Hash Cond: (vimb_position_1.section_id = vimb_section_1.id)
56. 8.240 188.072 ↓ 80.4 10,294 1

Nested Loop (cost=539.84..2,171.39 rows=128 width=116) (actual time=44.652..188.072 rows=10,294 loops=1)

57. 5.039 169.538 ↓ 73.5 10,294 1

Nested Loop (cost=539.57..2,130.02 rows=140 width=116) (actual time=44.640..169.538 rows=10,294 loops=1)

58. 2.153 139.738 ↓ 75.0 2,251 1

Nested Loop (cost=539.14..1,487.85 rows=30 width=9) (actual time=44.611..139.738 rows=2,251 loops=1)

59. 2.349 135.334 ↓ 70.3 2,251 1

Nested Loop (cost=538.86..1,444.02 rows=32 width=17) (actual time=44.586..135.334 rows=2,251 loops=1)

60. 15.353 130.734 ↓ 66.2 2,251 1

Nested Loop (cost=538.58..1,432.20 rows=34 width=24) (actual time=44.574..130.734 rows=2,251 loops=1)

  • Join Filter: (project_1.id = vimb_mediaplan_1.project_id)
61. 10.316 39.430 ↓ 127.9 25,317 1

Hash Join (cost=538.29..1,202.51 rows=198 width=24) (actual time=24.478..39.430 rows=25,317 loops=1)

  • Hash Cond: (vimb_order_1.project_id = project_1.id)
62. 4.652 4.652 ↑ 1.0 25,319 1

Seq Scan on vimb_order vimb_order_1 (cost=0.00..597.74 rows=25,319 width=8) (actual time=0.007..4.652 rows=25,319 loops=1)

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 355
63. 4.593 24.462 ↓ 126.0 25,318 1

Hash (cost=535.78..535.78 rows=201 width=16) (actual time=24.462..24.462 rows=25,318 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1443kB
64. 12.437 19.869 ↓ 126.0 25,318 1

Bitmap Heap Scan on project project_1 (cost=44.93..535.78 rows=201 width=16) (actual time=7.568..19.869 rows=25,318 loops=1)

  • Recheck Cond: (account_id = ANY ($1))
  • Filter: (deleted IS FALSE)
  • Heap Blocks: exact=929
65. 7.432 7.432 ↓ 126.0 25,318 1

Bitmap Index Scan on project_account_id_deleted_fkey_idx (cost=0.00..44.88 rows=201 width=0) (actual time=7.432..7.432 rows=25,318 loops=1)

  • Index Cond: ((account_id = ANY ($1)) AND (deleted = false))
66. 75.951 75.951 ↓ 0.0 0 25,317

Index Scan using vimb_mediaplan_project_id_fkey_idx on vimb_mediaplan vimb_mediaplan_1 (cost=0.29..1.15 rows=1 width=24) (actual time=0.003..0.003 rows=0 loops=25,317)

  • Index Cond: (project_id = vimb_order_1.project_id)
  • Filter: ((deleted IS FALSE) AND (end_date >= '2018-12-01'::date) AND (start_date <= '2018-12-18'::date))
  • Rows Removed by Filter: 3
67. 2.251 2.251 ↑ 1.0 1 2,251

Index Scan using vimb_product_pkey on vimb_product vimb_product_1 (cost=0.28..0.35 rows=1 width=9) (actual time=0.001..0.001 rows=1 loops=2,251)

  • Index Cond: (id = vimb_mediaplan_1.product_id)
  • Filter: (deleted IS FALSE)
68. 2.251 2.251 ↑ 1.0 1 2,251

Index Scan using account_pkey on account vimb_advertiser_1 (cost=0.28..1.37 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=2,251)

  • Index Cond: (id = project_1.account_id)
  • Filter: (deleted IS FALSE)
69. 24.761 24.761 ↑ 2.4 5 2,251

Index Scan using vimb_fragment_mediaplan_id_fkey_idx on vimb_fragment vimb_fragment_1 (cost=0.42..21.29 rows=12 width=115) (actual time=0.005..0.011 rows=5 loops=2,251)

  • Index Cond: (mediaplan_id = vimb_mediaplan_1.id)
  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 3
70. 10.294 10.294 ↑ 1.0 1 10,294

Index Scan using vimb_position_pkey on vimb_position vimb_position_1 (cost=0.28..0.30 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=10,294)

  • Index Cond: (id = vimb_fragment_1.position_id)
  • Filter: (deleted IS FALSE)
71. 0.073 0.189 ↑ 1.0 472 1

Hash (cost=12.14..12.14 rows=472 width=16) (actual time=0.189..0.189 rows=472 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
72. 0.116 0.116 ↑ 1.0 472 1

Seq Scan on vimb_section vimb_section_1 (cost=0.00..12.14 rows=472 width=16) (actual time=0.008..0.116 rows=472 loops=1)

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 42
73. 0.058 0.177 ↑ 1.0 324 1

Hash (cost=11.73..11.73 rows=324 width=38) (actual time=0.177..0.177 rows=324 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
74. 0.119 0.119 ↑ 1.0 324 1

Seq Scan on vimb_site vimb_site_1 (cost=0.00..11.73 rows=324 width=38) (actual time=0.008..0.119 rows=324 loops=1)

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 49
75. 0.013 0.038 ↑ 1.0 82 1

Hash (cost=3.08..3.08 rows=82 width=8) (actual time=0.038..0.038 rows=82 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
76. 0.025 0.025 ↑ 1.0 82 1

Seq Scan on vimb_site_owner vimb_site_owner_1 (cost=0.00..3.08 rows=82 width=8) (actual time=0.005..0.025 rows=82 loops=1)

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 26