explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 1zL

options
# exclusive inclusive rows x rows loops node
1. 0.068 41747.694 ↓ 15.5 31 1
→

Sort (cost=40000000092.95..40000000092.95 rows=2 width=148) (actual time=41747.694..41747.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 41747.626 ↓ 15.5 31 1
→

Subquery Scan on fractile_qry (cost=30000000092.86..30000000092.94 rows=2 width=148) (actual time=41747.598..41747.626 rows=31 loops=1)

3. 0.019 41747.620 ↓ 15.5 31 1
→

Unique (cost=30000000092.86..30000000092.92 rows=2 width=106) (actual time=41747.597..41747.620 rows=31 loops=1)

4. 0.066 41747.601 ↓ 15.5 31 1
→

Sort (cost=30000000092.86..30000000092.87 rows=2 width=106) (actual time=41747.596..41747.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 41747.535 ↓ 15.5 31 1
→

Append (cost=20000000042.17..20000000092.85 rows=2 width=106) (actual time=39332.205..41747.535 rows=31 loops=1)

6. 0.006 39332.263 ↓ 21.0 21 1
→

Subquery Scan on *SELECT* 1 (cost=20000000042.17..20000000042.22 rows=1 width=120) (actual time=39332.205..39332.263 rows=21 loops=1)

7. 3.267 39332.257 ↓ 21.0 21 1
→

HashAggregate (cost=20000000042.17..20000000042.21 rows=1 width=120) (actual time=39332.204..39332.257 rows=21 loops=1)

8. 7.002 39328.990 ↓ 3844.0 3844 1
→

WindowAgg (cost=20000000042.05..20000000042.14 rows=1 width=144) (actual time=39322.112..39328.990 rows=3844 loops=1)

9. 23.699 39321.988 ↓ 3844.0 3844 1
→

Sort (cost=20000000042.05..20000000042.06 rows=1 width=144) (actual time=39321.726..39321.988 rows=3844 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 39298.289 ↓ 3844.0 3844 1
→

WindowAgg (cost=10000000042.01..10000000042.04 rows=1 width=144) (actual time=39293.477..39298.289 rows=3844 loops=1)

11. 33.375 39293.263 ↓ 3844.0 3844 1
→

Sort (cost=10000000042.01..10000000042.02 rows=1 width=144) (actual time=39292.997..39293.263 rows=3844 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 39259.888 ↓ 3844.0 3844 1
→

Nested Loop Left Join (cost=0.00..42.00 rows=1 width=144) (actual time=106.694..39259.888 rows=3844 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 39245.486 ↓ 3844.0 3844 1
→

Nested Loop (cost=0.00..34.27 rows=1 width=116) (actual time=106.683..39245.486 rows=3844 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 38900.590 ↓ 86836.0 86836 1
→

Nested Loop (cost=0.00..32.94 rows=1 width=99) (actual time=105.152..38900.590 rows=86836 loops=1)

15. 74.699 38735.106 ↓ 86836.0 86836 1
→

Nested Loop (cost=0.00..27.26 rows=1 width=90) (actual time=105.136..38735.106 rows=86836 loops=1)

16. 350.838 38573.571 ↓ 86836.0 86836 1
→

Nested Loop (cost=0.00..20.98 rows=1 width=87) (actual time=105.112..38573.571 rows=86836 loops=1)

17. 342.273 848.296 ↓ 1205627.0 1205627 1
→

Nested Loop (cost=0.00..15.24 rows=1 width=75) (actual time=0.032..848.296 rows=1205627 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 ↓ 602813.5 1205627 1
→

Append (cost=0.00..14.16 rows=2 width=52) (actual time=0.019..506.014 rows=1205627 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 ↓ 1205627.0 1205627 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=1205627 loops=1)

  • Index Cond: ((s.pointdate = '2012-03-30'::date) AND (s.pointdate >= j.jobstartdate) AND (s.pointdate <= j.jobenddate))
22. 37374.437 37374.437 ↓ 0.0 0 1205627
→

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=1205627)

  • 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 86836
→

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=86836)

  • Index Cond: (p.stockid = s.stockid)
24. 86.836 86.836 ↑ 1.0 1 86836
→

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=86836)

  • Index Cond: (w.warehousefieldid = s.factorid)
25. 260.508 260.508 ↑ 1.2 4 86836
→

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

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

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

27. 7.688 7.688 ↓ 0.0 0 3844
→

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=3844)

  • 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 2415.265 ↓ 10.0 10 1
→

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

30. 2.171 2415.261 ↓ 10.0 10 1
→

HashAggregate (cost=50.60..50.63 rows=1 width=91) (actual time=2415.247..2415.261 rows=10 loops=1)

31. 0.297 2413.090 ↓ 963.0 963 1
→

Nested Loop Left Join (cost=0.00..50.58 rows=1 width=91) (actual time=5.843..2413.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 2410.867 ↓ 963.0 963 1
→

Nested Loop (cost=0.00..43.02 rows=1 width=59) (actual time=5.836..2410.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 2378.368 ↓ 10593.0 10593 1
→

Nested Loop (cost=0.00..41.75 rows=1 width=56) (actual time=5.668..2378.368 rows=10593 loops=1)

34. 18.437 2359.119 ↓ 3852.0 3852 1
→

Nested Loop (cost=0.00..36.32 rows=1 width=45) (actual time=5.655..2359.119 rows=3852 loops=1)

35. 20.617 52.851 ↓ 73801.0 73801 1
→

Nested Loop (cost=0.00..31.27 rows=1 width=49) (actual time=0.050..52.851 rows=73801 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 ↓ 10543.0 73801 1
→

Append (cost=0.00..30.12 rows=7 width=40) (actual time=0.023..32.218 rows=73801 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 ↓ 73801.0 73801 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=73801 loops=1)

  • Index Cond: ((s.pointdate = '2012-03-30'::date) AND (s.pointdate >= j.jobstartdate) AND (s.pointdate <= j.jobenddate))
40. 2287.831 2287.831 ↓ 0.0 0 73801
→

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=73801)

  • 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 3852
→

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=3852)

  • Index Cond: ((g.factorid = s.factorid) AND (g.characteristiccode = s.value))
42. 21.186 21.186 ↑ 1.0 1 10593
→

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

  • 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))