explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8hLHk

Settings
# exclusive inclusive rows x rows loops node
1. 0.144 19,733.346 ↑ 1.0 1 1

Aggregate (cost=13,290.40..13,290.41 rows=1 width=32) (actual time=19,733.346..19,733.346 rows=1 loops=1)

2. 1.352 19,733.202 ↓ 3.2 13 1

GroupAggregate (cost=13,290.20..13,290.34 rows=4 width=64) (actual time=19,729.893..19,733.202 rows=13 loops=1)

  • Group Key: pr.id_provider
3.          

CTE tmp_prov_destiny

4. 5.279 181.211 ↑ 50.0 4 1

GroupAggregate (cost=6,580.74..6,589.93 rows=200 width=96) (actual time=175.659..181.211 rows=4 loops=1)

  • Group Key: tpd.id_provider, tpd.provider_closure_how_apply
5.          

CTE tmp_prov_dest_chan

6. 0.048 0.629 ↓ 11.0 11 1

Nested Loop (cost=29.41..68.67 rows=1 width=75) (actual time=0.468..0.629 rows=11 loops=1)

7.          

CTE tmp_prov

8. 0.042 0.042 ↑ 5.6 18 1

Function Scan on unnest (cost=0.00..1.00 rows=100 width=32) (actual time=0.037..0.042 rows=18 loops=1)

9. 0.049 0.461 ↓ 4.0 4 1

Nested Loop Left Join (cost=9.57..31.04 rows=1 width=72) (actual time=0.349..0.461 rows=4 loops=1)

  • Filter: (COALESCE(prclpd.id_product, 'HOTELS'::text) = 'HOTELS'::text)
10. 0.057 0.400 ↓ 4.0 4 1

Nested Loop Left Join (cost=5.37..17.30 rows=1 width=72) (actual time=0.325..0.400 rows=4 loops=1)

  • Filter: (COALESCE(prclch.id_channel, 'B2C'::text) = 'B2C'::text)
11. 0.044 0.263 ↓ 4.0 4 1

Hash Join (cost=1.18..3.56 rows=1 width=72) (actual time=0.247..0.263 rows=4 loops=1)

  • Hash Cond: (tmp_prov.id_provider = prcl.id_provider)
12. 0.060 0.060 ↑ 5.6 18 1

CTE Scan on tmp_prov (cost=0.00..2.00 rows=100 width=32) (actual time=0.039..0.060 rows=18 loops=1)

13. 0.026 0.159 ↓ 4.0 4 1

Hash (cost=1.16..1.16 rows=1 width=72) (actual time=0.159..0.159 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.133 0.133 ↓ 4.0 4 1

Seq Scan on provider_closures prcl (cost=0.00..1.16 rows=1 width=72) (actual time=0.124..0.133 rows=4 loops=1)

  • Filter: ((provider_closure_type = 'DESTINY'::text) AND ((now())::date >= COALESCE(start_date, to_date('1900'::text, 'YYYY'::text))) AND ((now())::date <= COALESCE(end_date, to_date('9999'::text, 'YYYY'::text))))
  • Rows Removed by Filter: 1
15. 0.016 0.080 ↓ 0.0 0 4

Bitmap Heap Scan on provider_closure_channels prclch (cost=4.20..13.67 rows=6 width=40) (actual time=0.020..0.020 rows=0 loops=4)

  • Recheck Cond: (prcl.id_provider_closure = id_provider_closure)
16. 0.064 0.064 ↓ 0.0 0 4

Bitmap Index Scan on provider_closure_channels_pkey (cost=0.00..4.20 rows=6 width=0) (actual time=0.016..0.016 rows=0 loops=4)

  • Index Cond: (prcl.id_provider_closure = id_provider_closure)
17. 0.004 0.012 ↓ 0.0 0 4

Bitmap Heap Scan on provider_closure_products prclpd (cost=4.20..13.67 rows=6 width=40) (actual time=0.003..0.003 rows=0 loops=4)

  • Recheck Cond: (prcl.id_provider_closure = id_provider_closure)
18. 0.008 0.008 ↓ 0.0 0 4

Bitmap Index Scan on provider_closure_products_pkey (cost=0.00..4.20 rows=6 width=0) (actual time=0.002..0.002 rows=0 loops=4)

  • Index Cond: (prcl.id_provider_closure = id_provider_closure)
19. 0.072 0.120 ↑ 115.3 3 4

Bitmap Heap Scan on provider_closure_destinies prcldt (cost=18.83..33.16 rows=346 width=19) (actual time=0.030..0.030 rows=3 loops=4)

  • Recheck Cond: (id_provider_closure = prcl.id_provider_closure)
  • Heap Blocks: exact=4
20. 0.048 0.048 ↑ 115.3 3 4

Bitmap Index Scan on provider_closure_destinies_pkey (cost=0.00..18.75 rows=346 width=0) (actual time=0.012..0.012 rows=3 loops=4)

  • Index Cond: (id_provider_closure = prcl.id_provider_closure)
21. 13.443 175.932 ↓ 10.8 7,244 1

Sort (cost=6,512.07..6,513.74 rows=669 width=68) (actual time=175.167..175.932 rows=7,244 loops=1)

  • Sort Key: tpd.id_provider, tpd.provider_closure_how_apply
  • Sort Method: quicksort Memory: 758kB
22. 12.631 162.489 ↓ 10.8 7,244 1

HashAggregate (cost=6,467.29..6,473.98 rows=669 width=68) (actual time=158.874..162.489 rows=7,244 loops=1)

  • Group Key: tpd.id_provider, tpd.provider_closure_how_apply, ct.id_city
23. 0.741 149.858 ↓ 10.8 7,244 1

Append (cost=0.04..6,462.28 rows=669 width=68) (actual time=22.467..149.858 rows=7,244 loops=1)

24. 0.023 0.777 ↓ 0.0 0 1

Hash Join (cost=0.04..1,518.37 rows=168 width=68) (actual time=0.777..0.777 rows=0 loops=1)

  • Hash Cond: ((ct.id_city)::text = tpd.destiny_id)
25. 0.098 0.098 ↑ 33,517.0 1 1

Seq Scan on cities ct (cost=0.00..1,307.17 rows=33,517 width=4) (actual time=0.098..0.098 rows=1 loops=1)

26. 0.001 0.656 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=96) (actual time=0.656..0.656 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
27. 0.655 0.655 ↓ 0.0 0 1

CTE Scan on tmp_prov_dest_chan tpd (cost=0.00..0.02 rows=1 width=96) (actual time=0.655..0.655 rows=0 loops=1)

  • Filter: (destiny_type = 'CITY'::text)
  • Rows Removed by Filter: 11
28. 16.572 85.761 ↓ 4.0 678 1

Hash Join (cost=0.04..1,518.37 rows=168 width=68) (actual time=21.687..85.761 rows=678 loops=1)

  • Hash Cond: ((ct_1.id_region)::text = tpd_1.destiny_id)
29. 69.155 69.155 ↑ 1.0 33,517 1

Seq Scan on cities ct_1 (cost=0.00..1,307.17 rows=33,517 width=8) (actual time=0.013..69.155 rows=33,517 loops=1)

30. 0.022 0.034 ↓ 9.0 9 1

Hash (cost=0.02..0.02 rows=1 width=96) (actual time=0.034..0.034 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.012 0.012 ↓ 9.0 9 1

CTE Scan on tmp_prov_dest_chan tpd_1 (cost=0.00..0.02 rows=1 width=96) (actual time=0.003..0.012 rows=9 loops=1)

  • Filter: (destiny_type = 'REGION'::text)
  • Rows Removed by Filter: 2
32. 1.637 62.086 ↓ 48.3 6,566 1

Nested Loop (cost=4.63..49.20 rows=136 width=68) (actual time=0.173..62.086 rows=6,566 loops=1)

33. 0.115 10.199 ↓ 16.8 134 1

Nested Loop (cost=4.34..24.73 rows=8 width=68) (actual time=0.158..10.199 rows=134 loops=1)

34. 0.022 0.022 ↓ 2.0 2 1

CTE Scan on tmp_prov_dest_chan tpd_2 (cost=0.00..0.02 rows=1 width=96) (actual time=0.012..0.022 rows=2 loops=1)

  • Filter: (destiny_type = 'COUNTRY'::text)
  • Rows Removed by Filter: 9
35. 9.924 10.062 ↓ 8.4 67 2

Bitmap Heap Scan on regions rg (cost=4.34..24.62 rows=8 width=7) (actual time=0.095..5.031 rows=67 loops=2)

  • Recheck Cond: (id_country = tpd_2.destiny_id)
  • Heap Blocks: exact=30
36. 0.138 0.138 ↓ 8.4 67 2

Bitmap Index Scan on regions_idx_id_country (cost=0.00..4.34 rows=8 width=0) (actual time=0.069..0.069 rows=67 loops=2)

  • Index Cond: (id_country = tpd_2.destiny_id)
37. 50.250 50.250 ↓ 2.7 49 134

Index Scan using cities_idx_id_region on cities ct_2 (cost=0.29..2.88 rows=18 width=8) (actual time=0.251..0.375 rows=49 loops=134)

  • Index Cond: (id_region = rg.id_region)
38. 0.136 0.367 ↓ 0.0 0 1

Hash Join (cost=120.28..1,638.58 rows=165 width=68) (actual time=0.367..0.367 rows=0 loops=1)

  • Hash Cond: ((ct_3.id_city)::text = ae.id_element)
39. 0.039 0.039 ↑ 33,517.0 1 1

Seq Scan on cities ct_3 (cost=0.00..1,307.17 rows=33,517 width=4) (actual time=0.039..0.039 rows=1 loops=1)

40. 0.001 0.192 ↓ 0.0 0 1

Hash (cost=120.15..120.15 rows=11 width=69) (actual time=0.192..0.192 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
41. 0.022 0.191 ↓ 0.0 0 1

Hash Join (cost=0.04..120.15 rows=11 width=69) (actual time=0.191..0.191 rows=0 loops=1)

  • Hash Cond: ((ae.id_area)::text = tpd_3.destiny_id)
42. 0.159 0.159 ↑ 2,236.0 1 1

Seq Scan on areas_elements ae (cost=0.00..106.03 rows=2,236 width=9) (actual time=0.159..0.159 rows=1 loops=1)

  • Filter: (element_type = 'CITY'::text)
43. 0.000 0.010 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=96) (actual time=0.010..0.010 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
44. 0.010 0.010 ↓ 0.0 0 1

CTE Scan on tmp_prov_dest_chan tpd_3 (cost=0.00..0.02 rows=1 width=96) (actual time=0.010..0.010 rows=0 loops=1)

  • Filter: (destiny_type = 'AREA'::text)
  • Rows Removed by Filter: 11
45. 0.025 0.055 ↓ 0.0 0 1

Hash Join (cost=106.08..1,622.88 rows=15 width=68) (actual time=0.055..0.055 rows=0 loops=1)

  • Hash Cond: ((ct_4.id_region)::text = ae_1.id_element)
46. 0.020 0.020 ↑ 33,517.0 1 1

Seq Scan on cities ct_4 (cost=0.00..1,307.17 rows=33,517 width=8) (actual time=0.020..0.020 rows=1 loops=1)

47. 0.000 0.010 ↓ 0.0 0 1

Hash (cost=106.07..106.07 rows=1 width=69) (actual time=0.010..0.010 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
48. 0.001 0.010 ↓ 0.0 0 1

Nested Loop (cost=0.00..106.07 rows=1 width=69) (actual time=0.010..0.010 rows=0 loops=1)

  • Join Filter: (tpd_4.destiny_id = (ae_1.id_area)::text)
49. 0.009 0.009 ↓ 0.0 0 1

CTE Scan on tmp_prov_dest_chan tpd_4 (cost=0.00..0.02 rows=1 width=96) (actual time=0.009..0.009 rows=0 loops=1)

  • Filter: (destiny_type = 'AREA'::text)
  • Rows Removed by Filter: 11
50. 0.000 0.000 ↓ 0.0 0

Seq Scan on areas_elements ae_1 (cost=0.00..106.03 rows=1 width=9) (never executed)

  • Filter: (element_type = 'REGION'::text)
51. 0.003 0.071 ↓ 0.0 0 1

Merge Join (cost=106.64..108.19 rows=17 width=68) (actual time=0.071..0.071 rows=0 loops=1)

  • Merge Cond: (rg_1.id_country = ae_2.id_element)
52. 0.009 0.036 ↑ 33,517.0 1 1

Nested Loop (cost=0.57..6,238.51 rows=33,517 width=7) (actual time=0.036..0.036 rows=1 loops=1)

53. 0.018 0.018 ↑ 1,977.0 1 1

Index Scan using regions_idx_id_country on regions rg_1 (cost=0.28..190.56 rows=1,977 width=7) (actual time=0.018..0.018 rows=1 loops=1)

54. 0.009 0.009 ↑ 18.0 1 1

Index Scan using cities_idx_id_region on cities ct_5 (cost=0.29..2.88 rows=18 width=8) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (id_region = rg_1.id_region)
55. 0.008 0.032 ↓ 0.0 0 1

Materialize (cost=106.08..106.08 rows=1 width=69) (actual time=0.032..0.032 rows=0 loops=1)

56. 0.017 0.024 ↓ 0.0 0 1

Sort (cost=106.08..106.08 rows=1 width=69) (actual time=0.024..0.024 rows=0 loops=1)

  • Sort Key: ae_2.id_element
  • Sort Method: quicksort Memory: 25kB
57. 0.000 0.007 ↓ 0.0 0 1

Nested Loop (cost=0.00..106.07 rows=1 width=69) (actual time=0.007..0.007 rows=0 loops=1)

  • Join Filter: (tpd_5.destiny_id = (ae_2.id_area)::text)
58. 0.007 0.007 ↓ 0.0 0 1

CTE Scan on tmp_prov_dest_chan tpd_5 (cost=0.00..0.02 rows=1 width=96) (actual time=0.007..0.007 rows=0 loops=1)

  • Filter: (destiny_type = 'AREA'::text)
  • Rows Removed by Filter: 11
59. 0.000 0.000 ↓ 0.0 0

Seq Scan on areas_elements ae_2 (cost=0.00..106.03 rows=1 width=9) (never executed)

  • Filter: (element_type = 'COUNTRY'::text)
60.          

CTE tmp_prov_active

61. 0.059 0.059 ↑ 5.6 18 1

Function Scan on unnest unnest_1 (cost=0.00..1.00 rows=100 width=96) (actual time=0.057..0.059 rows=18 loops=1)

62.          

CTE tmp_hotel_coords

63. 4.430 10,130.818 ↓ 2.9 1,824 1

Nested Loop (cost=9.70..4,026.29 rows=624 width=19) (actual time=108.385..10,130.818 rows=1,824 loops=1)

64. 1,199.834 1,272.452 ↓ 2.1 273 1

Bitmap Heap Scan on hotels ht (cost=9.28..485.66 rows=128 width=8) (actual time=80.058..1,272.452 rows=273 loops=1)

  • Recheck Cond: ('(4907994.5727907857, -795394.18293227628, 3984835.6359148878),(4917994.5725347279, -785394.18318833341, 3994835.6356588309)'::cube @> (ll_to_earth(((geo_lat)::numeric)::double precision, ((geo_lon)::numeric)::double precision))::cube)
  • Filter: ((delete_date IS NULL) AND (active = 'Y'::text))
  • Heap Blocks: exact=108
65. 72.618 72.618 ↓ 2.1 273 1

Bitmap Index Scan on hotels_idx_coords (cost=0.00..9.25 rows=129 width=0) (actual time=72.618..72.618 rows=273 loops=1)

  • Index Cond: ('(4907994.5727907857, -795394.18293227628, 3984835.6359148878),(4917994.5725347279, -785394.18318833341, 3994835.6356588309)'::cube @> (ll_to_earth(((geo_lat)::numeric)::double precision, ((geo_lon)::numeric)::double precision))::cube)
66. 8,853.936 8,853.936 ↓ 1.2 7 273

Index Scan using hotels_providers_idx_id_hotel on hotels_providers hp (cost=0.42..27.60 rows=6 width=19) (actual time=7.105..32.432 rows=7 loops=273)

  • Index Cond: (id_hotel = ht.id_hotel)
67. 2.271 19,731.850 ↓ 496.8 1,987 1

Merge Anti Join (cost=2,672.99..2,673.06 rows=4 width=64) (actual time=19,729.377..19,731.850 rows=1,987 loops=1)

  • Merge Cond: (pr.id_provider = prov_dest_exc.id_provider)
  • Join Filter: (thc.id_city = ANY (prov_dest_exc.list_cities))
68. 14.503 19,729.497 ↓ 248.4 1,987 1

Sort (cost=2,668.47..2,668.49 rows=8 width=68) (actual time=19,729.289..19,729.497 rows=1,987 loops=1)

  • Sort Key: pr.id_provider
  • Sort Method: quicksort Memory: 204kB
69. 37.991 19,714.994 ↓ 248.4 1,987 1

Nested Loop Left Join (cost=3.25..2,668.36 rows=8 width=68) (actual time=289.855..19,714.994 rows=1,987 loops=1)

  • Join Filter: (pr.id_provider = prov_dest_inc.id_provider)
  • Rows Removed by Join Filter: 4315
  • Filter: ((prov_dest_inc.id_provider IS NULL) OR (thc.id_city = ANY (prov_dest_inc.list_cities)))
  • Rows Removed by Filter: 341
70. 34.589 19,444.203 ↓ 14.8 2,328 1

Hash Join (cost=3.25..2,658.37 rows=157 width=68) (actual time=108.577..19,444.203 rows=2,328 loops=1)

  • Hash Cond: (thc.id_provider = pr.avail_provider)
  • Join Filter: ((pr.id_provider = pr.avail_provider) OR (SubPlan 6))
  • Rows Removed by Join Filter: 1184
71. 10,142.011 10,142.011 ↓ 2.9 1,824 1

CTE Scan on tmp_hotel_coords thc (cost=0.00..12.48 rows=624 width=68) (actual time=108.397..10,142.011 rows=1,824 loops=1)

72. 0.027 0.103 ↑ 5.6 18 1

Hash (cost=2.00..2.00 rows=100 width=64) (actual time=0.103..0.103 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
73. 0.076 0.076 ↑ 5.6 18 1

CTE Scan on tmp_prov_active pr (cost=0.00..2.00 rows=100 width=64) (actual time=0.062..0.076 rows=18 loops=1)

74.          

SubPlan (forHash Join)

75. 9,267.500 9,267.500 ↑ 1.0 1 2,750

Index Only Scan using hotels_providers_provider_pkey on hotels_providers_provider hpp (cost=0.43..8.45 rows=1 width=0) (actual time=3.370..3.370 rows=1 loops=2,750)

  • Index Cond: ((id_provider_1 = pr.avail_provider) AND (id_hotels_provider_1 = thc.id_hotels_provider) AND (id_provider_2 = pr.id_provider))
  • Heap Fetches: 1566
76. 232.800 232.800 ↓ 2.0 2 2,328

CTE Scan on tmp_prov_destiny prov_dest_inc (cost=0.00..4.50 rows=1 width=64) (actual time=0.090..0.100 rows=2 loops=2,328)

  • Filter: (apply = 'INCLUDE'::text)
  • Rows Removed by Filter: 2
77. 0.059 0.082 ↓ 2.0 2 1

Sort (cost=4.51..4.51 rows=1 width=64) (actual time=0.082..0.082 rows=2 loops=1)

  • Sort Key: prov_dest_exc.id_provider
  • Sort Method: quicksort Memory: 29kB
78. 0.023 0.023 ↓ 2.0 2 1

CTE Scan on tmp_prov_destiny prov_dest_exc (cost=0.00..4.50 rows=1 width=64) (actual time=0.014..0.023 rows=2 loops=1)

  • Filter: (apply = 'EXCLUDE'::text)
  • Rows Removed by Filter: 2
Planning time : 1,065.133 ms
Execution time : 19,738.934 ms