explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c4xR

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 104.342 ↑ 1.0 1 1

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

2. 0.391 104.318 ↓ 3.2 13 1

GroupAggregate (cost=13,290.20..13,290.34 rows=4 width=64) (actual time=103.340..104.318 rows=13 loops=1)

  • Group Key: pr.id_provider
3.          

CTE tmp_prov_destiny

4. 3.275 45.196 ↑ 50.0 4 1

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

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

CTE tmp_prov_dest_chan

6. 0.018 0.166 ↓ 11.0 11 1

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

7.          

CTE tmp_prov

8. 0.018 0.018 ↑ 5.6 18 1

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

9. 0.022 0.132 ↓ 4.0 4 1

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

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

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

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

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

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

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

13. 0.006 0.032 ↓ 4.0 4 1

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

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

Seq Scan on provider_closures prcl (cost=0.00..1.16 rows=1 width=72) (actual time=0.022..0.026 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.004 0.012 ↓ 0.0 0 4

Bitmap Heap Scan on provider_closure_channels prclch (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)
16. 0.008 0.008 ↓ 0.0 0 4

Bitmap Index Scan on provider_closure_channels_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)
17. 0.000 0.004 ↓ 0.0 0 4

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

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

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

  • Index Cond: (prcl.id_provider_closure = id_provider_closure)
19. 0.008 0.016 ↑ 115.3 3 4

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

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

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

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

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

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

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

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

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

24. 0.006 0.201 ↓ 0.0 0 1

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

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

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

26. 0.000 0.183 ↓ 0.0 0 1

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

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

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

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

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

  • Hash Cond: ((ct_1.id_region)::text = tpd_1.destiny_id)
29. 9.002 9.002 ↑ 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.006..9.002 rows=33,517 loops=1)

30. 0.007 0.012 ↓ 9.0 9 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.005 0.005 ↓ 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.002..0.005 rows=9 loops=1)

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

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

33. 0.051 0.186 ↓ 16.8 134 1

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

34. 0.011 0.011 ↓ 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.008..0.011 rows=2 loops=1)

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

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

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

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

  • Index Cond: (id_country = tpd_2.destiny_id)
37. 2.680 2.680 ↓ 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.003..0.020 rows=49 loops=134)

  • Index Cond: (id_region = rg.id_region)
38. 0.029 0.092 ↓ 0.0 0 1

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

  • Hash Cond: ((ct_3.id_city)::text = ae.id_element)
39. 0.020 0.020 ↑ 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.020..0.020 rows=1 loops=1)

40. 0.000 0.043 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
41. 0.015 0.043 ↓ 0.0 0 1

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
44. 0.004 0.004 ↓ 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.004..0.004 rows=0 loops=1)

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

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

  • Hash Cond: ((ct_4.id_region)::text = ae_1.id_element)
46. 0.009 0.009 ↑ 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.009..0.009 rows=1 loops=1)

47. 0.000 0.004 ↓ 0.0 0 1

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

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

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

  • Join Filter: (tpd_4.destiny_id = (ae_1.id_area)::text)
49. 0.003 0.003 ↓ 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.003..0.003 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.055 ↓ 0.0 0 1

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

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

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

53. 0.009 0.009 ↑ 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.009..0.009 rows=1 loops=1)

54. 0.007 0.007 ↑ 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.007..0.007 rows=1 loops=1)

  • Index Cond: (id_region = rg_1.id_region)
55. 0.015 0.031 ↓ 0.0 0 1

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

56. 0.012 0.016 ↓ 0.0 0 1

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

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

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

  • Join Filter: (tpd_5.destiny_id = (ae_2.id_area)::text)
58. 0.004 0.004 ↓ 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.004..0.004 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.034 0.034 ↑ 5.6 18 1

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

62.          

CTE tmp_hotel_coords

63. 0.123 3.545 ↓ 2.9 1,824 1

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

64. 0.388 0.692 ↓ 2.1 273 1

Bitmap Heap Scan on hotels ht (cost=9.28..485.66 rows=128 width=8) (actual time=0.357..0.692 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. 0.304 0.304 ↓ 2.1 273 1

Bitmap Index Scan on hotels_idx_coords (cost=0.00..9.25 rows=129 width=0) (actual time=0.304..0.304 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. 2.730 2.730 ↓ 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=0.004..0.010 rows=7 loops=273)

  • Index Cond: (id_hotel = ht.id_hotel)
67. 0.637 103.927 ↓ 496.8 1,987 1

Merge Anti Join (cost=2,672.99..2,673.06 rows=4 width=64) (actual time=103.233..103.927 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. 2.315 103.271 ↓ 248.4 1,987 1

Sort (cost=2,668.47..2,668.49 rows=8 width=68) (actual time=103.211..103.271 rows=1,987 loops=1)

  • Sort Key: pr.id_provider
  • Sort Method: quicksort Memory: 204kB
69. 8.673 100.956 ↓ 248.4 1,987 1

Nested Loop Left Join (cost=3.25..2,668.36 rows=8 width=68) (actual time=45.677..100.956 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. 3.705 41.067 ↓ 14.8 2,328 1

Hash Join (cost=3.25..2,658.37 rows=157 width=68) (actual time=0.434..41.067 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. 4.309 4.309 ↓ 2.9 1,824 1

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

72. 0.013 0.053 ↑ 5.6 18 1

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

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

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

74.          

SubPlan (forHash Join)

75. 33.000 33.000 ↑ 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=0.012..0.012 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. 51.216 51.216 ↓ 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.020..0.022 rows=2 loops=2,328)

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

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

  • Sort Key: prov_dest_exc.id_provider
  • Sort Method: quicksort Memory: 29kB
78. 0.006 0.006 ↓ 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.003..0.006 rows=2 loops=1)

  • Filter: (apply = 'EXCLUDE'::text)
  • Rows Removed by Filter: 2
Planning time : 5.890 ms
Execution time : 105.914 ms