explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HMju : Optimization for: listaofertas; plan #6T6

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 117,705.070 265,566.148 ↓ 674.0 674 1

Nested Loop (cost=189.99..111,792.48 rows=1 width=972) (actual time=173.779..265,566.148 rows=674 loops=1)

  • Join Filter: (oh.sogecodi = oca.sogecodi)
2.          

Initplan (forNested Loop)

3. 0.044 0.439 ↑ 1.0 1 1

Aggregate (cost=76.60..76.61 rows=1 width=8) (actual time=0.439..0.439 rows=1 loops=1)

4. 0.395 0.395 ↑ 1.0 694 1

Seq Scan on tipotarifas (cost=0.00..74.86 rows=694 width=0) (actual time=0.019..0.395 rows=694 loops=1)

  • Filter: (titaresi = 0)
  • Rows Removed by Filter: 975
5. 0.003 0.011 ↑ 1.0 1 1

Aggregate (cost=1.34..1.35 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1)

6. 0.008 0.008 ↑ 1.0 18 1

Seq Scan on mercado (cost=0.00..1.30 rows=18 width=0) (actual time=0.005..0.008 rows=18 loops=1)

  • Filter: (mercficticio = '0'::numeric)
  • Rows Removed by Filter: 6
7. 3.453 76.159 ↓ 674.0 674 1

Nested Loop Left Join (cost=111.89..3,155.11 rows=1 width=316) (actual time=1.703..76.159 rows=674 loops=1)

8. 4.109 67.314 ↓ 674.0 674 1

Nested Loop (cost=111.60..3,153.83 rows=1 width=284) (actual time=1.690..67.314 rows=674 loops=1)

9. 5.510 56.086 ↓ 2.5 791 1

Hash Left Join (cost=111.31..3,048.77 rows=314 width=228) (actual time=1.671..56.086 rows=791 loops=1)

  • Hash Cond: ((oh.centcodi)::text = (c.centcodi)::text)
  • Filter: ((c.centbaja <> 1) OR ((oh.centcodi)::text = '-1'::text))
  • Rows Removed by Filter: 184
10. 37.652 50.450 ↓ 1.8 975 1

Hash Join (cost=95.90..3,031.72 rows=548 width=222) (actual time=1.531..50.450 rows=975 loops=1)

  • Hash Cond: ((oh.sogecodi = oc.sogecodi) AND (oh.ofcodi = oc.ofcacodi))
11. 11.297 11.297 ↑ 1.0 72,316 1

Seq Scan on ofertamasterhotel oh (cost=0.00..2,556.16 rows=72,316 width=101) (actual time=0.011..11.297 rows=72,316 loops=1)

12. 0.597 1.501 ↑ 1.0 1,636 1

Hash (cost=71.36..71.36 rows=1,636 width=121) (actual time=1.501..1.501 rows=1,636 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 257kB
13. 0.904 0.904 ↑ 1.0 1,636 1

Seq Scan on ofertacambio oc (cost=0.00..71.36 rows=1,636 width=121) (actual time=0.012..0.904 rows=1,636 loops=1)

14. 0.034 0.126 ↑ 1.0 196 1

Hash (cost=12.96..12.96 rows=196 width=10) (actual time=0.126..0.126 rows=196 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
15. 0.092 0.092 ↑ 1.0 196 1

Seq Scan on centros c (cost=0.00..12.96 rows=196 width=10) (actual time=0.010..0.092 rows=196 loops=1)

16. 7.119 7.119 ↑ 1.0 1 791

Index Scan using ofertamaster_pkey on ofertamaster om (cost=0.29..0.33 rows=1 width=60) (actual time=0.009..0.009 rows=1 loops=791)

  • Index Cond: ((sogecodi = oh.sogecodi) AND (ofmacodi = oh.ofmacodi))
  • Filter: ((ofmatipo)::text = 'C'::text)
  • Rows Removed by Filter: 0
17. 5.392 5.392 ↑ 1.0 1 674

Index Scan using ofertacambiodescripcion_pkey on ofertacambiodescripcion ofcd (cost=0.29..1.28 rows=1 width=40) (actual time=0.008..0.008 rows=1 loops=674)

  • Index Cond: ((sogecodi = oc.sogecodi) AND (ofcacodi = oc.ofcacodi) AND (idiocodi = 1))
18. 2.696 2.696 ↑ 1.0 1 674

Index Scan using ofertacategoria_pkey on ofertacategoria oca (cost=0.14..0.23 rows=1 width=22) (actual time=0.004..0.004 rows=1 loops=674)

  • Index Cond: ((sogecodi = om.sogecodi) AND (ofcacodi = om.ofcacodi))
19.          

SubPlan (forNested Loop)

20. 3.841 1,256.007 ↑ 1.0 1 167

Aggregate (cost=2,221.90..2,221.91 rows=1 width=8) (actual time=7.521..7.521 rows=1 loops=167)

21. 1,036.402 1,252.166 ↓ 4.0 4 167

Bitmap Heap Scan on ofertamasterhotel (cost=208.09..2,221.89 rows=1 width=4) (actual time=5.041..7.498 rows=4 loops=167)

  • Recheck Cond: (sogecodi = om.sogecodi)
  • Filter: ((centcodi > 0) AND (ofmacodi = om.ofmacodi) AND ((ofmatipo)::text = (om.ofmatipo)::text))
  • Rows Removed by Filter: 22905
  • Heap Blocks: exact=230549
22. 215.764 215.764 ↓ 2.5 22,909 167

Bitmap Index Scan on ofertamasterhotel_pkey (cost=0.00..208.09 rows=9,040 width=0) (actual time=1.292..1.292 rows=22,909 loops=167)

  • Index Cond: (sogecodi = om.sogecodi)
23. 3.507 14,645.566 ↑ 1.0 1 167

Aggregate (cost=7,353.21..7,353.22 rows=1 width=8) (actual time=87.698..87.698 rows=1 loops=167)

24. 2.969 14,642.059 ↓ 2.0 2 167

Nested Loop (cost=208.37..7,353.21 rows=1 width=4) (actual time=35.277..87.677 rows=2 loops=167)

25. 453.600 14,634.878 ↓ 2.0 2 167

Nested Loop (cost=208.09..7,344.84 rows=1 width=12) (actual time=35.271..87.634 rows=2 loops=167)

  • Join Filter: (omh.ofmahocodi = op.ofmahocodi)
  • Rows Removed by Join Filter: 33662
26. 1,048.593 1,262.687 ↓ 4.0 4 167

Bitmap Heap Scan on ofertamasterhotel omh (cost=208.09..2,221.89 rows=1 width=16) (actual time=5.042..7.561 rows=4 loops=167)

  • Recheck Cond: (sogecodi = om.sogecodi)
  • Filter: ((centcodi > 0) AND (ofmacodi = om.ofmacodi) AND ((ofmatipo)::text = (om.ofmatipo)::text))
  • Rows Removed by Filter: 22905
  • Heap Blocks: exact=230549
27. 214.094 214.094 ↓ 2.5 22,909 167

Bitmap Index Scan on ofertamasterhotel_pkey (cost=0.00..208.09 rows=9,040 width=0) (actual time=1.282..1.282 rows=22,909 loops=167)

  • Index Cond: (sogecodi = om.sogecodi)
28. 12,918.591 12,918.591 ↓ 3.9 8,557 657

Seq Scan on ofertaperiodos op (cost=0.00..5,095.68 rows=2,182 width=8) (actual time=0.856..19.663 rows=8,557 loops=657)

  • Filter: ((sogecodi = om.sogecodi) AND (ofpetipo = 2) AND (now() >= ofpefechadesde) AND (now() <= ofpefechahasta))
  • Rows Removed by Filter: 139790
29. 4.212 4.212 ↑ 1.0 1 324

Index Only Scan using ofertacambio_pkey on ofertacambio oc_1 (cost=0.28..8.30 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=324)

  • Index Cond: ((sogecodi = om.sogecodi) AND (ofcacodi = omh.ofcodi))
  • Heap Fetches: 324
30. 7.414 9,278.284 ↑ 1.0 1 674

Aggregate (cost=3,983.08..3,983.09 rows=1 width=8) (actual time=13.765..13.766 rows=1 loops=674)

31. 9,270.870 9,270.870 ↑ 1.0 1 674

Seq Scan on ofertaperiodos (cost=0.00..3,983.07 rows=1 width=8) (actual time=4.317..13.755 rows=1 loops=674)

  • Filter: ((ofmahocodi = oh.ofmahocodi) AND (sogecodi = om.sogecodi) AND (ofpetipo = 2))
  • Rows Removed by Filter: 148346
32. 10.110 9,325.464 ↑ 1.0 1 674

Aggregate (cost=3,983.08..3,983.09 rows=1 width=8) (actual time=13.836..13.836 rows=1 loops=674)

33. 9,315.354 9,315.354 ↑ 1.0 1 674

Seq Scan on ofertaperiodos ofertaperiodos_1 (cost=0.00..3,983.07 rows=1 width=8) (actual time=4.346..13.821 rows=1 loops=674)

  • Filter: ((ofmahocodi = oh.ofmahocodi) AND (sogecodi = om.sogecodi) AND (ofpetipo = 2))
  • Rows Removed by Filter: 148346
34. 10.784 9,272.218 ↑ 1.0 1 674

Aggregate (cost=3,983.08..3,983.09 rows=1 width=8) (actual time=13.757..13.757 rows=1 loops=674)

35. 9,261.434 9,261.434 ↑ 1.0 1 674

Seq Scan on ofertaperiodos ofertaperiodos_2 (cost=0.00..3,983.07 rows=1 width=8) (actual time=4.307..13.741 rows=1 loops=674)

  • Filter: ((ofmahocodi = oh.ofmahocodi) AND (sogecodi = om.sogecodi) AND (ofpetipo = 1))
  • Rows Removed by Filter: 148346
36. 10.784 9,276.936 ↑ 1.0 1 674

Aggregate (cost=3,983.08..3,983.09 rows=1 width=8) (actual time=13.764..13.764 rows=1 loops=674)

37. 9,266.152 9,266.152 ↑ 1.0 1 674

Seq Scan on ofertaperiodos ofertaperiodos_3 (cost=0.00..3,983.07 rows=1 width=8) (actual time=4.309..13.748 rows=1 loops=674)

  • Filter: ((ofmahocodi = oh.ofmahocodi) AND (sogecodi = om.sogecodi) AND (ofpetipo = 1))
  • Rows Removed by Filter: 148346
38. 12.806 9,251.998 ↑ 1.0 1 674

Aggregate (cost=3,983.09..3,983.17 rows=1 width=32) (actual time=13.726..13.727 rows=1 loops=674)

39. 9,239.192 9,239.192 ↑ 1.0 1 674

Seq Scan on ofertaperiodos ofertaperiodos_4 (cost=0.00..3,983.07 rows=1 width=2) (actual time=4.297..13.708 rows=1 loops=674)

  • Filter: ((ofmahocodi = oh.ofmahocodi) AND (sogecodi = om.sogecodi) AND (ofpetipo = 2))
  • Rows Removed by Filter: 148346
40. 10.784 9,287.720 ↑ 1.0 1 674

Aggregate (cost=3,983.08..3,983.09 rows=1 width=8) (actual time=13.779..13.780 rows=1 loops=674)

41. 9,276.936 9,276.936 ↑ 1.0 1 674

Seq Scan on ofertaperiodos ofertaperiodos_5 (cost=0.00..3,983.07 rows=1 width=8) (actual time=4.306..13.764 rows=1 loops=674)

  • Filter: ((ofmahocodi = oh.ofmahocodi) AND (sogecodi = om.sogecodi) AND (ofpetipo = 2))
  • Rows Removed by Filter: 148346
42. 10.784 9,300.526 ↑ 1.0 1 674

Aggregate (cost=3,983.08..3,983.09 rows=1 width=8) (actual time=13.799..13.799 rows=1 loops=674)

43. 9,289.742 9,289.742 ↑ 1.0 1 674

Seq Scan on ofertaperiodos ofertaperiodos_6 (cost=0.00..3,983.07 rows=1 width=8) (actual time=4.314..13.783 rows=1 loops=674)

  • Filter: ((ofmahocodi = oh.ofmahocodi) AND (sogecodi = om.sogecodi) AND (ofpetipo = 2))
  • Rows Removed by Filter: 148346
44. 12.132 9,274.240 ↑ 1.0 1 674

Aggregate (cost=3,983.09..3,983.17 rows=1 width=32) (actual time=13.760..13.760 rows=1 loops=674)

45. 9,262.108 9,262.108 ↑ 1.0 1 674

Seq Scan on ofertaperiodos ofertaperiodos_7 (cost=0.00..3,983.07 rows=1 width=2) (actual time=4.290..13.742 rows=1 loops=674)

  • Filter: ((ofmahocodi = oh.ofmahocodi) AND (sogecodi = om.sogecodi) AND (ofpetipo = 1))
  • Rows Removed by Filter: 148346
46. 12.132 9,291.764 ↑ 1.0 1 674

Aggregate (cost=3,983.08..3,983.09 rows=1 width=32) (actual time=13.786..13.786 rows=1 loops=674)

47. 9,279.632 9,279.632 ↑ 1.0 1 674

Seq Scan on ofertaperiodos ofertaperiodos_8 (cost=0.00..3,983.07 rows=1 width=5) (actual time=4.318..13.768 rows=1 loops=674)

  • Filter: ((ofmahocodi = oh.ofmahocodi) AND (sogecodi = om.sogecodi) AND (ofpetipo = 2))
  • Rows Removed by Filter: 148346
48. 12.132 9,244.584 ↑ 1.0 1 674

Aggregate (cost=3,983.08..3,983.09 rows=1 width=32) (actual time=13.716..13.716 rows=1 loops=674)

49. 9,232.452 9,232.452 ↑ 1.0 1 674

Seq Scan on ofertaperiodos ofertaperiodos_9 (cost=0.00..3,983.07 rows=1 width=5) (actual time=4.281..13.698 rows=1 loops=674)

  • Filter: ((ofmahocodi = oh.ofmahocodi) AND (sogecodi = om.sogecodi) AND (ofpetipo = 2))
  • Rows Removed by Filter: 148346
50. 11.458 9,684.032 ↑ 1.0 1 674

Aggregate (cost=3,983.08..3,983.09 rows=1 width=32) (actual time=14.368..14.368 rows=1 loops=674)

51. 9,672.574 9,672.574 ↑ 1.0 1 674

Seq Scan on ofertaperiodos ofertaperiodos_10 (cost=0.00..3,983.07 rows=1 width=5) (actual time=4.492..14.351 rows=1 loops=674)

  • Filter: ((ofmahocodi = oh.ofmahocodi) AND (sogecodi = om.sogecodi) AND (ofpetipo = 2))
  • Rows Removed by Filter: 148346
52. 12.132 9,443.414 ↑ 1.0 1 674

Aggregate (cost=3,983.08..3,983.09 rows=1 width=32) (actual time=14.011..14.011 rows=1 loops=674)

53. 9,431.282 9,431.282 ↑ 1.0 1 674

Seq Scan on ofertaperiodos ofertaperiodos_11 (cost=0.00..3,983.07 rows=1 width=5) (actual time=4.399..13.993 rows=1 loops=674)

  • Filter: ((ofmahocodi = oh.ofmahocodi) AND (sogecodi = om.sogecodi) AND (ofpetipo = 2))
  • Rows Removed by Filter: 148346
54. 15.502 5,220.130 ↑ 1.0 1 674

Aggregate (cost=2,199.30..2,199.31 rows=1 width=8) (actual time=7.745..7.745 rows=1 loops=674)

55. 4,277.878 5,204.628 ↓ 25.0 25 674

Bitmap Heap Scan on ofertamasterhotel ofertamasterhotel_1 (cost=208.09..2,199.29 rows=1 width=0) (actual time=3.293..7.722 rows=25 loops=674)

  • Recheck Cond: (sogecodi = om.sogecodi)
  • Filter: ((centcodi > 0) AND (ofmacodi = om.ofmacodi))
  • Rows Removed by Filter: 24457
  • Heap Blocks: exact=1099052
56. 926.750 926.750 ↓ 2.7 24,482 674

Bitmap Index Scan on ofertamasterhotel_pkey (cost=0.00..208.09 rows=9,040 width=0) (actual time=1.375..1.375 rows=24,482 loops=674)

  • Index Cond: (sogecodi = om.sogecodi)
57. 8.088 8,344.794 ↑ 1.0 1 674

Aggregate (cost=1,255.62..1,255.63 rows=1 width=8) (actual time=12.381..12.381 rows=1 loops=674)

58. 8,336.706 8,336.706 ↓ 4.0 4 674

Index Only Scan using ofertasregimenes_pkey on ofertasregimenes (cost=0.42..1,255.62 rows=1 width=0) (actual time=0.935..12.369 rows=4 loops=674)

  • Index Cond: ((sogecodi = om.sogecodi) AND (ofcodi = (oh.ofcodi)::numeric) AND (oftipo = (om.ofmatipo)::text))
  • Heap Fetches: 2992
59. 7.785 5,730.798 ↑ 1.0 1 519

Aggregate (cost=1,255.63..1,255.64 rows=1 width=32) (actual time=11.042..11.042 rows=1 loops=519)

60. 5,723.013 5,723.013 ↓ 3.0 3 519

Index Only Scan using ofertasregimenes_pkey on ofertasregimenes ofertasregimenes_1 (cost=0.42..1,255.62 rows=1 width=4) (actual time=1.160..11.027 rows=3 loops=519)

  • Index Cond: ((sogecodi = om.sogecodi) AND (ofcodi = (oh.ofcodi)::numeric) AND (oftipo = (om.ofmatipo)::text))
  • Heap Fetches: 1752
61. 1.905 458.470 ↑ 1.0 1 635

Aggregate (cost=900.36..900.37 rows=1 width=8) (actual time=0.722..0.722 rows=1 loops=635)

62. 70.954 456.565 ↓ 0.0 0 635

Nested Loop (cost=10.51..900.36 rows=1 width=0) (actual time=0.704..0.719 rows=0 loops=635)

63. 161.925 190.500 ↓ 1.0 102 635

Bitmap Heap Scan on tipotarifas tt (cost=10.09..67.66 rows=99 width=8) (actual time=0.068..0.300 rows=102 loops=635)

  • Recheck Cond: (sogecodi = om.sogecodi)
  • Filter: (titaresi = 0)
  • Rows Removed by Filter: 734
  • Heap Blocks: exact=23785
64. 28.575 28.575 ↓ 3.5 836 635

Bitmap Index Scan on tipotarifas_pkey (cost=0.00..10.06 rows=238 width=0) (actual time=0.045..0.045 rows=836 loops=635)

  • Index Cond: (sogecodi = om.sogecodi)
65. 195.111 195.111 ↓ 0.0 0 65,037

Index Only Scan using ofertastipotarifa_pkey on ofertastipotarifa oft (cost=0.43..8.33 rows=1 width=9) (actual time=0.003..0.003 rows=0 loops=65,037)

  • Index Cond: ((sogecodi = om.sogecodi) AND (titacodi = (tt.titacodi)::numeric) AND (ofcodi = (oh.ofcodi)::numeric) AND (oftipo = (om.ofmatipo)::text))
  • Heap Fetches: 217
66. 0.195 30.420 ↑ 1.0 1 39

Aggregate (cost=900.36..900.37 rows=1 width=8) (actual time=0.779..0.780 rows=1 loops=39)

67. 4.524 30.225 ↑ 1.0 1 39

Nested Loop (cost=10.51..900.36 rows=1 width=0) (actual time=0.490..0.775 rows=1 loops=39)

68. 3.744 4.485 ↓ 1.8 181 39

Bitmap Heap Scan on tipotarifas tt_1 (cost=10.09..67.66 rows=99 width=8) (actual time=0.029..0.115 rows=181 loops=39)

  • Recheck Cond: (sogecodi = om.sogecodi)
  • Filter: (titaresi = 0)
  • Rows Removed by Filter: 50
  • Heap Blocks: exact=714
69. 0.741 0.741 ↑ 1.0 231 39

Bitmap Index Scan on tipotarifas_pkey (cost=0.00..10.06 rows=238 width=0) (actual time=0.019..0.019 rows=231 loops=39)

  • Index Cond: (sogecodi = om.sogecodi)
70. 21.216 21.216 ↓ 0.0 0 7,072

Index Only Scan using ofertastipotarifa_pkey on ofertastipotarifa oft_1 (cost=0.43..8.33 rows=1 width=9) (actual time=0.003..0.003 rows=0 loops=7,072)

  • Index Cond: ((sogecodi = om.sogecodi) AND (titacodi = (tt_1.titacodi)::numeric) AND (ofcodi = (oh.ofcodi)::numeric) AND (oftipo = (om.ofmatipo)::text))
  • Heap Fetches: 46
71. 8.088 45.832 ↑ 1.0 1 674

Aggregate (cost=9.83..9.84 rows=1 width=8) (actual time=0.068..0.068 rows=1 loops=674)

72. 13.409 37.744 ↓ 15.0 15 674

Hash Join (cost=8.46..9.82 rows=1 width=0) (actual time=0.046..0.056 rows=15 loops=674)

  • Hash Cond: ((me.merccodi)::numeric = ome.merccodi)
73. 5.463 5.463 ↑ 1.0 18 607

Seq Scan on mercado me (cost=0.00..1.30 rows=18 width=4) (actual time=0.005..0.009 rows=18 loops=607)

  • Filter: (mercficticio = '0'::numeric)
  • Rows Removed by Filter: 6
74. 4.718 18.872 ↓ 15.0 15 674

Hash (cost=8.45..8.45 rows=1 width=5) (actual time=0.028..0.028 rows=15 loops=674)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
75. 14.154 14.154 ↓ 15.0 15 674

Index Only Scan using ofertasmercado_pkey on ofertasmercado ome (cost=0.43..8.45 rows=1 width=5) (actual time=0.014..0.021 rows=15 loops=674)

  • Index Cond: ((sogecodi = om.sogecodi) AND (ofcodi = (oh.ofcodi)::numeric) AND (oftipo = (om.ofmatipo)::text))
  • Heap Fetches: 9951
76. 1.408 5.280 ↑ 1.0 1 176

Aggregate (cost=9.83..9.84 rows=1 width=32) (actual time=0.030..0.030 rows=1 loops=176)

77. 1.282 3.872 ↓ 5.0 5 176

Hash Join (cost=8.46..9.82 rows=1 width=4) (actual time=0.018..0.022 rows=5 loops=176)

  • Hash Cond: ((me_1.merccodi)::numeric = ome_1.merccodi)
78. 0.654 0.654 ↑ 1.0 18 109

Seq Scan on mercado me_1 (cost=0.00..1.30 rows=18 width=8) (actual time=0.002..0.006 rows=18 loops=109)

  • Filter: (mercficticio = '0'::numeric)
  • Rows Removed by Filter: 6
79. 0.880 1.936 ↓ 5.0 5 176

Hash (cost=8.45..8.45 rows=1 width=5) (actual time=0.011..0.011 rows=5 loops=176)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
80. 1.056 1.056 ↓ 5.0 5 176

Index Only Scan using ofertasmercado_pkey on ofertasmercado ome_1 (cost=0.43..8.45 rows=1 width=5) (actual time=0.005..0.006 rows=5 loops=176)

  • Index Cond: ((sogecodi = om.sogecodi) AND (ofcodi = (oh.ofcodi)::numeric) AND (oftipo = (om.ofmatipo)::text))
  • Heap Fetches: 944
81. 13.580 113.296 ↑ 1.0 1 388

Aggregate (cost=40.89..40.90 rows=1 width=8) (actual time=0.292..0.292 rows=1 loops=388)

82. 81.868 99.716 ↓ 4.0 455 388

Bitmap Heap Scan on partners (cost=9.74..40.60 rows=115 width=0) (actual time=0.056..0.257 rows=455 loops=388)

  • Recheck Cond: (sogecodi = om.sogecodi)
  • Filter: (partactivo = 1)
  • Rows Removed by Filter: 417
  • Heap Blocks: exact=9544
83. 17.848 17.848 ↓ 4.6 872 388

Bitmap Index Scan on partners_pkey (cost=0.00..9.71 rows=191 width=0) (actual time=0.046..0.046 rows=872 loops=388)