explain.depesz.com

PostgreSQL's explain analyze made readable

Result: K9wI

Settings
# exclusive inclusive rows x rows loops node
1. 0.102 12,377.793 ↑ 1.0 1 1

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

2. 0.878 12,377.691 ↓ 3.5 14 1

GroupAggregate (cost=13,290.20..13,290.34 rows=4 width=64) (actual time=12,374.180..12,377.691 rows=14 loops=1)

  • Group Key: pr.id_provider
3.          

CTE tmp_prov_destiny

4. 1.878 55.250 ↑ 50.0 4 1

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

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

CTE tmp_prov_dest_chan

6. 0.047 0.352 ↓ 11.0 11 1

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

7.          

CTE tmp_prov

8. 0.027 0.027 ↑ 5.6 18 1

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

9. 0.046 0.277 ↓ 4.0 4 1

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

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

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

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

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

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

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

13. 0.012 0.063 ↓ 4.0 4 1

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

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

Seq Scan on provider_closures prcl (cost=0.00..1.16 rows=1 width=72) (actual time=0.041..0.051 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.012 0.028 ↓ 0.0 0 4

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

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

Bitmap Index Scan on provider_closure_channels_pkey (cost=0.00..4.20 rows=6 width=0) (actual time=0.004..0.004 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.012 0.028 ↑ 115.3 3 4

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

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

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

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

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

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

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

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

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

24. 0.012 0.442 ↓ 0.0 0 1

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

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

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

26. 0.000 0.377 ↓ 0.0 0 1

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

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

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

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

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

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

30. 0.012 0.025 ↓ 9.0 9 1

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

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

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

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

33. 0.086 0.345 ↓ 16.8 134 1

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

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

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

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

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

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

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

  • Index Cond: (id_region = rg.id_region)
38. 0.023 0.079 ↓ 0.0 0 1

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

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

40. 0.000 0.031 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
41. 0.006 0.031 ↓ 0.0 0 1

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

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

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

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

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

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

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

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

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

47. 0.000 0.007 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
48. 0.001 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_4.destiny_id = (ae_1.id_area)::text)
49. 0.006 0.006 ↓ 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.006..0.006 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.002 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.010 0.027 ↑ 33,517.0 1 1

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

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

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

  • Index Cond: (id_region = rg_1.id_region)
55. 0.011 0.026 ↓ 0.0 0 1

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

56. 0.011 0.015 ↓ 0.0 0 1

Sort (cost=106.08..106.08 rows=1 width=69) (actual time=0.015..0.015 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.098 0.098 ↑ 5.6 18 1

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

62.          

CTE tmp_hotel_coords

63. 2.672 7,226.998 ↓ 2.0 1,253 1

Nested Loop (cost=9.70..4,026.29 rows=624 width=19) (actual time=200.722..7,226.998 rows=1,253 loops=1)

64. 839.380 999.887 ↓ 1.3 169 1

Bitmap Heap Scan on hotels ht (cost=9.28..485.66 rows=128 width=8) (actual time=175.793..999.887 rows=169 loops=1)

  • Recheck Cond: ('(5066906.607363156, -323969.08340377506, 3849224.8414495015),(5076906.6071070982, -313969.08365983219, 3859224.8411934446)'::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=83
65. 160.507 160.507 ↓ 1.3 169 1

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

  • Index Cond: ('(5066906.607363156, -323969.08340377506, 3849224.8414495015),(5076906.6071070982, -313969.08365983219, 3859224.8411934446)'::cube @> (ll_to_earth(((geo_lat)::numeric)::double precision, ((geo_lon)::numeric)::double precision))::cube)
66. 6,224.439 6,224.439 ↓ 1.2 7 169

Index Scan using hotels_providers_idx_id_hotel on hotels_providers hp (cost=0.42..27.60 rows=6 width=19) (actual time=9.479..36.831 rows=7 loops=169)

  • Index Cond: (id_hotel = ht.id_hotel)
67. 2.606 12,376.813 ↓ 365.0 1,460 1

Merge Anti Join (cost=2,672.99..2,673.06 rows=4 width=64) (actual time=12,374.049..12,376.813 rows=1,460 loops=1)

  • Merge Cond: (pr.id_provider = prov_dest_exc.id_provider)
  • Join Filter: (thc.id_city = ANY (prov_dest_exc.list_cities))
  • Rows Removed by Join Filter: 110
68. 9.819 12,374.150 ↓ 196.5 1,572 1

Sort (cost=2,668.47..2,668.49 rows=8 width=68) (actual time=12,374.001..12,374.150 rows=1,572 loops=1)

  • Sort Key: pr.id_provider
  • Sort Method: quicksort Memory: 171kB
69. 16.046 12,364.331 ↓ 196.5 1,572 1

Nested Loop Left Join (cost=3.25..2,668.36 rows=8 width=68) (actual time=256.256..12,364.331 rows=1,572 loops=1)

  • Join Filter: (pr.id_provider = prov_dest_inc.id_provider)
  • Rows Removed by Join Filter: 2922
  • Filter: ((prov_dest_inc.id_provider IS NULL) OR (thc.id_city = ANY (prov_dest_inc.list_cities)))
70. 20.040 12,260.253 ↓ 10.0 1,572 1

Hash Join (cost=3.25..2,658.37 rows=157 width=68) (actual time=200.936..12,260.253 rows=1,572 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: 726
71. 7,234.282 7,234.282 ↓ 2.0 1,253 1

CTE Scan on tmp_hotel_coords thc (cost=0.00..12.48 rows=624 width=68) (actual time=200.731..7,234.282 rows=1,253 loops=1)

72. 0.028 0.151 ↑ 5.6 18 1

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

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

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

74.          

SubPlan (forHash Join)

75. 5,005.780 5,005.780 ↑ 1.0 1 1,690

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

  • 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: 964
76. 88.032 88.032 ↓ 2.0 2 1,572

CTE Scan on tmp_prov_destiny prov_dest_inc (cost=0.00..4.50 rows=1 width=64) (actual time=0.047..0.056 rows=2 loops=1,572)

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

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

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

  • Filter: (apply = 'EXCLUDE'::text)
  • Rows Removed by Filter: 2