explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OaDc

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 148,932.801 ↓ 5.0 10 1

Append (cost=0.43..4,592,232.72 rows=2 width=2,547) (actual time=138,530.748..148,932.801 rows=10 loops=1)

2. 0.011 138,540.476 ↓ 10.0 10 1

Subquery Scan on *SELECT* 1 (cost=0.43..2,296,122.27 rows=1 width=2,547) (actual time=138,530.747..138,540.476 rows=10 loops=1)

3. 0.029 138,540.465 ↓ 10.0 10 1

Subquery Scan on v_vstore_all_premise_priority_package_pkg_sku_metrics (cost=0.43..2,296,122.26 rows=1 width=2,547) (actual time=138,530.745..138,540.465 rows=10 loops=1)

  • Filter: (v_vstore_all_premise_priority_package_pkg_sku_metrics.metric_type = 'volume'::text)
  • Rows Removed by Filter: 30
4. 16.992 138,540.436 ↑ 2.5 40 1

Nested Loop (cost=0.43..2,296,121.01 rows=100 width=310) (actual time=138,530.739..138,540.436 rows=40 loops=1)

  • Join Filter: ((dat.master_pkg_sku_cd)::text = (d_item_sku_pkg_2018_11_27_11_24_53.master_pkg_sku_cd)::text)
  • Rows Removed by Join Filter: 2420
5. 0.020 138,518.354 ↓ 10.0 10 1

Nested Loop (cost=0.43..2,296,060.09 rows=1 width=214) (actual time=138,515.394..138,518.354 rows=10 loops=1)

6. 0.082 138,518.114 ↓ 10.0 10 1

Nested Loop (cost=0.00..2,296,051.63 rows=1 width=212) (actual time=138,515.310..138,518.114 rows=10 loops=1)

  • Join Filter: ((dat.priority_package_group_cd)::text = (l_lov_2018_11_30_10_14_28.lov_cd)::text)
  • Rows Removed by Join Filter: 110
7. 18.887 18.887 ↑ 26.2 5 1

Seq Scan on l_lov_2018_11_30_10_14_28 (cost=0.00..4,524.10 rows=131 width=21) (actual time=16.143..18.887 rows=5 loops=1)

  • Filter: ((attribute_cd)::text = 'CBI_PRIORITY_PACKAGE_GROUP_VAL'::text)
  • Rows Removed by Filter: 142003
8. 0.078 138,499.145 ↓ 1.6 24 5

Materialize (cost=0.00..2,291,498.09 rows=15 width=194) (actual time=555.538..27,699.829 rows=24 loops=5)

9. 0.201 138,499.067 ↓ 1.6 24 1

Subquery Scan on dat (cost=0.00..2,291,498.02 rows=15 width=194) (actual time=2,777.676..138,499.067 rows=24 loops=1)

  • Filter: (dat.time_period_cd = 'CYTM'::text)
  • Rows Removed by Filter: 456
10. 138,498.866 138,498.866 ↑ 6.2 480 1

Seq Scan on f_position_store_pri_pack_pkg_sku_agg_2018_11_27_00_01_30 (cost=0.00..2,291,460.52 rows=3,000 width=298) (actual time=2,777.667..138,498.866 rows=480 loops=1)

  • Filter: (((store_cd)::text = '100012506'::text) AND (position_id = 0))
  • Rows Removed by Filter: 25296529
11. 0.220 0.220 ↑ 1.0 1 10

Index Scan using d_store_2018_11_30_10_32_15_store_cd_idx on d_store_2018_11_30_10_32_15 (cost=0.43..8.45 rows=1 width=12) (actual time=0.021..0.022 rows=1 loops=10)

  • Index Cond: ((store_cd)::text = '100012506'::text)
  • Filter: ((premise_type_cd)::text ~~ 'F'::text)
12. 0.640 0.640 ↑ 1.0 243 10

Seq Scan on d_item_sku_pkg_2018_11_27_11_24_53 (cost=0.00..8.43 rows=243 width=96) (actual time=0.002..0.064 rows=243 loops=10)

13.          

SubPlan (forNested Loop)

14. 0.100 0.700 ↑ 1.0 1 50

Limit (cost=0.29..6.12 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=50)

15. 0.600 0.600 ↑ 2.0 1 50

Index Scan using d_item_2018_11_27_11_20_18_master_sku_cd on d_item_2018_11_27_11_20_18 (cost=0.29..11.95 rows=2 width=4) (actual time=0.012..0.012 rows=1 loops=50)

  • Index Cond: ((master_sku_cd)::text = (dat.master_pkg_sku_cd)::text)
16. 0.100 0.550 ↑ 1.0 1 50

Limit (cost=0.29..6.12 rows=1 width=5) (actual time=0.010..0.011 rows=1 loops=50)

17. 0.450 0.450 ↑ 2.0 1 50

Index Scan using d_item_2018_11_27_11_20_18_master_sku_cd on d_item_2018_11_27_11_20_18 d_item_2018_11_27_11_20_18_1 (cost=0.29..11.95 rows=2 width=5) (actual time=0.009..0.009 rows=1 loops=50)

  • Index Cond: ((master_sku_cd)::text = (dat.master_pkg_sku_cd)::text)
18. 0.050 0.500 ↑ 1.0 1 50

Limit (cost=0.29..6.12 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=50)

19. 0.450 0.450 ↑ 2.0 1 50

Index Scan using d_item_2018_11_27_11_20_18_master_sku_cd on d_item_2018_11_27_11_20_18 d_item_2018_11_27_11_20_18_2 (cost=0.29..11.95 rows=2 width=4) (actual time=0.009..0.009 rows=1 loops=50)

  • Index Cond: ((master_sku_cd)::text = (dat.master_pkg_sku_cd)::text)
20. 0.050 0.500 ↑ 1.0 1 50

Limit (cost=0.29..6.12 rows=1 width=2) (actual time=0.010..0.010 rows=1 loops=50)

21. 0.450 0.450 ↑ 2.0 1 50

Index Scan using d_item_2018_11_27_11_20_18_master_sku_cd on d_item_2018_11_27_11_20_18 d_item_2018_11_27_11_20_18_3 (cost=0.29..11.95 rows=2 width=2) (actual time=0.009..0.009 rows=1 loops=50)

  • Index Cond: ((master_sku_cd)::text = (dat.master_pkg_sku_cd)::text)
22. 0.050 0.550 ↑ 1.0 1 50

Limit (cost=0.29..6.12 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=50)

23. 0.500 0.500 ↑ 2.0 1 50

Index Scan using d_item_2018_11_27_11_20_18_master_sku_cd on d_item_2018_11_27_11_20_18 d_item_2018_11_27_11_20_18_4 (cost=0.29..11.95 rows=2 width=4) (actual time=0.010..0.010 rows=1 loops=50)

  • Index Cond: ((master_sku_cd)::text = (dat.master_pkg_sku_cd)::text)
24. 0.100 0.550 ↑ 1.0 1 50

Limit (cost=0.29..6.12 rows=1 width=7) (actual time=0.010..0.011 rows=1 loops=50)

25. 0.450 0.450 ↑ 2.0 1 50

Index Scan using d_item_2018_11_27_11_20_18_master_sku_cd on d_item_2018_11_27_11_20_18 d_item_2018_11_27_11_20_18_5 (cost=0.29..11.95 rows=2 width=7) (actual time=0.009..0.009 rows=1 loops=50)

  • Index Cond: ((master_sku_cd)::text = (dat.master_pkg_sku_cd)::text)
26. 0.100 0.550 ↑ 1.0 1 50

Limit (cost=0.29..6.12 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=50)

27. 0.450 0.450 ↑ 2.0 1 50

Index Scan using d_item_2018_11_27_11_20_18_master_sku_cd on d_item_2018_11_27_11_20_18 d_item_2018_11_27_11_20_18_6 (cost=0.29..11.95 rows=2 width=4) (actual time=0.009..0.009 rows=1 loops=50)

  • Index Cond: ((master_sku_cd)::text = (d_item_sku_pkg_2018_11_27_11_24_53.master_pkg_sku_cd)::text)
28. 0.050 0.550 ↑ 1.0 1 50

Limit (cost=0.29..6.12 rows=1 width=5) (actual time=0.011..0.011 rows=1 loops=50)

29. 0.500 0.500 ↑ 2.0 1 50

Index Scan using d_item_2018_11_27_11_20_18_master_sku_cd on d_item_2018_11_27_11_20_18 d_item_2018_11_27_11_20_18_7 (cost=0.29..11.95 rows=2 width=5) (actual time=0.010..0.010 rows=1 loops=50)

  • Index Cond: ((master_sku_cd)::text = (d_item_sku_pkg_2018_11_27_11_24_53.master_pkg_sku_cd)::text)
30. 0.001 10,392.316 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.43..2,296,110.45 rows=1 width=2,547) (actual time=10,392.316..10,392.316 rows=0 loops=1)

  • Filter: ("*SELECT* 2".metric_type = 'volume'::text)
31. 0.002 10,392.315 ↓ 0.0 0 1

Nested Loop (cost=0.43..2,296,109.20 rows=100 width=299) (actual time=10,392.315..10,392.315 rows=0 loops=1)

  • Join Filter: ((dat2.master_pkg_sku_cd)::text = (d_item_sku_pkg_2018_11_27_11_24_53_1.master_pkg_sku_cd)::text)
32. 0.012 10,392.313 ↓ 0.0 0 1

Nested Loop (cost=0.43..2,296,060.09 rows=1 width=212) (actual time=10,392.313..10,392.313 rows=0 loops=1)

33. 0.063 10,392.161 ↓ 10.0 10 1

Nested Loop (cost=0.00..2,296,051.63 rows=1 width=212) (actual time=10,389.964..10,392.161 rows=10 loops=1)

  • Join Filter: ((dat2.priority_package_group_cd)::text = (l_lov_2018_11_30_10_14_28_1.lov_cd)::text)
  • Rows Removed by Join Filter: 110
34. 18.603 18.603 ↑ 26.2 5 1

Seq Scan on l_lov_2018_11_30_10_14_28 l_lov_2018_11_30_10_14_28_1 (cost=0.00..4,524.10 rows=131 width=21) (actual time=16.460..18.603 rows=5 loops=1)

  • Filter: ((attribute_cd)::text = 'CBI_PRIORITY_PACKAGE_GROUP_VAL'::text)
  • Rows Removed by Filter: 142003
35. 0.066 10,373.495 ↓ 1.6 24 5

Materialize (cost=0.00..2,291,498.09 rows=15 width=194) (actual time=54.465..2,074.699 rows=24 loops=5)

36. 10,373.429 10,373.429 ↓ 1.6 24 1

Subquery Scan on dat2 (cost=0.00..2,291,498.02 rows=15 width=194) (actual time=272.309..10,373.429 rows=24 loops=1)

  • Filter: (dat2.time_period_cd = 'CYTM'::text)
  • Rows Removed by Filter: 456
  • -> Seq Scan on f_position_store_pri_pack_pkg_sku_agg_2018_11_27_00_01_30 f_position_store_pri_pack_pkg_sku_agg_2018_11_27_00_01_30_1 (cost=0.00..2291460.52 rows=3000 width=298) (actual time=272.297..10373.290 rows=48 (...)
  • Filter: (((store_cd)::text = '100012506'::text) AND (position_id = 0))
  • Rows Removed by Filter: 25296529
37. 0.140 0.140 ↓ 0.0 0 10

Index Scan using d_store_2018_11_30_10_32_15_store_cd_idx on d_store_2018_11_30_10_32_15 d_store_2018_11_30_10_32_15_1 (cost=0.43..8.45 rows=1 width=10) (actual time=0.014..0.014 rows=0 loops=10)

  • Index Cond: ((store_cd)::text = '100012506'::text)
  • Filter: ((premise_type_cd)::text ~~ 'O'::text)
  • Rows Removed by Filter: 1
38. 0.000 0.000 ↓ 0.0 0

Seq Scan on d_item_sku_pkg_2018_11_27_11_24_53 d_item_sku_pkg_2018_11_27_11_24_53_1 (cost=0.00..8.43 rows=243 width=96) (never executed)

39.          

SubPlan (forNested Loop)

40. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.29..4.15 rows=1 width=4) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Index Scan using d_item_2018_11_27_11_20_18_master_pkg_cd on d_item_2018_11_27_11_20_18 d_item_2018_11_27_11_20_18_8 (cost=0.29..38.92 rows=10 width=4) (never executed)

  • Index Cond: ((master_pkg_cd)::text = (d_item_sku_pkg_2018_11_27_11_24_53_1.master_pkg_sku_cd)::text)
42. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.29..4.15 rows=1 width=5) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Index Scan using d_item_2018_11_27_11_20_18_master_pkg_cd on d_item_2018_11_27_11_20_18 d_item_2018_11_27_11_20_18_9 (cost=0.29..38.92 rows=10 width=5) (never executed)

  • Index Cond: ((master_pkg_cd)::text = (d_item_sku_pkg_2018_11_27_11_24_53_1.master_pkg_sku_cd)::text)
44. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.29..4.15 rows=1 width=4) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Index Scan using d_item_2018_11_27_11_20_18_master_pkg_cd on d_item_2018_11_27_11_20_18 d_item_2018_11_27_11_20_18_10 (cost=0.29..38.92 rows=10 width=4) (never executed)

  • Index Cond: ((master_pkg_cd)::text = (d_item_sku_pkg_2018_11_27_11_24_53_1.master_pkg_sku_cd)::text)
46. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.29..4.15 rows=1 width=2) (never executed)

47. 0.000 0.000 ↓ 0.0 0

Index Scan using d_item_2018_11_27_11_20_18_master_pkg_cd on d_item_2018_11_27_11_20_18 d_item_2018_11_27_11_20_18_11 (cost=0.29..38.92 rows=10 width=2) (never executed)

  • Index Cond: ((master_pkg_cd)::text = (d_item_sku_pkg_2018_11_27_11_24_53_1.master_pkg_sku_cd)::text)
48. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.29..4.15 rows=1 width=4) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Index Scan using d_item_2018_11_27_11_20_18_master_pkg_cd on d_item_2018_11_27_11_20_18 d_item_2018_11_27_11_20_18_12 (cost=0.29..38.92 rows=10 width=4) (never executed)

  • Index Cond: ((master_pkg_cd)::text = (d_item_sku_pkg_2018_11_27_11_24_53_1.master_pkg_sku_cd)::text)
50. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.29..4.15 rows=1 width=7) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Index Scan using d_item_2018_11_27_11_20_18_master_pkg_cd on d_item_2018_11_27_11_20_18 d_item_2018_11_27_11_20_18_13 (cost=0.29..38.92 rows=10 width=7) (never executed)

  • Index Cond: ((master_pkg_cd)::text = (d_item_sku_pkg_2018_11_27_11_24_53_1.master_pkg_sku_cd)::text)
52. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.29..6.12 rows=1 width=4) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Index Scan using d_item_2018_11_27_11_20_18_master_sku_cd on d_item_2018_11_27_11_20_18 d_item_2018_11_27_11_20_18_14 (cost=0.29..11.95 rows=2 width=4) (never executed)

  • Index Cond: ((master_sku_cd)::text = (d_item_sku_pkg_2018_11_27_11_24_53_1.master_pkg_sku_cd)::text)
54. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.29..6.12 rows=1 width=5) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Index Scan using d_item_2018_11_27_11_20_18_master_sku_cd on d_item_2018_11_27_11_20_18 d_item_2018_11_27_11_20_18_15 (cost=0.29..11.95 rows=2 width=5) (never executed)

  • Index Cond: ((master_sku_cd)::text = (d_item_sku_pkg_2018_11_27_11_24_53_1.master_pkg_sku_cd)::text)