explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sGsI

Settings
# exclusive inclusive rows x rows loops node
1. 0.189 4,819.605 ↓ 531.0 1,062 1

Append (cost=8,700.67..1,658,756.70 rows=2 width=738) (actual time=598.673..4,819.605 rows=1,062 loops=1)

2. 4.680 4,350.192 ↓ 975.0 975 1

Hash Left Join (cost=8,700.67..1,548,920.42 rows=1 width=818) (actual time=598.673..4,350.192 rows=975 loops=1)

  • Hash Cond: ("left"((stk.stpgics)::text, 2) = (gicsmap.gsmsectorid)::text)
3. 1,013.162 4,345.501 ↓ 975.0 975 1

Nested Loop Left Join (cost=8,699.42..1,548,919.10 rows=1 width=801) (actual time=598.626..4,345.501 rows=975 loops=1)

  • Join Filter: (stk.stpprdid = e2.edprdid)
  • Rows Removed by Join Filter: 16,238,611
4. 0.751 1,067.414 ↓ 975.0 975 1

Nested Loop Left Join (cost=81.31..1,539,900.18 rows=1 width=797) (actual time=486.314..1,067.414 rows=975 loops=1)

5. 0.683 1,059.838 ↓ 975.0 975 1

Nested Loop Left Join (cost=80.88..1,539,896.22 rows=1 width=566) (actual time=486.301..1,059.838 rows=975 loops=1)

6. 0.394 1,054.280 ↓ 975.0 975 1

Nested Loop Left Join (cost=80.46..1,539,895.54 rows=1 width=533) (actual time=486.284..1,054.280 rows=975 loops=1)

7. 0.366 1,049.011 ↓ 975.0 975 1

Nested Loop Left Join (cost=80.03..1,539,895.09 rows=1 width=528) (actual time=486.264..1,049.011 rows=975 loops=1)

8. 4.735 1,046.695 ↓ 975.0 975 1

Nested Loop Left Join (cost=79.75..1,539,894.77 rows=1 width=485) (actual time=486.253..1,046.695 rows=975 loops=1)

  • Join Filter: (ipodate.ipoprdid = product.prdid)
  • Rows Removed by Join Filter: 49,725
9. 1.030 995.160 ↓ 975.0 975 1

Nested Loop Left Join (cost=79.75..1,539,890.38 rows=1 width=481) (actual time=486.176..995.160 rows=975 loops=1)

10. 0.701 994.130 ↓ 975.0 975 1

Nested Loop Left Join (cost=79.60..1,539,890.20 rows=1 width=380) (actual time=486.174..994.130 rows=975 loops=1)

  • Join Filter: (tickrule.tcrexcid = exchange.excid)
  • Rows Removed by Join Filter: 768
11. 0.561 989.529 ↓ 975.0 975 1

Nested Loop Left Join (cost=79.18..1,539,889.74 rows=1 width=380) (actual time=486.154..989.529 rows=975 loops=1)

12. 8.980 987.993 ↓ 975.0 975 1

Nested Loop Left Join (cost=79.03..1,539,889.57 rows=1 width=376) (actual time=486.147..987.993 rows=975 loops=1)

  • Join Filter: (marketinfo.mkiexcid = product.prdprimaryexcid)
  • Rows Removed by Join Filter: 136,509
13. 0.773 965.363 ↓ 975.0 975 1

Nested Loop Left Join (cost=34.34..1,539,843.00 rows=1 width=373) (actual time=485.813..965.363 rows=975 loops=1)

14. 0.391 958.740 ↓ 975.0 975 1

Nested Loop Left Join (cost=34.07..1,539,842.69 rows=1 width=370) (actual time=485.788..958.740 rows=975 loops=1)

15. 0.683 955.424 ↓ 975.0 975 1

Nested Loop Left Join (cost=33.92..1,539,842.51 rows=1 width=363) (actual time=485.776..955.424 rows=975 loops=1)

16. 0.456 952.791 ↓ 975.0 975 1

Nested Loop (cost=33.78..1,539,842.34 rows=1 width=357) (actual time=485.760..952.791 rows=975 loops=1)

17. 65.209 950.385 ↓ 975.0 975 1

Nested Loop (cost=33.64..1,539,842.11 rows=1 width=357) (actual time=485.755..950.385 rows=975 loops=1)

  • Join Filter: (productlisting.prlexcid = exchange.excid)
  • Rows Removed by Join Filter: 326,625
18. 0.152 0.152 ↑ 1.0 336 1

Index Scan using exchange_pk on exchange (cost=0.15..20.42 rows=339 width=12) (actual time=0.010..0.152 rows=336 loops=1)

19. 14.114 885.024 ↓ 975.0 975 336

Materialize (cost=33.49..1,539,816.61 rows=1 width=349) (actual time=0.001..2.634 rows=975 loops=336)

20. 328.881 870.910 ↓ 975.0 975 1

Nested Loop (cost=33.49..1,539,816.61 rows=1 width=349) (actual time=0.271..870.910 rows=975 loops=1)

  • Join Filter: ((product.prdid = stk.stpprdid) AND (productlisting.prlexcid = stk.stpexcid))
21. 56.443 542.029 ↓ 4.0 91,912 1

Nested Loop (cost=1.30..645,545.76 rows=23,170 width=260) (actual time=0.152..542.029 rows=91,912 loops=1)

22. 39.700 301.762 ↓ 2.0 91,912 1

Nested Loop (cost=0.87..621,050.81 rows=46,822 width=184) (actual time=0.121..301.762 rows=91,912 loops=1)

23. 64.136 64.136 ↑ 1.3 98,963 1

Index Scan using codes_isactive_idx on codes c (cost=0.43..98,639.78 rows=125,942 width=164) (actual time=0.065..64.136 rows=98,963 loops=1)

  • Index Cond: (cisactiveflag = 0)
  • Filter: (cmktid IS NOT NULL)
24. 197.926 197.926 ↑ 1.0 1 98,963

Index Scan using prlprdid_excid_todate on productlisting (cost=0.43..4.14 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=98,963)

  • Index Cond: ((prlprdid = c.cprdid) AND (prlexcid = c.cexcid))
  • Filter: (prlcurrency IS NOT NULL)
  • Rows Removed by Filter: 0
25. 183.824 183.824 ↑ 1.0 1 91,912

Index Scan using prdid_pk on product (cost=0.43..0.51 rows=1 width=76) (actual time=0.002..0.002 rows=1 loops=91,912)

  • Index Cond: (prdid = productlisting.prlprdid)
26. 0.000 0.000 ↓ 0.0 0 91,912

Index Scan using pk_stkprod_prdid_excid_fromdate_todate on stockproduct stk (cost=32.18..38.58 rows=1 width=101) (actual time=0.000..0.000 rows=0 loops=91,912)

  • Index Cond: ((stpprdid = c.cprdid) AND (stpexcid = c.cexcid) AND (stptodate = (SubPlan 1)))
27.          

SubPlan (for Index Scan)

28. 0.000 275.736 ↑ 1.0 1 91,912

Aggregate (cost=31.61..31.62 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=91,912)

29. 275.736 275.736 ↑ 7.0 1 91,912

Index Only Scan using pk_stkprod_prdid_excid_fromdate_todate on stockproduct (cost=0.56..31.59 rows=7 width=8) (actual time=0.002..0.003 rows=1 loops=91,912)

  • Index Cond: ((stpprdid = c.cprdid) AND (stpexcid = c.cexcid))
  • Heap Fetches: 55,889
30. 0.000 275.736 ↑ 1.0 1 91,912

Aggregate (cost=31.61..31.62 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=91,912)

31. 275.736 275.736 ↑ 7.0 1 91,912

Index Only Scan using pk_stkprod_prdid_excid_fromdate_todate on stockproduct (cost=0.56..31.59 rows=7 width=8) (actual time=0.002..0.003 rows=1 loops=91,912)

  • Index Cond: ((stpprdid = c.cprdid) AND (stpexcid = c.cexcid))
  • Heap Fetches: 55,889
32. 1.950 1.950 ↑ 1.0 1 975

Index Scan using country_pk on country (cost=0.14..0.22 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=975)

  • Index Cond: (ctyid = exchange.excctyid)
33. 1.950 1.950 ↓ 0.0 0 975

Index Scan using un_mapping_mapwhat_mapfrom on mappings (cost=0.14..0.16 rows=1 width=22) (actual time=0.002..0.002 rows=0 loops=975)

  • Index Cond: (((mapwhat)::text = 'RdsMktSegment'::text) AND ((mapfrom)::text = (stk.stpmarketsegment)::text))
34. 2.925 2.925 ↑ 1.0 1 975

Index Scan using pk_subtype_subinternalcode on subtype (cost=0.15..0.17 rows=1 width=11) (actual time=0.002..0.003 rows=1 loops=975)

  • Index Cond: ((subinternalcode)::text = (product.prdsubtype)::text)
35. 5.850 5.850 ↑ 1.0 1 975

Index Scan using mki_pk on marketinfo exc (cost=0.28..0.30 rows=1 width=28) (actual time=0.006..0.006 rows=1 loops=975)

  • Index Cond: (((mkimarketcode)::text = (c.cmktid)::text) AND (mkiexcid = c.cexcid))
  • Filter: ((mkiisintora)::text = ANY ('{Y,J}'::text[]))
  • Rows Removed by Filter: 0
36. 13.386 13.650 ↓ 2.4 141 975

HashAggregate (cost=44.68..45.26 rows=58 width=7) (actual time=0.000..0.014 rows=141 loops=975)

  • Group Key: marketinfo.mkiexcid, marketinfo.mkiexccode
37. 0.264 0.264 ↑ 1.3 208 1

Seq Scan on marketinfo (cost=0.00..43.35 rows=267 width=7) (actual time=0.009..0.264 rows=208 loops=1)

  • Filter: (((mkitype)::text = 'CS'::text) AND ((mkiisintora)::text = 'Y'::text))
  • Rows Removed by Filter: 1,277
38. 0.975 0.975 ↓ 0.0 0 975

Index Only Scan using moprdid_pk on mothers (cost=0.15..0.17 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=975)

  • Index Cond: (moprdid = product.prdid)
  • Heap Fetches: 0
39. 3.900 3.900 ↑ 1.0 1 975

Index Scan using tcr_pk on tickrule (cost=0.43..0.45 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=975)

  • Index Cond: (tcrprdid = product.prdid)
40. 0.000 0.000 ↓ 0.0 0 975

Index Scan using tsz_pk on ticksizetable (cost=0.15..0.17 rows=1 width=109) (actual time=0.000..0.000 rows=0 loops=975)

  • Index Cond: (tszid = tickrule.tcrticksizeid)
41. 46.800 46.800 ↓ 1.1 51 975

Seq Scan on ipodate (cost=0.00..3.82 rows=46 width=8) (actual time=0.014..0.048 rows=51 loops=975)

  • Filter: (ipodate >= (('now'::cstring)::date - '1 day'::interval))
  • Rows Removed by Filter: 45
42. 1.950 1.950 ↓ 0.0 0 975

Index Scan using pnprdidlang_pk on productname (cost=0.28..0.30 rows=1 width=51) (actual time=0.002..0.002 rows=0 loops=975)

  • Index Cond: ((pnprdid = product.prdid) AND ((pnlanguage)::text = 'JP'::text))
43. 4.875 4.875 ↑ 1.0 1 975

Index Scan using tci_prdid_excid_pk on technicalindicators (cost=0.42..0.44 rows=1 width=13) (actual time=0.005..0.005 rows=1 loops=975)

  • Index Cond: ((tciprdid = stk.stpprdid) AND (tciexcid = stk.stpexcid))
44. 4.875 4.875 ↑ 1.0 1 975

Index Scan using pk_stockissuer_stiprdid_stiexcid on stockissuer (cost=0.42..0.67 rows=1 width=41) (actual time=0.005..0.005 rows=1 loops=975)

  • Index Cond: ((c.cprdid = stiprdid) AND (c.cexcid = stiexcid))
45. 6.825 6.825 ↑ 1.0 1 975

Index Scan using multiplecodes_prd_exc_idx on multiplecodes (cost=0.43..3.95 rows=1 width=247) (actual time=0.007..0.007 rows=1 loops=975)

  • Index Cond: ((c.cprdid = mpcprdid) AND (c.cexcid = mpcexcid))
46. 2,203.706 2,264.925 ↓ 1.4 16,655 975

HashAggregate (cost=8,618.10..8,741.43 rows=12,333 width=8) (actual time=0.115..2.323 rows=16,655 loops=975)

  • Group Key: e2.edprdid
47. 61.219 61.219 ↑ 1.0 307,066 1

Seq Scan on earningsdate e2 (cost=0.00..7,079.76 rows=307,668 width=8) (actual time=0.009..61.219 rows=307,066 loops=1)

  • Filter: (edearningsdate < now())
  • Rows Removed by Filter: 9,611
48. 0.006 0.011 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=17) (actual time=0.011..0.011 rows=11 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
49. 0.005 0.005 ↑ 1.0 11 1

Seq Scan on gicssectornamemapping gicsmap (cost=0.00..1.11 rows=11 width=17) (actual time=0.004..0.005 rows=11 loops=1)

50. 0.069 469.224 ↓ 87.0 87 1

Subquery Scan on *SELECT* 2 (cost=8,620.96..109,836.28 rows=1 width=658) (actual time=137.351..469.224 rows=87 loops=1)

51. 0.769 469.155 ↓ 87.0 87 1

Nested Loop Left Join (cost=8,620.96..109,836.27 rows=1 width=658) (actual time=137.347..469.155 rows=87 loops=1)

52. 0.024 467.864 ↓ 87.0 87 1

Nested Loop Left Join (cost=8,620.53..109,827.94 rows=1 width=427) (actual time=137.236..467.864 rows=87 loops=1)

53. 0.413 467.666 ↓ 87.0 87 1

Nested Loop Left Join (cost=8,620.25..109,827.62 rows=1 width=384) (actual time=137.231..467.666 rows=87 loops=1)

  • Join Filter: (ipodate_1.ipoprdid = product_1.prdid)
  • Rows Removed by Join Filter: 4,437
54. 0.086 463.251 ↓ 87.0 87 1

Nested Loop Left Join (cost=8,620.25..109,823.23 rows=1 width=380) (actual time=137.166..463.251 rows=87 loops=1)

55. 81.873 463.165 ↓ 87.0 87 1

Nested Loop Left Join (cost=8,620.10..109,823.05 rows=1 width=279) (actual time=137.149..463.165 rows=87 loops=1)

  • Join Filter: (war.wrpprdid = e2_1.edprdid)
  • Rows Removed by Join Filter: 1,448,985
56. 0.072 92.104 ↓ 87.0 87 1

Nested Loop Left Join (cost=2.00..100,804.13 rows=1 width=275) (actual time=27.466..92.104 rows=87 loops=1)

  • Join Filter: (tickrule_1.tcrexcid = exchange_1.excid)
57. 2.902 91.858 ↓ 87.0 87 1

Nested Loop Left Join (cost=1.57..100,803.66 rows=1 width=275) (actual time=27.454..91.858 rows=87 loops=1)

  • Join Filter: ((subtype_1.subinternalcode)::text = (product_1.prdsubtype)::text)
  • Rows Removed by Join Filter: 31,494
58. 9.603 86.955 ↓ 87.0 87 1

Nested Loop (cost=1.57..100,791.52 rows=1 width=268) (actual time=27.391..86.955 rows=87 loops=1)

  • Join Filter: (war.wrpccyid = currency.ccyprdid)
  • Rows Removed by Join Filter: 138,765
59. 1.629 68.739 ↓ 87.0 87 1

Nested Loop (cost=1.57..100,742.61 rows=1 width=268) (actual time=27.183..68.739 rows=87 loops=1)

  • Join Filter: (exchange_1.excctyid = country_1.ctyid)
  • Rows Removed by Join Filter: 21,576
60. 2.159 65.805 ↓ 87.0 87 1

Nested Loop (cost=1.57..100,734.01 rows=1 width=272) (actual time=27.176..65.805 rows=87 loops=1)

  • Join Filter: (codes.cexcid = exchange_1.excid)
  • Rows Removed by Join Filter: 29,145
61. 0.045 61.819 ↓ 87.0 87 1

Nested Loop (cost=1.57..100,722.38 rows=1 width=264) (actual time=27.165..61.819 rows=87 loops=1)

  • Join Filter: (codes.cprdid = product_1.prdid)
62. 0.026 61.426 ↓ 87.0 87 1

Nested Loop (cost=1.13..100,719.02 rows=1 width=188) (actual time=27.151..61.426 rows=87 loops=1)

63. 0.071 61.052 ↓ 1.8 87 1

Nested Loop (cost=0.71..100,423.10 rows=49 width=168) (actual time=27.134..61.052 rows=87 loops=1)

64. 60.633 60.633 ↑ 14.4 87 1

Index Scan using codes_isactive_idx on codes (cost=0.43..99,905.74 rows=1,256 width=164) (actual time=27.120..60.633 rows=87 loops=1)

  • Index Cond: (cisactiveflag = 0)
  • Filter: ((cmktid IS NOT NULL) AND (("position"((cmktid)::text, 'WAR.CS.AUX.AX'::text) = 1) OR ("position"((cmktid)::text, 'WAR.CS.IRL.I'::text) = 1)))
  • Rows Removed by Filter: 98,876
65. 0.348 0.348 ↑ 1.0 1 87

Index Scan using mki_pk on marketinfo exc_1 (cost=0.28..0.40 rows=1 width=25) (actual time=0.004..0.004 rows=1 loops=87)

  • Index Cond: (((mkimarketcode)::text = (codes.cmktid)::text) AND (mkiexcid = codes.cexcid))
  • Filter: ((mkiisintora)::text = ANY ('{Y,J}'::text[]))
66. 0.348 0.348 ↑ 1.0 1 87

Index Scan using wrpprdexcmattypestrike_pk on warrantproduct war (cost=0.42..6.03 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=87)

  • Index Cond: ((wrpprdid = codes.cprdid) AND (wrpexcid = codes.cexcid))
  • Filter: (wrpccyid IS NOT NULL)
67. 0.348 0.348 ↑ 1.0 1 87

Index Scan using prdid_pk on product product_1 (cost=0.43..3.34 rows=1 width=76) (actual time=0.003..0.004 rows=1 loops=87)

  • Index Cond: (prdid = war.wrpprdid)
68. 1.827 1.827 ↑ 1.0 336 87

Seq Scan on exchange exchange_1 (cost=0.00..7.39 rows=339 width=12) (actual time=0.001..0.021 rows=336 loops=87)

69. 1.305 1.305 ↑ 1.0 249 87

Seq Scan on country country_1 (cost=0.00..5.49 rows=249 width=4) (actual time=0.002..0.015 rows=249 loops=87)

70. 8.613 8.613 ↑ 1.0 1,596 87

Seq Scan on currency (cost=0.00..28.96 rows=1,596 width=8) (actual time=0.002..0.099 rows=1,596 loops=87)

71. 2.001 2.001 ↑ 1.0 362 87

Seq Scan on subtype subtype_1 (cost=0.00..7.62 rows=362 width=11) (actual time=0.002..0.023 rows=362 loops=87)

72. 0.174 0.174 ↓ 0.0 0 87

Index Scan using tcr_pk on tickrule tickrule_1 (cost=0.43..0.45 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=87)

  • Index Cond: (tcrprdid = product_1.prdid)
73. 233.078 289.188 ↓ 1.4 16,655 87

HashAggregate (cost=8,618.10..8,741.43 rows=12,333 width=8) (actual time=1.226..3.324 rows=16,655 loops=87)

  • Group Key: e2_1.edprdid
74. 56.110 56.110 ↑ 1.0 307,066 1

Seq Scan on earningsdate e2_1 (cost=0.00..7,079.76 rows=307,668 width=8) (actual time=0.006..56.110 rows=307,066 loops=1)

  • Filter: (edearningsdate < now())
  • Rows Removed by Filter: 9,611
75. 0.000 0.000 ↓ 0.0 0 87

Index Scan using tsz_pk on ticksizetable ticksizetable_1 (cost=0.15..0.17 rows=1 width=109) (actual time=0.000..0.000 rows=0 loops=87)

  • Index Cond: (tszid = tickrule_1.tcrticksizeid)
76. 4.002 4.002 ↓ 1.1 51 87

Seq Scan on ipodate ipodate_1 (cost=0.00..3.82 rows=46 width=8) (actual time=0.014..0.046 rows=51 loops=87)

  • Filter: (ipodate >= (('now'::cstring)::date - '1 day'::interval))
  • Rows Removed by Filter: 45
77. 0.174 0.174 ↓ 0.0 0 87

Index Scan using pnprdidlang_pk on productname productname_1 (cost=0.28..0.30 rows=1 width=51) (actual time=0.002..0.002 rows=0 loops=87)

  • Index Cond: ((pnprdid = product_1.prdid) AND ((pnlanguage)::text = 'JP'::text))
78. 0.522 0.522 ↑ 1.0 1 87

Index Scan using multiplecodes_prd_exc_idx on multiplecodes multiplecodes_1 (cost=0.43..8.29 rows=1 width=247) (actual time=0.006..0.006 rows=1 loops=87)

  • Index Cond: ((codes.cprdid = mpcprdid) AND (codes.cexcid = mpcexcid))