explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 1zL

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 0.068 41,747.694 ↓ 15.5 31 1

Sort (cost=40,000,000,092.95..40,000,000,092.95 rows=2 width=148) (actual time=41,747.694..41,747.694 rows=31 loops=1)

  • Sort Key: fractile_qry.jobid, fractile_qry.factorid, fractile_qry.jobfactorid, fractile_qry.maxfractile, fractile_qry.fractile
  • Sort Method: quicksort Memory: 55kB
2. 0.006 41,747.626 ↓ 15.5 31 1

Subquery Scan on fractile_qry (cost=30,000,000,092.86..30,000,000,092.94 rows=2 width=148) (actual time=41,747.598..41,747.626 rows=31 loops=1)

3. 0.019 41,747.620 ↓ 15.5 31 1

Unique (cost=30,000,000,092.86..30,000,000,092.92 rows=2 width=106) (actual time=41,747.597..41,747.620 rows=31 loops=1)

4. 0.066 41,747.601 ↓ 15.5 31 1

Sort (cost=30,000,000,092.86..30,000,000,092.87 rows=2 width=106) (actual time=41,747.596..41,747.601 rows=31 loops=1)

  • Sort Key: *SELECT* 1.pointdate, *SELECT* 1.jobid, *SELECT* 1.stockids, *SELECT* 1.factorid, *SELECT* 1.minbreakpoint, *SELECT* 1.maxbreakpoint, *SELECT* 1.fractile, *SELECT* 1.maxfractile, *SELECT* 1.jobfactorid, *SELECT* 1.layer
  • Sort Method: quicksort Memory: 55kB
5. 0.007 41,747.535 ↓ 15.5 31 1

Append (cost=20,000,000,042.17..20,000,000,092.85 rows=2 width=106) (actual time=39,332.205..41,747.535 rows=31 loops=1)

6. 0.006 39,332.263 ↓ 21.0 21 1

Subquery Scan on *SELECT* 1 (cost=20,000,000,042.17..20,000,000,042.22 rows=1 width=120) (actual time=39,332.205..39,332.263 rows=21 loops=1)

7. 3.267 39,332.257 ↓ 21.0 21 1

HashAggregate (cost=20,000,000,042.17..20,000,000,042.21 rows=1 width=120) (actual time=39,332.204..39,332.257 rows=21 loops=1)

8. 7.002 39,328.990 ↓ 3,844.0 3,844 1

WindowAgg (cost=20,000,000,042.05..20,000,000,042.14 rows=1 width=144) (actual time=39,322.112..39,328.990 rows=3,844 loops=1)

9. 23.699 39,321.988 ↓ 3,844.0 3,844 1

Sort (cost=20,000,000,042.05..20,000,000,042.06 rows=1 width=144) (actual time=39,321.726..39,321.988 rows=3,844 loops=1)

  • Sort Key: f.jobfactorid, (COALESCE(ff.fractile, '1'::text)), (CASE WHEN (w.splitdirection = 'SHARE'::text) THEN ((s.value / p.lastcumsplitfactor) * s.cumsplitfactor) WHEN (w.splitdirection = 'PSHR'::text) THEN ((s.value * p.lastcumsplitfactor) / s.cumsplitfactor) ELSE s.value END)
  • Sort Method: quicksort Memory: 787kB
10. 5.026 39,298.289 ↓ 3,844.0 3,844 1

WindowAgg (cost=10,000,000,042.01..10,000,000,042.04 rows=1 width=144) (actual time=39,293.477..39,298.289 rows=3,844 loops=1)

11. 33.375 39,293.263 ↓ 3,844.0 3,844 1

Sort (cost=10,000,000,042.01..10,000,000,042.02 rows=1 width=144) (actual time=39,292.997..39,293.263 rows=3,844 loops=1)

  • Sort Key: f.jobfactorid, (COALESCE(ff.fractile, '1'::text)), (CASE WHEN (w.splitdirection = 'SHARE'::text) THEN ((s.value / p.lastcumsplitfactor) * s.cumsplitfactor) WHEN (w.splitdirection = 'PSHR'::text) THEN ((s.value * p.lastcumsplitfactor) / s.cumsplitfactor) ELSE s.value END)
  • Sort Method: quicksort Memory: 637kB
12. 6.714 39,259.888 ↓ 3,844.0 3,844 1

Nested Loop Left Join (cost=0.00..42.00 rows=1 width=144) (actual time=106.694..39,259.888 rows=3,844 loops=1)

  • Join Filter: ((f.layerjobfactorid = ff.jobfactorid) AND (s.pointdate = ff.pointdate) AND (s.stockid = (unnest(ff.stockids))) AND (f.jobfactorid = jf.jobfactorid))
13. 84.388 39,245.486 ↓ 3,844.0 3,844 1

Nested Loop (cost=0.00..34.27 rows=1 width=116) (actual time=106.683..39,245.486 rows=3,844 loops=1)

  • Join Filter: ((s.factorid = f.factorid) AND ((f.maxdate IS NULL) OR ('2012-03-30'::date > f.maxdate) OR (f.mindate IS NULL) OR ('2012-03-30'::date < f.mindate) OR (j.dooverwrite IS TRUE)))
14. 78.648 38,900.590 ↓ 86,836.0 86,836 1

Nested Loop (cost=0.00..32.94 rows=1 width=99) (actual time=105.152..38,900.590 rows=86,836 loops=1)

15. 74.699 38,735.106 ↓ 86,836.0 86,836 1

Nested Loop (cost=0.00..27.26 rows=1 width=90) (actual time=105.136..38,735.106 rows=86,836 loops=1)

16. 350.838 38,573.571 ↓ 86,836.0 86,836 1

Nested Loop (cost=0.00..20.98 rows=1 width=87) (actual time=105.112..38,573.571 rows=86,836 loops=1)

17. 342.273 848.296 ↓ 1,205,627.0 1,205,627 1

Nested Loop (cost=0.00..15.24 rows=1 width=75) (actual time=0.032..848.296 rows=1,205,627 loops=1)

  • Join Filter: ((s.pointdate >= j.jobstartdate) AND (s.pointdate <= j.jobenddate))
18. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on tbljob j (cost=0.00..1.04 rows=1 width=17) (actual time=0.009..0.009 rows=1 loops=1)

  • Filter: ((universeid = 41) AND (jobid = 4))
19. 141.056 506.014 ↓ 602,813.5 1,205,627 1

Append (cost=0.00..14.16 rows=2 width=52) (actual time=0.019..506.014 rows=1,205,627 loops=1)

20. 0.003 0.003 ↓ 0.0 0 1

Index Scan using tblstockfactor_idx on tblstockfactor s (cost=0.00..6.28 rows=1 width=76) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((s.pointdate = '2012-03-30'::date) AND (s.pointdate >= j.jobstartdate) AND (s.pointdate <= j.jobenddate))
21. 364.955 364.955 ↓ 1,205,627.0 1,205,627 1

Index Scan using tblstockfactor_2012_pointdate_idx on tblstockfactor_2012 s (cost=0.00..7.89 rows=1 width=27) (actual time=0.016..364.955 rows=1,205,627 loops=1)

  • Index Cond: ((s.pointdate = '2012-03-30'::date) AND (s.pointdate >= j.jobstartdate) AND (s.pointdate <= j.jobenddate))
22. 37,374.437 37,374.437 ↓ 0.0 0 1,205,627

Index Scan using tblbmarkholdings_tmp_date_extract_date_stock_id_benchmark_i_idx on tblbmarkholdings_tmp b (cost=0.00..5.73 rows=1 width=24) (actual time=0.030..0.031 rows=0 loops=1,205,627)

  • Index Cond: ((b.date = '2012-03-30'::date) AND (b.stock_id = s.stockid) AND (b.benchmark_id = 41))
23. 86.836 86.836 ↑ 1.0 1 86,836

Index Scan using tblstocklastsplit_pkey on tblstocklastsplit p (cost=0.00..6.27 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=86,836)

  • Index Cond: (p.stockid = s.stockid)
24. 86.836 86.836 ↑ 1.0 1 86,836

Index Scan using tblwarehousefields_pkey on tblwarehousefields w (cost=0.00..5.67 rows=1 width=9) (actual time=0.001..0.001 rows=1 loops=86,836)

  • Index Cond: (w.warehousefieldid = s.factorid)
25. 260.508 260.508 ↑ 1.2 4 86,836

Seq Scan on tbljobfactor f (cost=0.00..1.25 rows=5 width=38) (actual time=0.001..0.003 rows=4 loops=86,836)

  • Filter: ((f.maxfractile > 0) AND (f.jobid = 4) AND (f.layerjobfactorid = 0))
26. 0.000 7.688 ↓ 0.0 0 3,844

Nested Loop (cost=0.00..7.58 rows=5 width=361) (actual time=0.002..0.002 rows=0 loops=3,844)

27. 7.688 7.688 ↓ 0.0 0 3,844

Index Scan using tblfactorfractile_idx on tblfactorfractile ff (cost=0.00..6.27 rows=1 width=361) (actual time=0.002..0.002 rows=0 loops=3,844)

  • Index Cond: ((jobid = 4) AND (pointdate = '2012-03-30'::date))
  • Filter: (jobfactorid = 0)
28. 0.000 0.000 ↓ 0.0 0

Seq Scan on tbljobfactor jf (cost=0.00..1.25 rows=5 width=12) (actual time=.. rows= loops=0)

  • Filter: ((jf.maxfractile > 0) AND (jf.jobid = 4) AND (jf.layerjobfactorid = 0))
29. 0.004 2,415.265 ↓ 10.0 10 1

Subquery Scan on *SELECT* 2 (cost=50.60..50.64 rows=1 width=91) (actual time=2,415.248..2,415.265 rows=10 loops=1)

30. 2.171 2,415.261 ↓ 10.0 10 1

HashAggregate (cost=50.60..50.63 rows=1 width=91) (actual time=2,415.247..2,415.261 rows=10 loops=1)

31. 0.297 2,413.090 ↓ 963.0 963 1

Nested Loop Left Join (cost=0.00..50.58 rows=1 width=91) (actual time=5.843..2,413.090 rows=963 loops=1)

  • Join Filter: ((f.layerjobfactorid = ff.jobfactorid) AND (s.pointdate = ff.pointdate) AND (s.stockid = (unnest(ff.stockids))) AND (f.jobfactorid = jf.jobfactorid))
32. 11.313 2,410.867 ↓ 963.0 963 1

Nested Loop (cost=0.00..43.02 rows=1 width=59) (actual time=5.836..2,410.867 rows=963 loops=1)

  • Join Filter: ((s.factorid = f.factorid) AND (g.characteristicid = f.characteristicid) AND ((f.maxdate IS NULL) OR ('2012-03-30'::date > f.maxdate) OR (f.mindate IS NULL) OR ('2012-03-30'::date < f.mindate) OR (j.dooverwrite IS TRUE)))
33. 3.841 2,378.368 ↓ 10,593.0 10,593 1

Nested Loop (cost=0.00..41.75 rows=1 width=56) (actual time=5.668..2,378.368 rows=10,593 loops=1)

34. 18.437 2,359.119 ↓ 3,852.0 3,852 1

Nested Loop (cost=0.00..36.32 rows=1 width=45) (actual time=5.655..2,359.119 rows=3,852 loops=1)

35. 20.617 52.851 ↓ 73,801.0 73,801 1

Nested Loop (cost=0.00..31.27 rows=1 width=49) (actual time=0.050..52.851 rows=73,801 loops=1)

  • Join Filter: ((s.pointdate >= j.jobstartdate) AND (s.pointdate <= j.jobenddate))
36. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on tbljob j (cost=0.00..1.04 rows=1 width=17) (actual time=0.015..0.016 rows=1 loops=1)

  • Filter: ((jobid = 4) AND (universeid = 41))
37. 8.925 32.218 ↓ 10,543.0 73,801 1

Append (cost=0.00..30.12 rows=7 width=40) (actual time=0.023..32.218 rows=73,801 loops=1)

38. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on tblstockcharacteristic s (cost=0.00..23.75 rows=6 width=44) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (s.pointdate = '2012-03-30'::date)
39. 23.292 23.292 ↓ 73,801.0 73,801 1

Index Scan using tblstockcharacteristic_2012_pointdate_idx on tblstockcharacteristic_2012 s (cost=0.00..6.37 rows=1 width=15) (actual time=0.021..23.292 rows=73,801 loops=1)

  • Index Cond: ((s.pointdate = '2012-03-30'::date) AND (s.pointdate >= j.jobstartdate) AND (s.pointdate <= j.jobenddate))
40. 2,287.831 2,287.831 ↓ 0.0 0 73,801

Index Scan using tblbmarkholdings_tmp_date_extract_date_stock_id_benchmark_i_idx on tblbmarkholdings_tmp b (cost=0.00..5.04 rows=1 width=12) (actual time=0.030..0.031 rows=0 loops=73,801)

  • Index Cond: ((b.date = '2012-03-30'::date) AND (b.stock_id = s.stockid) AND (b.benchmark_id = 41))
41. 15.408 15.408 ↓ 3.0 3 3,852

Index Scan using tblstockcharacteristicgroup_idx on tblstockcharacteristicgroup g (cost=0.00..5.41 rows=1 width=15) (actual time=0.003..0.004 rows=3 loops=3,852)

  • Index Cond: ((g.factorid = s.factorid) AND (g.characteristiccode = s.value))
42. 21.186 21.186 ↑ 1.0 1 10,593

Seq Scan on tbljobfactor f (cost=0.00..1.25 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=10,593)

  • Filter: ((f.jobid = 4) AND (f.layerjobfactorid = 0) AND (f.maxfractile = 0))
43. 0.963 1.926 ↓ 0.0 0 963

Nested Loop (cost=0.00..7.53 rows=1 width=361) (actual time=0.002..0.002 rows=0 loops=963)

44. 0.963 0.963 ↓ 0.0 0 963

Index Scan using tblfactorfractile_idx on tblfactorfractile ff (cost=0.00..6.27 rows=1 width=361) (actual time=0.001..0.001 rows=0 loops=963)

  • Index Cond: ((jobid = 4) AND (pointdate = '2012-03-30'::date))
  • Filter: (jobfactorid = 0)
45. 0.000 0.000 ↓ 0.0 0

Seq Scan on tbljobfactor jf (cost=0.00..1.25 rows=1 width=12) (actual time=.. rows= loops=0)

  • Filter: ((jf.jobid = 4) AND (jf.layerjobfactorid = 0) AND (jf.maxfractile = 0))