explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T2oq

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 9.150 ↓ 0.0 0 1

Nested Loop (cost=1,046.34..1,067.24 rows=1 width=294) (actual time=9.150..9.150 rows=0 loops=1)

  • Join Filter: (c.campaign_group_id = cgr.id)
2. 0.022 9.149 ↓ 0.0 0 1

Nested Loop (cost=1,046.34..1,059.59 rows=1 width=279) (actual time=9.149..9.149 rows=0 loops=1)

3. 0.006 0.244 ↓ 21.0 21 1

Nested Loop (cost=32.71..37.81 rows=1 width=183) (actual time=0.150..0.244 rows=21 loops=1)

4. 0.034 0.175 ↓ 21.0 21 1

Hash Join (cost=32.44..36.73 rows=1 width=156) (actual time=0.135..0.175 rows=21 loops=1)

  • Hash Cond: (cc.creative_id = b.creative_id)
5. 0.030 0.030 ↓ 1.1 65 1

Seq Scan on campaign_creative cc (cost=0.00..4.06 rows=57 width=13) (actual time=0.006..0.030 rows=65 loops=1)

  • Filter: ((start_date <= '2019-10-04 14:43:39.653602+00'::timestamp with time zone) AND ((end_date IS NULL) OR (end_date >= '2019-10-04 14:43:39.653602+00'::timestamp with time zone)))
  • Rows Removed by Filter: 6
6. 0.026 0.111 ↓ 29.0 29 1

Hash (cost=32.43..32.43 rows=1 width=143) (actual time=0.111..0.111 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
7. 0.055 0.085 ↓ 29.0 29 1

Bitmap Heap Scan on banners b (cost=25.76..32.43 rows=1 width=143) (actual time=0.038..0.085 rows=29 loops=1)

  • Recheck Cond: (((height = 400) AND (width = 240) AND (NOT is_deleted) AND is_active) OR ((height = 360) AND (width = 640) AND (NOT is_deleted) AND is_active) OR ((height = 250) AND (width = 300) AND (NOT is_deleted) AND is_active) OR ((height = 300) AND (width = 300) AND (NOT is_deleted) AND is_active) OR ((height = 280) AND (width = 446) AND (NOT is_deleted) AND is_active) OR ((height = 320) AND (width = 480) AND (NOT is_deleted) AND is_active))
  • Filter: (mime = ANY ('{image/jpg,image/jpeg,image/png,text/html,image/gif,text/javascript}'::text[]))
  • Heap Blocks: exact=11
8. 0.001 0.030 ↓ 0.0 0 1

BitmapOr (cost=25.76..25.76 rows=2 width=0) (actual time=0.030..0.030 rows=0 loops=1)

9. 0.015 0.015 ↓ 8.5 17 1

Bitmap Index Scan on banners_id_height_width_part_index (cost=0.00..4.29 rows=2 width=0) (actual time=0.015..0.015 rows=17 loops=1)

  • Index Cond: ((height = 400) AND (width = 240))
10. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on banners_id_height_width_part_index (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((height = 360) AND (width = 640))
11. 0.004 0.004 ↓ 12.0 12 1

Bitmap Index Scan on banners_id_height_width_part_index (cost=0.00..4.29 rows=1 width=0) (actual time=0.004..0.004 rows=12 loops=1)

  • Index Cond: ((height = 250) AND (width = 300))
12. 0.002 0.002 ↓ 0.0 0 1

Bitmap Index Scan on banners_id_height_width_part_index (cost=0.00..4.29 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: ((height = 300) AND (width = 300))
13. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on banners_id_height_width_part_index (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((height = 280) AND (width = 446))
14. 0.002 0.002 ↓ 0.0 0 1

Bitmap Index Scan on banners_id_height_width_part_index (cost=0.00..4.29 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: ((height = 320) AND (width = 480))
15. 0.063 0.063 ↑ 1.0 1 21

Index Scan using creatives_id_uindex on creatives cr (cost=0.27..1.06 rows=1 width=31) (actual time=0.003..0.003 rows=1 loops=21)

  • Index Cond: (id = cc.creative_id)
  • Filter: ((NOT is_deleted) AND (subtype = 'display'::text))
16. 0.249 8.883 ↓ 0.0 0 21

Bitmap Heap Scan on campaigns c (cost=1,013.63..1,017.71 rows=1 width=96) (actual time=0.423..0.423 rows=0 loops=21)

  • Recheck Cond: ((id = cc.campaign_id) AND is_enabled AND is_running AND (NOT is_deleted))
  • Filter: ((start_date <= '2019-10-04 14:43:39.653604+00'::timestamp with time zone) AND ((end_date IS NULL) OR (end_date >= '2019-10-04 14:43:39.653605+00'::timestamp with time zone)) AND (NOT (hashed SubPlan 17)) AND (NOT (hashed SubPlan 18)) AND ((NOT (hashed SubPlan 1)) OR (hashed SubPlan 2)) AND ((NOT (hashed SubPlan 3)) OR (hashed SubPlan 4)) AND ((NOT (hashed SubPlan 5)) OR (hashed SubPlan 6)) AND ((NOT (hashed SubPlan 7)) OR (hashed SubPlan 8)) AND ((NOT (hashed SubPlan 9)) OR (hashed SubPlan 10)) AND ((NOT (hashed SubPlan 11)) OR (hashed SubPlan 12)) AND ((NOT (hashed SubPlan 13)) OR (hashed SubPlan 14)) AND ((hashed SubPlan 15) OR (NOT (hashed SubPlan 16))) AND ((array_length("position", 1) IS NULL) OR (array_length("position", 1) < 1)) AND ((array_length(gender, 1) IS NULL) OR (array_length(gender, 1) < 1)))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=2
17. 0.021 0.021 ↓ 0.0 0 21

Bitmap Index Scan on campaigns_id_start_date_end_date_part_index (cost=0.00..0.28 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=21)

  • Index Cond: (id = cc.campaign_id)
18.          

SubPlan (forBitmap Heap Scan)

19. 0.089 0.134 ↑ 1.0 47 1

HashAggregate (cost=10.83..11.30 rows=47 width=4) (actual time=0.126..0.134 rows=47 loops=1)

  • Group Key: cage.campaign_id
20. 0.045 0.045 ↑ 1.0 306 1

Seq Scan on campaign_targeting_age cage (cost=0.00..10.06 rows=306 width=4) (actual time=0.005..0.045 rows=306 loops=1)

21. 0.475 6.292 ↑ 1.0 65 1

HashAggregate (cost=35.94..36.62 rows=68 width=4) (actual time=6.283..6.292 rows=65 loops=1)

  • Group Key: clng.campaign_id
22. 5.817 5.817 ↓ 1.0 2,076 1

Seq Scan on campaign_targeting_language clng (cost=0.00..30.75 rows=2,075 width=4) (actual time=0.009..5.817 rows=2,076 loops=1)

23. 0.040 0.059 ↓ 1.2 79 1

HashAggregate (cost=2.83..3.51 rows=68 width=4) (actual time=0.050..0.059 rows=79 loops=1)

  • Group Key: cgeo.campaign_id
24. 0.019 0.019 ↓ 1.1 165 1

Seq Scan on campaign_targeting_geo cgeo (cost=0.00..2.46 rows=146 width=4) (actual time=0.005..0.019 rows=165 loops=1)

25. 0.013 0.037 ↓ 1.1 33 1

HashAggregate (cost=3.27..3.57 rows=30 width=4) (actual time=0.033..0.037 rows=33 loops=1)

  • Group Key: cgeo_1.campaign_id
26. 0.024 0.024 ↑ 1.0 33 1

Seq Scan on campaign_targeting_geo cgeo_1 (cost=0.00..3.19 rows=34 width=4) (actual time=0.005..0.024 rows=33 loops=1)

  • Filter: ((country3 = 'RUS'::bpchar) AND (region = ''::text))
  • Rows Removed by Filter: 132
27. 0.047 0.170 ↑ 1.0 125 1

HashAggregate (cost=15.55..16.80 rows=125 width=4) (actual time=0.155..0.170 rows=125 loops=1)

  • Group Key: campaigns.id
28. 0.042 0.123 ↑ 1.0 125 1

Hash Join (cost=7.64..15.23 rows=125 width=4) (actual time=0.068..0.123 rows=125 loops=1)

  • Hash Cond: (campaigns.campaign_group_id = cgr_1.id)
29. 0.032 0.032 ↑ 1.0 125 1

Seq Scan on campaigns (cost=0.00..7.25 rows=125 width=8) (actual time=0.002..0.032 rows=125 loops=1)

30. 0.026 0.049 ↑ 1.0 162 1

Hash (cost=5.62..5.62 rows=162 width=4) (actual time=0.049..0.049 rows=162 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
31. 0.023 0.023 ↑ 1.0 162 1

Seq Scan on campaign_groups cgr_1 (cost=0.00..5.62 rows=162 width=4) (actual time=0.003..0.023 rows=162 loops=1)

32. 0.031 0.119 ↓ 3.0 65 1

HashAggregate (cost=14.03..14.25 rows=22 width=4) (actual time=0.111..0.119 rows=65 loops=1)

  • Group Key: campaigns_1.id
33. 0.027 0.088 ↓ 3.0 65 1

Hash Join (cost=6.39..13.98 rows=22 width=4) (actual time=0.046..0.088 rows=65 loops=1)

  • Hash Cond: (campaigns_1.campaign_group_id = cgr_2.id)
34. 0.025 0.025 ↑ 1.0 125 1

Seq Scan on campaigns campaigns_1 (cost=0.00..7.25 rows=125 width=8) (actual time=0.002..0.025 rows=125 loops=1)

35. 0.009 0.036 ↑ 1.0 29 1

Hash (cost=6.03..6.03 rows=29 width=4) (actual time=0.036..0.036 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
36. 0.027 0.027 ↑ 1.0 29 1

Seq Scan on campaign_groups cgr_2 (cost=0.00..6.03 rows=29 width=4) (actual time=0.005..0.027 rows=29 loops=1)

  • Filter: (balance > '0'::numeric)
  • Rows Removed by Filter: 133
37. 0.024 0.034 ↓ 1.4 40 1

HashAggregate (cost=2.05..2.34 rows=29 width=4) (actual time=0.029..0.034 rows=40 loops=1)

  • Group Key: cd.campaign_id
38. 0.010 0.010 ↓ 1.2 99 1

Seq Scan on campaign_targeting_device cd (cost=0.00..1.84 rows=84 width=4) (actual time=0.004..0.010 rows=99 loops=1)

39. 0.000 0.000 ↓ 0.0 0

Group (cost=10.26..10.26 rows=1 width=4) (never executed)

  • Group Key: cd_1.campaign_id
40. 0.000 0.000 ↓ 0.0 0

Sort (cost=10.26..10.26 rows=1 width=4) (never executed)

  • Sort Key: cd_1.campaign_id
41. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.18..10.25 rows=1 width=4) (never executed)

  • Hash Cond: (cd_1.device_id = td.id)
42. 0.000 0.000 ↓ 0.0 0

Seq Scan on campaign_targeting_device cd_1 (cost=0.00..1.84 rows=84 width=8) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.17..8.17 rows=1 width=4) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Index Scan using targeting_device_device_uindex on targeting_device td (cost=0.15..8.17 rows=1 width=4) (never executed)

  • Index Cond: (device = 2)
45. 0.050 0.079 ↓ 1.0 43 1

HashAggregate (cost=5.79..6.20 rows=41 width=4) (actual time=0.074..0.079 rows=43 loops=1)

  • Group Key: os.campaign_id
46. 0.029 0.029 ↑ 1.0 303 1

Seq Scan on campaign_targeting_os os (cost=0.00..5.03 rows=303 width=4) (actual time=0.009..0.029 rows=303 loops=1)

47. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=8.32..8.45 rows=13 width=4) (never executed)

  • Group Key: os_1.campaign_id
48. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1.99..8.28 rows=13 width=4) (never executed)

  • Hash Cond: (os_1.os = tos.id)
49. 0.000 0.000 ↓ 0.0 0

Seq Scan on campaign_targeting_os os_1 (cost=0.00..5.03 rows=303 width=8) (never executed)

50. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.98..1.98 rows=1 width=4) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Seq Scan on targeting_os tos (cost=0.00..1.98 rows=1 width=4) (never executed)

  • Filter: (os_name = 'Mac OS X'::text)
52. 0.014 0.020 ↑ 1.1 39 1

HashAggregate (cost=1.51..1.92 rows=41 width=4) (actual time=0.016..0.020 rows=39 loops=1)

  • Group Key: tdm.campaign_id
53. 0.006 0.006 ↑ 1.0 41 1

Seq Scan on campaign_targeting_domain tdm (cost=0.00..1.41 rows=41 width=4) (actual time=0.003..0.006 rows=41 loops=1)

54. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=1.57..1.60 rows=3 width=4) (never executed)

  • Group Key: tdm_1.campaign_id
55. 0.000 0.000 ↓ 0.0 0

Seq Scan on campaign_targeting_domain tdm_1 (cost=0.00..1.56 rows=3 width=4) (never executed)

  • Filter: (domain = ANY ('{ru.investing.com,*.ru.investing.com,*.investing.com}'::text[]))
56. 0.048 0.073 ↓ 1.1 40 1

HashAggregate (cost=5.59..5.94 rows=35 width=4) (actual time=0.068..0.073 rows=40 loops=1)

  • Group Key: bw.campaign_id
57. 0.025 0.025 ↓ 1.0 292 1

Seq Scan on campaign_targeting_browser bw (cost=0.00..4.87 rows=287 width=4) (actual time=0.005..0.025 rows=292 loops=1)

58. 0.000 0.000 ↓ 0.0 0

Group (cost=31.73..31.77 rows=9 width=4) (never executed)

  • Group Key: bw_1.campaign_id
59. 0.000 0.000 ↓ 0.0 0

Sort (cost=31.73..31.75 rows=9 width=4) (never executed)

  • Sort Key: bw_1.campaign_id
60. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=25.95..31.58 rows=9 width=4) (never executed)

  • Hash Cond: (bw_1.browser = tbw.id)
61. 0.000 0.000 ↓ 0.0 0

Seq Scan on campaign_targeting_browser bw_1 (cost=0.00..4.87 rows=287 width=8) (never executed)

62. 0.000 0.000 ↓ 0.0 0

Hash (cost=25.88..25.88 rows=6 width=4) (never executed)

63. 0.000 0.000 ↓ 0.0 0

Seq Scan on targeting_browser tbw (cost=0.00..25.88 rows=6 width=4) (never executed)

  • Filter: (browser = 'Safari'::text)
64. 0.453 1.367 ↑ 1.5 82 1

Group (cost=0.28..454.52 rows=123 width=4) (actual time=0.029..1.367 rows=82 loops=1)

  • Group Key: ch.campaign_id
65. 0.914 0.914 ↑ 1.0 7,442 1

Index Only Scan using campaign_targeting_hourweekday_is_enabled_hour_weekday_index on campaign_targeting_hourweekday ch (cost=0.28..435.91 rows=7,442 width=4) (actual time=0.028..0.914 rows=7,442 loops=1)

  • Heap Fetches: 0
66. 0.017 0.135 ↓ 2.0 73 1

Group (cost=0.28..399.25 rows=37 width=4) (actual time=0.009..0.135 rows=73 loops=1)

  • Group Key: ch_1.campaign_id
67. 0.118 0.118 ↓ 1.7 73 1

Index Only Scan using campaign_targeting_hourweekday_is_enabled_hour_weekday_index on campaign_targeting_hourweekday ch_1 (cost=0.28..399.14 rows=44 width=4) (actual time=0.008..0.118 rows=73 loops=1)

  • Index Cond: ((hour = 14) AND (weekday = 4))
  • Heap Fetches: 0
68. 0.001 0.059 ↑ 1.0 2 1

Group (cost=1.57..1.58 rows=2 width=4) (actual time=0.058..0.059 rows=2 loops=1)

  • Group Key: ctc.campaign_id
69. 0.038 0.058 ↑ 1.0 2 1

Sort (cost=1.57..1.58 rows=2 width=4) (actual time=0.058..0.058 rows=2 loops=1)

  • Sort Key: ctc.campaign_id
  • Sort Method: quicksort Memory: 25kB
70. 0.020 0.020 ↑ 1.0 2 1

Seq Scan on campaign_targeting_category ctc (cost=0.00..1.56 rows=2 width=4) (actual time=0.017..0.020 rows=2 loops=1)

  • Filter: (category = ANY ('{IAB9-30,IAB9}'::text[]))
  • Rows Removed by Filter: 99
71. 0.027 0.035 ↓ 2.6 37 1

HashAggregate (cost=1.56..1.70 rows=14 width=4) (actual time=0.030..0.035 rows=37 loops=1)

  • Group Key: ctc_1.campaign_id
72. 0.008 0.008 ↓ 2.2 101 1

Seq Scan on campaign_targeting_category ctc_1 (cost=0.00..1.45 rows=45 width=4) (actual time=0.002..0.008 rows=101 loops=1)

73. 0.000 0.000 ↓ 0.0 0

Seq Scan on campaign_groups cgr (cost=0.00..5.62 rows=162 width=19) (never executed)

Planning time : 32.903 ms
Execution time : 9.909 ms