explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Kxos

Settings
# exclusive inclusive rows x rows loops node
1. 4,191.604 58,866.121 ↑ 255,967.5 16,300 1

Merge Left Join (cost=43,149,614.77..1,107,190,621.53 rows=4,172,270,384 width=657) (actual time=58,841.045..58,866.121 rows=16,300 loops=1)

  • Merge Cond: ((ranked_analysis.forward_buckets_id = fwd.id) AND (ranked_analysis.evaluated_at = fwd.evaluated_at))
2. 14.800 9,183.399 ↑ 432.3 16,300 1

Sort (cost=30,635,498.65..30,653,116.13 rows=7,046,991 width=441) (actual time=9,180.750..9,183.399 rows=16,300 loops=1)

  • Sort Key: ranked_analysis.forward_buckets_id, ranked_analysis.evaluated_at
  • Sort Method: external merge Disk: 2712kB
3. 4.584 9,168.599 ↑ 432.3 16,300 1

Hash Left Join (cost=23,390,664.75..25,570,659.60 rows=7,046,991 width=441) (actual time=8,135.491..9,168.599 rows=16,300 loops=1)

  • Hash Cond: ((p.rds_id = ranked_analysis_1.rds_id) AND (ranked_analysis.unit_scenario_id = ranked_analysis_1.unit_scenario_id))
4. 5.036 9,163.688 ↑ 432.3 16,300 1

Hash Left Join (cost=23,389,462.79..25,516,603.45 rows=7,046,991 width=433) (actual time=8,135.152..9,163.688 rows=16,300 loops=1)

  • Hash Cond: (p.rds_id = ranked_analysis.rds_id)
5. 171.235 9,155.740 ↑ 471.7 14,940 1

Hash Join (cost=23,376,279.63..25,450,215.51 rows=7,046,991 width=169) (actual time=8,132.226..9,155.740 rows=14,940 loops=1)

  • Hash Cond: (p.rds_id = o.rds_id)
6. 29.734 7,003.485 ↑ 25.9 271,942 1

Subquery Scan on p (cost=23,060,613.57..24,822,361.32 rows=7,046,991 width=84) (actual time=6,149.353..7,003.485 rows=271,942 loops=1)

7. 668.335 6,973.751 ↑ 25.9 271,942 1

GroupAggregate (cost=23,060,613.57..24,751,891.41 rows=7,046,991 width=140) (actual time=6,149.350..6,973.751 rows=271,942 loops=1)

  • Group Key: top_day_slasher_positions_positions_cm.position_date, top_day_slasher_positions_positions_cm.trading_desk_id, top_day_slasher_positions_positions_cm.trading_group_id, top_day_slasher_positions_positions_cm.clearing_account_id, top_day_slasher_positions_positions_cm.rds_id
  • Filter: (sum(top_day_slasher_positions_positions_cm.position_quantity) <> '0'::double precision)
  • Rows Removed by Filter: 150509
8. 725.349 6,305.416 ↑ 120.9 582,996 1

Sort (cost=23,060,613.57..23,236,788.34 rows=70,469,910 width=84) (actual time=6,149.303..6,305.416 rows=582,996 loops=1)

  • Sort Key: top_day_slasher_positions_positions_cm.position_date, top_day_slasher_positions_positions_cm.trading_desk_id, top_day_slasher_positions_positions_cm.trading_group_id, top_day_slasher_positions_positions_cm.clearing_account_id, top_day_slasher_positions_positions_cm.rds_id
  • Sort Method: external merge Disk: 45448kB
9. 47.967 5,580.067 ↑ 120.9 582,996 1

Result (cost=0.00..3,758,395.20 rows=70,469,910 width=84) (actual time=968.620..5,580.067 rows=582,996 loops=1)

10. 37.894 5,532.100 ↑ 120.9 582,996 1

Append (cost=0.00..3,053,696.10 rows=70,469,910 width=84) (actual time=968.619..5,532.100 rows=582,996 loops=1)

11. 1,001.181 1,001.181 ↑ 127.1 100,496 1

Seq Scan on top_day_slasher_positions_positions_cm (cost=0.00..553,373.60 rows=12,770,160 width=84) (actual time=968.617..1,001.181 rows=100,496 loops=1)

12. 1,091.949 1,091.949 ↑ 121.5 115,071 1

Seq Scan on top_day_slasher_positions_positions_etfs (cost=0.00..605,852.00 rows=13,981,200 width=84) (actual time=1,057.162..1,091.949 rows=115,071 loops=1)

13. 1,370.366 1,370.366 ↑ 122.9 143,480 1

Seq Scan on top_day_slasher_positions_positions_fxoptions (cost=0.00..764,194.60 rows=17,635,260 width=84) (actual time=1,326.548..1,370.366 rows=143,480 loops=1)

14. 743.365 743.365 ↑ 108.1 88,631 1

Seq Scan on top_day_slasher_positions_positions_other (cost=0.00..414,999.00 rows=9,576,900 width=84) (actual time=720.690..743.365 rows=88,631 loops=1)

15. 1,287.345 1,287.345 ↑ 122.0 135,318 1

Seq Scan on top_day_slasher_positions_positions_sg (cost=0.00..715,276.90 rows=16,506,390 width=84) (actual time=1,245.966..1,287.345 rows=135,318 loops=1)

16. 176.791 1,981.020 ↑ 2.5 587,883 1

Hash (cost=274,986.12..274,986.12 rows=1,446,396 width=101) (actual time=1,981.020..1,981.020 rows=587,883 loops=1)

  • Buckets: 32768 Batches: 64 Memory Usage: 1260kB
17. 460.214 1,804.229 ↑ 2.5 587,883 1

Hash Join (cost=148,990.21..274,986.12 rows=1,446,396 width=101) (actual time=966.057..1,804.229 rows=587,883 loops=1)

  • Hash Cond: (o.option_listing_id = ol.id)
18. 378.296 378.296 ↓ 1.1 1,559,065 1

Seq Scan on options o (cost=0.00..76,514.96 rows=1,446,396 width=49) (actual time=0.005..378.296 rows=1,559,065 loops=1)

19. 8.709 965.719 ↑ 3.5 32,314 1

Hash (cost=146,215.39..146,215.39 rows=114,546 width=68) (actual time=965.719..965.719 rows=32,314 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 1202kB
20. 4.511 957.010 ↑ 3.5 32,314 1

Hash Left Join (cost=130,866.94..146,215.39 rows=114,546 width=68) (actual time=785.490..957.010 rows=32,314 loops=1)

  • Hash Cond: (f.future_product_id = sfp.future_product_id)
21. 6.232 952.487 ↑ 3.5 32,314 1

Hash Join (cost=130,865.51..145,913.22 rows=114,546 width=52) (actual time=785.466..952.487 rows=32,314 loops=1)

  • Hash Cond: (f.future_product_id = fp.id)
22. 122.319 900.308 ↑ 3.5 32,314 1

Hash Join (cost=109,675.90..124,422.91 rows=114,546 width=47) (actual time=739.451..900.308 rows=32,314 loops=1)

  • Hash Cond: (ol.underlying_rds_id = f.rds_id)
23. 30.845 46.930 ↑ 1.1 101,924 1

Hash Join (cost=3,783.77..7,964.09 rows=114,546 width=44) (actual time=7.432..46.930 rows=101,924 loops=1)

  • Hash Cond: (ol.option_product_id = op.id)
24. 8.688 8.688 ↑ 1.1 101,924 1

Seq Scan on option_listings ol (cost=0.00..3,879.46 rows=114,546 width=44) (actual time=0.005..8.688 rows=101,924 loops=1)

25. 1.289 7.397 ↑ 1.1 6,829 1

Hash (cost=3,693.34..3,693.34 rows=7,234 width=16) (actual time=7.397..7.397 rows=6,829 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 414kB
26. 6.108 6.108 ↑ 1.1 6,829 1

Seq Scan on option_products op (cost=0.00..3,693.34 rows=7,234 width=16) (actual time=0.004..6.108 rows=6,829 loops=1)

27. 230.871 731.059 ↓ 1.1 1,115,760 1

Hash (cost=84,437.84..84,437.84 rows=1,056,184 width=35) (actual time=731.059..731.059 rows=1,115,760 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 2943kB
28. 500.188 500.188 ↓ 1.1 1,115,760 1

Seq Scan on futures f (cost=0.00..84,437.84 rows=1,056,184 width=35) (actual time=0.006..500.188 rows=1,115,760 loops=1)

29. 7.908 45.947 ↑ 1.0 47,898 1

Hash (cost=20,575.38..20,575.38 rows=49,138 width=13) (actual time=45.947..45.947 rows=47,898 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2758kB
30. 38.039 38.039 ↑ 1.0 47,898 1

Seq Scan on future_products fp (cost=0.00..20,575.38 rows=49,138 width=13) (actual time=0.008..38.039 rows=47,898 loops=1)

31. 0.005 0.012 ↑ 1.0 19 1

Hash (cost=1.19..1.19 rows=19 width=32) (actual time=0.012..0.012 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
32. 0.007 0.007 ↑ 1.0 19 1

Seq Scan on future_products_underlying_info sfp (cost=0.00..1.19 rows=19 width=32) (actual time=0.004..0.007 rows=19 loops=1)

33. 0.480 2.912 ↓ 41.5 1,080 1

Hash (cost=13,182.84..13,182.84 rows=26 width=280) (actual time=2.912..2.912 rows=1,080 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 346kB
34. 0.207 2.432 ↓ 41.5 1,080 1

Subquery Scan on ranked_analysis (cost=12,990.24..13,182.84 rows=26 width=280) (actual time=1.468..2.432 rows=1,080 loops=1)

  • Filter: (ranked_analysis.rank = 1)
35. 0.708 2.225 ↑ 4.8 1,080 1

WindowAgg (cost=12,990.24..13,118.64 rows=5,136 width=304) (actual time=1.463..2.225 rows=1,080 loops=1)

36. 0.724 1.517 ↑ 4.8 1,080 1

Sort (cost=12,990.24..13,003.08 rows=5,136 width=280) (actual time=1.454..1.517 rows=1,080 loops=1)

  • Sort Key: a.unit_scenario_id, a.scalar_id, a.rds_id, a.evaluated_at DESC
  • Sort Method: quicksort Memory: 605kB
37. 0.150 0.793 ↑ 4.8 1,080 1

Result (cost=484.58..12,673.69 rows=5,136 width=280) (actual time=0.148..0.793 rows=1,080 loops=1)

38. 0.066 0.643 ↑ 4.8 1,080 1

Append (cost=484.58..12,622.33 rows=5,136 width=280) (actual time=0.145..0.643 rows=1,080 loops=1)

39. 0.448 0.577 ↑ 4.8 1,080 1

Bitmap Heap Scan on intraday_option_analysis_p2019_03_08 a (cost=484.58..12,622.33 rows=5,136 width=280) (actual time=0.144..0.577 rows=1,080 loops=1)

  • Recheck Cond: (((evaluated_at >= '2019-03-08 15:30:00-06'::timestamp with time zone) AND (evaluated_at < '2019-03-08 16:30:00-06'::timestamp with time zone)) OR ((evaluated_at >= '2019-03-08 09:45:00-06'::timestamp with time zone) AND (evaluated_at < '2019-03-08 10:00:00-06'::timestamp with time zone)) OR ((evaluated_at >= '2019-03-08 15:45:00-06'::timestamp with time zone) AND (evaluated_at < '2019-03-08 16:00:00-06'::timestamp with time zone) AND (unit_scenario_id = 2)))
  • Heap Blocks: exact=47
40. 0.002 0.129 ↓ 0.0 0 1

BitmapOr (cost=484.58..484.58 rows=5,149 width=0) (actual time=0.129..0.129 rows=0 loops=1)

41. 0.119 0.119 ↑ 2.4 1,080 1

Bitmap Index Scan on intraday_option_analysis_p2019_03_08_key (cost=0.00..158.10 rows=2,568 width=0) (actual time=0.119..0.119 rows=1,080 loops=1)

  • Index Cond: ((evaluated_at >= '2019-03-08 15:30:00-06'::timestamp with time zone) AND (evaluated_at < '2019-03-08 16:30:00-06'::timestamp with time zone))
42. 0.006 0.006 ↓ 0.0 0 1

Bitmap Index Scan on intraday_option_analysis_p2019_03_08_key (cost=0.00..158.10 rows=2,568 width=0) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: ((evaluated_at >= '2019-03-08 09:45:00-06'::timestamp with time zone) AND (evaluated_at < '2019-03-08 10:00:00-06'::timestamp with time zone))
43. 0.002 0.002 ↓ 0.0 0 1

Bitmap Index Scan on intraday_option_analysis_p2019_03_08_key (cost=0.00..164.52 rows=13 width=0) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: ((evaluated_at >= '2019-03-08 15:45:00-06'::timestamp with time zone) AND (evaluated_at < '2019-03-08 16:00:00-06'::timestamp with time zone) AND (unit_scenario_id = 2))
44. 0.023 0.327 ↓ 120.0 120 1

Hash (cost=1,201.94..1,201.94 rows=1 width=32) (actual time=0.327..0.327 rows=120 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
45. 0.017 0.304 ↓ 120.0 120 1

Subquery Scan on ranked_analysis_1 (cost=1,200.03..1,201.94 rows=1 width=32) (actual time=0.218..0.304 rows=120 loops=1)

  • Filter: (ranked_analysis_1.rank = 1)
46. 0.073 0.287 ↓ 2.4 120 1

WindowAgg (cost=1,200.03..1,201.31 rows=51 width=304) (actual time=0.216..0.287 rows=120 loops=1)

47. 0.058 0.214 ↓ 2.4 120 1

Sort (cost=1,200.03..1,200.16 rows=51 width=48) (actual time=0.208..0.214 rows=120 loops=1)

  • Sort Key: a_1.unit_scenario_id, a_1.scalar_id, a_1.rds_id, a_1.evaluated_at DESC
  • Sort Method: quicksort Memory: 34kB
48. 0.011 0.156 ↓ 2.4 120 1

Result (cost=1,000.01..1,198.58 rows=51 width=48) (actual time=0.096..0.156 rows=120 loops=1)

49. 0.010 0.145 ↓ 2.4 120 1

Append (cost=1,000.01..1,198.07 rows=51 width=48) (actual time=0.095..0.145 rows=120 loops=1)

50. 0.051 0.135 ↓ 2.4 120 1

Bitmap Heap Scan on intraday_option_analysis_p2019_03_08 a_1 (cost=1,000.01..1,198.07 rows=51 width=48) (actual time=0.094..0.135 rows=120 loops=1)

  • Recheck Cond: (((evaluated_at >= '2019-03-08 15:30:00-06'::timestamp with time zone) AND (evaluated_at < '2019-03-08 16:30:00-06'::timestamp with time zone) AND (scalar_id = ANY ('{1,8}'::bigint[]))) OR ((evaluated_at >= '2019-03-08 09:45:00-06'::timestamp with time zone) AND (evaluated_at < '2019-03-08 10:00:00-06'::timestamp with time zone) AND (scalar_id = ANY ('{1,8}'::bigint[]))) OR ((evaluated_at >= '2019-03-08 15:45:00-06'::timestamp with time zone) AND (evaluated_at < '2019-03-08 16:00:00-06'::timestamp with time zone) AND (scalar_id = ANY ('{1,8}'::bigint[])) AND (unit_scenario_id = 2)))
  • Filter: (scalar_id = ANY ('{1,8}'::bigint[]))
  • Heap Blocks: exact=44
51. 0.001 0.084 ↓ 0.0 0 1

BitmapOr (cost=1,000.01..1,000.01 rows=51 width=0) (actual time=0.084..0.084 rows=0 loops=1)

52. 0.075 0.075 ↓ 4.6 120 1

Bitmap Index Scan on intraday_option_analysis_p2019_03_08_key (cost=0.00..329.05 rows=26 width=0) (actual time=0.075..0.075 rows=120 loops=1)

  • Index Cond: ((evaluated_at >= '2019-03-08 15:30:00-06'::timestamp with time zone) AND (evaluated_at < '2019-03-08 16:30:00-06'::timestamp with time zone) AND (scalar_id = ANY ('{1,8}'::bigint[])))
53. 0.005 0.005 ↓ 0.0 0 1

Bitmap Index Scan on intraday_option_analysis_p2019_03_08_key (cost=0.00..329.05 rows=26 width=0) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: ((evaluated_at >= '2019-03-08 09:45:00-06'::timestamp with time zone) AND (evaluated_at < '2019-03-08 10:00:00-06'::timestamp with time zone) AND (scalar_id = ANY ('{1,8}'::bigint[])))
54. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on intraday_option_analysis_p2019_03_08_key (cost=0.00..341.88 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((evaluated_at >= '2019-03-08 15:45:00-06'::timestamp with time zone) AND (evaluated_at < '2019-03-08 16:00:00-06'::timestamp with time zone) AND (scalar_id = ANY ('{1,8}'::bigint[])) AND (unit_scenario_id = 2))
55. 2,153.681 45,491.118 ↑ 1.0 23,581,027 1

Materialize (cost=12,514,116.11..12,632,528.93 rows=23,682,564 width=256) (actual time=39,583.929..45,491.118 rows=23,581,027 loops=1)

56. 33,996.411 43,337.437 ↑ 1.0 23,581,027 1

Sort (cost=12,514,116.11..12,573,322.52 rows=23,682,564 width=256) (actual time=39,583.922..43,337.437 rows=23,581,027 loops=1)

  • Sort Key: fwd.id, fwd.evaluated_at
  • Sort Method: external merge Disk: 6162680kB
57. 1,360.495 9,341.026 ↑ 1.0 23,677,372 1

Append (cost=0.00..1,113,957.64 rows=23,682,564 width=256) (actual time=0.022..9,341.026 rows=23,677,372 loops=1)

58. 2,486.708 2,486.708 ↑ 1.0 7,691,764 1

Seq Scan on intraday_forward_buckets_p2019_03_05 fwd (cost=0.00..361,845.86 rows=7,692,786 width=256) (actual time=0.021..2,486.708 rows=7,691,764 loops=1)

59. 2,329.564 2,329.564 ↑ 1.0 6,411,761 1

Seq Scan on intraday_forward_buckets_p2019_03_06 fwd_1 (cost=0.00..301,627.54 rows=6,412,554 width=256) (actual time=0.015..2,329.564 rows=6,411,761 loops=1)

60. 884.348 884.348 ↑ 1.0 2,442,959 1

Seq Scan on intraday_forward_buckets_p2019_03_07 fwd_2 (cost=0.00..114,918.49 rows=2,443,149 width=256) (actual time=0.011..884.348 rows=2,442,959 loops=1)

61. 2,279.894 2,279.894 ↑ 1.0 7,130,888 1

Seq Scan on intraday_forward_buckets_p2019_03_08 fwd_3 (cost=0.00..335,514.95 rows=7,132,995 width=256) (actual time=0.016..2,279.894 rows=7,130,888 loops=1)

62. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on intraday_forward_buckets_p2019_03_09 fwd_4 (cost=0.00..12.70 rows=270 width=256) (actual time=0.008..0.008 rows=0 loops=1)

63. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on intraday_forward_buckets_p2019_03_10 fwd_5 (cost=0.00..12.70 rows=270 width=256) (actual time=0.003..0.003 rows=0 loops=1)

64. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on intraday_forward_buckets_p2019_03_11 fwd_6 (cost=0.00..12.70 rows=270 width=256) (actual time=0.003..0.003 rows=0 loops=1)

65. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on intraday_forward_buckets_p2019_03_12 fwd_7 (cost=0.00..12.70 rows=270 width=256) (actual time=0.003..0.003 rows=0 loops=1)

Planning time : 5.884 ms
Execution time : 59,721.719 ms