explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jo8

Settings
# exclusive inclusive rows x rows loops node
1. 2,441.433 49,351.412 ↑ 517,588.5 19,130 1

Merge Right Join (cost=130,297,439.97..2,655,353,093.31 rows=9,901,467,640 width=657) (actual time=49,300.563..49,351.412 rows=19,130 loops=1)

  • Merge Cond: ((fwd.id = ranked_analysis.forward_buckets_id) AND (fwd.evaluated_at = ranked_analysis.evaluated_at))
2. 15,501.579 23,306.780 ↑ 1.0 15,850,518 1

Sort (cost=8,338,413.25..8,378,080.57 rows=15,866,928 width=256) (actual time=20,961.572..23,306.780 rows=15,850,518 loops=1)

  • Sort Key: fwd.id, fwd.evaluated_at
  • Sort Method: external sort Disk: 4128464kB
3. 969.576 7,805.201 ↑ 1.0 15,861,965 1

Append (cost=0.00..746,333.28 rows=15,866,928 width=256) (actual time=0.015..7,805.201 rows=15,861,965 loops=1)

4. 1,165.148 1,165.148 ↑ 1.0 2,442,959 1

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

5. 2,509.074 2,509.074 ↑ 1.0 7,248,596 1

Seq Scan on intraday_forward_buckets_p2019_03_08 fwd_1 (cost=0.00..341,052.15 rows=7,250,715 width=256) (actual time=0.017..2,509.074 rows=7,248,596 loops=1)

6. 0.008 0.008 ↓ 0.0 0 1

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

7. 0.004 0.004 ↓ 0.0 0 1

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

8. 3,161.373 3,161.373 ↑ 1.0 6,170,410 1

Seq Scan on intraday_forward_buckets_p2019_03_11 fwd_4 (cost=0.00..290,286.44 rows=6,171,444 width=256) (actual time=0.031..3,161.373 rows=6,170,410 loops=1)

9. 0.007 0.007 ↓ 0.0 0 1

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

10. 0.003 0.003 ↓ 0.0 0 1

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

11. 0.003 0.003 ↓ 0.0 0 1

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

12. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on intraday_forward_buckets_p2019_03_15 fwd_8 (cost=0.00..12.70 rows=270 width=256) (actual time=0.005..0.005 rows=0 loops=1)

13. 1.971 23,603.199 ↑ 1,304.8 19,130 1

Materialize (cost=121,959,026.72..122,083,833.08 rows=24,961,272 width=441) (actual time=23,596.990..23,603.199 rows=19,130 loops=1)

14. 22.235 23,601.228 ↑ 1,304.8 19,130 1

Sort (cost=121,959,026.72..122,021,429.90 rows=24,961,272 width=441) (actual time=23,596.985..23,601.228 rows=19,130 loops=1)

  • Sort Key: ranked_analysis.forward_buckets_id, ranked_analysis.evaluated_at
  • Sort Method: external merge Disk: 4016kB
15. 5.920 23,578.993 ↑ 1,304.8 19,130 1

Hash Left Join (cost=96,217,651.77..103,791,047.06 rows=24,961,272 width=441) (actual time=22,556.996..23,578.993 rows=19,130 loops=1)

  • Hash Cond: ((p.rds_id = ranked_analysis_1.rds_id) AND (ranked_analysis.unit_scenario_id = ranked_analysis_1.unit_scenario_id))
16. 6.912 23,570.924 ↑ 1,304.8 19,130 1

Hash Left Join (cost=96,217,472.59..103,603,652.10 rows=24,961,272 width=433) (actual time=22,554.830..23,570.924 rows=19,130 loops=1)

  • Hash Cond: (p.rds_id = ranked_analysis.rds_id)
17. 170.494 23,544.551 ↑ 1,714.7 14,557 1

Hash Join (cost=96,215,567.27..103,503,149.76 rows=24,961,272 width=169) (actual time=22,535.354..23,544.551 rows=14,557 loops=1)

  • Hash Cond: (p.rds_id = o.rds_id)
18. 29.553 21,139.826 ↑ 92.5 269,706 1

Subquery Scan on p (cost=95,904,993.56..102,145,311.56 rows=24,961,272 width=84) (actual time=20,299.109..21,139.826 rows=269,706 loops=1)

19. 666.081 21,110.273 ↑ 92.5 269,706 1

GroupAggregate (cost=95,904,993.56..101,895,698.84 rows=24,961,272 width=140) (actual time=20,299.105..21,110.273 rows=269,706 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: 146648
20. 706.746 20,444.192 ↑ 435.3 573,490 1

Sort (cost=95,904,993.56..96,529,025.36 rows=249,612,720 width=84) (actual time=20,298.973..20,444.192 rows=573,490 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: 44720kB
21. 49.844 19,737.446 ↑ 435.3 573,490 1

Result (cost=0.00..13,312,678.40 rows=249,612,720 width=84) (actual time=2,852.013..19,737.446 rows=573,490 loops=1)

22. 32.960 19,687.602 ↑ 435.3 573,490 1

Append (cost=0.00..10,816,551.20 rows=249,612,720 width=84) (actual time=2,852.011..19,687.602 rows=573,490 loops=1)

23. 3,511.945 3,511.945 ↑ 449.5 100,655 1

Seq Scan on top_day_slasher_positions_positions_cm (cost=0.00..1,960,563.80 rows=45,243,780 width=84) (actual time=2,852.009..3,511.945 rows=100,655 loops=1)

24. 3,835.589 3,835.589 ↑ 428.0 114,572 1

Seq Scan on top_day_slasher_positions_positions_etfs (cost=0.00..2,124,885.10 rows=49,035,810 width=84) (actual time=3,808.110..3,835.589 rows=114,572 loops=1)

25. 4,991.080 4,991.080 ↑ 439.6 140,149 1

Seq Scan on top_day_slasher_positions_positions_fxoptions (cost=0.00..2,669,832.10 rows=61,611,510 width=84) (actual time=3,125.942..4,991.080 rows=140,149 loops=1)

26. 2,792.781 2,792.781 ↑ 427.0 82,799 1

Seq Scan on top_day_slasher_positions_positions_other (cost=0.00..1,531,931.70 rows=35,352,270 width=84) (actual time=1,221.265..2,792.781 rows=82,799 loops=1)

27. 4,523.247 4,523.247 ↑ 431.4 135,315 1

Seq Scan on top_day_slasher_positions_positions_sg (cost=0.00..2,529,338.50 rows=58,369,350 width=84) (actual time=4,056.154..4,523.247 rows=135,315 loops=1)

28. 174.918 2,234.231 ↑ 2.3 588,036 1

Hash (cost=271,853.92..271,853.92 rows=1,376,703 width=101) (actual time=2,234.231..2,234.231 rows=588,036 loops=1)

  • Buckets: 32768 Batches: 64 Memory Usage: 1260kB
29. 460.786 2,059.313 ↑ 2.3 588,036 1

Hash Join (cost=148,892.22..271,853.92 rows=1,376,703 width=101) (actual time=1,124.167..2,059.313 rows=588,036 loops=1)

  • Hash Cond: (o.option_listing_id = ol.id)
30. 474.524 474.524 ↓ 1.1 1,559,343 1

Seq Scan on options o (cost=0.00..75,818.03 rows=1,376,703 width=49) (actual time=0.008..474.524 rows=1,559,343 loops=1)

31. 8.979 1,124.003 ↑ 3.5 32,598 1

Hash (cost=146,155.96..146,155.96 rows=112,981 width=68) (actual time=1,124.003..1,124.003 rows=32,598 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 1209kB
32. 4.529 1,115.024 ↑ 3.5 32,598 1

Hash Left Join (cost=130,867.59..146,155.96 rows=112,981 width=68) (actual time=937.082..1,115.024 rows=32,598 loops=1)

  • Hash Cond: (f.future_product_id = sfp.future_product_id)
33. 6.528 1,110.480 ↑ 3.5 32,598 1

Hash Join (cost=130,866.16..145,857.90 rows=112,981 width=52) (actual time=937.048..1,110.480 rows=32,598 loops=1)

  • Hash Cond: (f.future_product_id = fp.id)
34. 124.646 1,021.587 ↑ 3.5 32,598 1

Hash Join (cost=109,676.56..124,371.70 rows=112,981 width=47) (actual time=854.621..1,021.587 rows=32,598 loops=1)

  • Hash Cond: (ol.underlying_rds_id = f.rds_id)
35. 32.914 53.032 ↑ 1.1 102,444 1

Hash Join (cost=3,784.42..7,944.98 rows=112,981 width=44) (actual time=9.731..53.032 rows=102,444 loops=1)

  • Hash Cond: (ol.option_product_id = op.id)
36. 10.423 10.423 ↑ 1.1 102,444 1

Seq Scan on option_listings ol (cost=0.00..3,863.81 rows=112,981 width=44) (actual time=0.011..10.423 rows=102,444 loops=1)

37. 1.137 9.695 ↑ 1.1 6,829 1

Hash (cost=3,693.63..3,693.63 rows=7,263 width=16) (actual time=9.695..9.695 rows=6,829 loops=1)

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

Seq Scan on option_products op (cost=0.00..3,693.63 rows=7,263 width=16) (actual time=0.009..8.558 rows=6,829 loops=1)

39. 240.160 843.909 ↓ 1.1 1,117,298 1

Hash (cost=84,437.84..84,437.84 rows=1,056,184 width=35) (actual time=843.909..843.909 rows=1,117,298 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 2946kB
40. 603.749 603.749 ↓ 1.1 1,117,298 1

Seq Scan on futures f (cost=0.00..84,437.84 rows=1,056,184 width=35) (actual time=0.013..603.749 rows=1,117,298 loops=1)

41. 9.391 82.365 ↑ 1.0 47,898 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2758kB
42. 72.974 72.974 ↑ 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.010..72.974 rows=47,898 loops=1)

43. 0.005 0.015 ↑ 1.0 19 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
44. 0.010 0.010 ↑ 1.0 19 1

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

45. 3.115 19.461 ↓ 1,593.0 6,372 1

Hash (cost=1,905.27..1,905.27 rows=4 width=280) (actual time=19.461..19.461 rows=6,372 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2006kB
46. 1.092 16.346 ↓ 1,593.0 6,372 1

Subquery Scan on ranked_analysis (cost=1,877.33..1,905.27 rows=4 width=280) (actual time=10.734..16.346 rows=6,372 loops=1)

  • Filter: (ranked_analysis.rank = 1)
47. 4.142 15.254 ↓ 8.6 6,372 1

WindowAgg (cost=1,877.33..1,895.96 rows=745 width=304) (actual time=10.730..15.254 rows=6,372 loops=1)

48. 5.823 11.112 ↓ 8.6 6,372 1

Sort (cost=1,877.33..1,879.20 rows=745 width=280) (actual time=10.719..11.112 rows=6,372 loops=1)

  • Sort Key: a.unit_scenario_id, a.scalar_id, a.rds_id, a.evaluated_at DESC
  • Sort Method: quicksort Memory: 3464kB
49. 0.885 5.289 ↓ 8.6 6,372 1

Result (cost=73.53..1,841.79 rows=745 width=280) (actual time=1.013..5.289 rows=6,372 loops=1)

50. 0.370 4.404 ↓ 8.6 6,372 1

Append (cost=73.53..1,834.34 rows=745 width=280) (actual time=1.010..4.404 rows=6,372 loops=1)

51. 3.071 4.034 ↓ 8.6 6,372 1

Bitmap Heap Scan on intraday_option_analysis_p2019_03_11 a (cost=73.53..1,834.34 rows=745 width=280) (actual time=1.009..4.034 rows=6,372 loops=1)

  • Recheck Cond: (((evaluated_at >= '2019-03-11 12:30:00-05'::timestamp with time zone) AND (evaluated_at < '2019-03-11 13:30:00-05'::timestamp with time zone)) OR ((evaluated_at >= '2019-03-11 10:45:00-05'::timestamp with time zone) AND (evaluated_at < '2019-03-11 11:00:00-05'::timestamp with time zone)) OR ((evaluated_at >= '2019-03-11 15:45:00-05'::timestamp with time zone) AND (evaluated_at < '2019-03-11 16:00:00-05'::timestamp with time zone) AND (unit_scenario_id = 2)))
  • Heap Blocks: exact=274
52. 0.001 0.963 ↓ 0.0 0 1

BitmapOr (cost=73.53..73.53 rows=747 width=0) (actual time=0.963..0.963 rows=0 loops=1)

53. 0.950 0.950 ↓ 23.8 8,856 1

Bitmap Index Scan on intraday_option_analysis_p2019_03_11_key (cost=0.00..24.01 rows=372 width=0) (actual time=0.950..0.950 rows=8,856 loops=1)

  • Index Cond: ((evaluated_at >= '2019-03-11 12:30:00-05'::timestamp with time zone) AND (evaluated_at < '2019-03-11 13:30:00-05'::timestamp with time zone))
54. 0.007 0.007 ↓ 0.0 0 1

Bitmap Index Scan on intraday_option_analysis_p2019_03_11_key (cost=0.00..24.01 rows=372 width=0) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: ((evaluated_at >= '2019-03-11 10:45:00-05'::timestamp with time zone) AND (evaluated_at < '2019-03-11 11:00:00-05'::timestamp with time zone))
55. 0.005 0.005 ↓ 0.0 0 1

Bitmap Index Scan on intraday_option_analysis_p2019_03_11_key (cost=0.00..24.94 rows=2 width=0) (actual time=0.004..0.005 rows=0 loops=1)

  • Index Cond: ((evaluated_at >= '2019-03-11 15:45:00-05'::timestamp with time zone) AND (evaluated_at < '2019-03-11 16:00:00-05'::timestamp with time zone) AND (unit_scenario_id = 2))
56. 0.127 2.149 ↓ 708.0 708 1

Hash (cost=179.16..179.16 rows=1 width=32) (actual time=2.149..2.149 rows=708 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 53kB
57. 0.077 2.022 ↓ 708.0 708 1

Subquery Scan on ranked_analysis_1 (cost=178.90..179.16 rows=1 width=32) (actual time=1.538..2.022 rows=708 loops=1)

  • Filter: (ranked_analysis_1.rank = 1)
58. 0.391 1.945 ↓ 101.1 708 1

WindowAgg (cost=178.90..179.08 rows=7 width=304) (actual time=1.535..1.945 rows=708 loops=1)

59. 0.348 1.554 ↓ 101.1 708 1

Sort (cost=178.90..178.92 rows=7 width=48) (actual time=1.523..1.554 rows=708 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: 80kB
60. 0.057 1.206 ↓ 101.1 708 1

Result (cost=151.52..178.80 rows=7 width=48) (actual time=0.817..1.206 rows=708 loops=1)

61. 0.040 1.149 ↓ 101.1 708 1

Append (cost=151.52..178.73 rows=7 width=48) (actual time=0.815..1.149 rows=708 loops=1)

62. 0.332 1.109 ↓ 101.1 708 1

Bitmap Heap Scan on intraday_option_analysis_p2019_03_11 a_1 (cost=151.52..178.73 rows=7 width=48) (actual time=0.815..1.109 rows=708 loops=1)

  • Recheck Cond: (((evaluated_at >= '2019-03-11 12:30:00-05'::timestamp with time zone) AND (evaluated_at < '2019-03-11 13:30:00-05'::timestamp with time zone) AND (scalar_id = ANY ('{1,8}'::bigint[]))) OR ((evaluated_at >= '2019-03-11 10:45:00-05'::timestamp with time zone) AND (evaluated_at < '2019-03-11 11:00:00-05'::timestamp with time zone) AND (scalar_id = ANY ('{1,8}'::bigint[]))) OR ((evaluated_at >= '2019-03-11 15:45:00-05'::timestamp with time zone) AND (evaluated_at < '2019-03-11 16:00:00-05'::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=253
63. 0.002 0.777 ↓ 0.0 0 1

BitmapOr (cost=151.52..151.52 rows=7 width=0) (actual time=0.777..0.777 rows=0 loops=1)

64. 0.765 0.765 ↓ 246.0 984 1

Bitmap Index Scan on intraday_option_analysis_p2019_03_11_key (cost=0.00..49.88 rows=4 width=0) (actual time=0.765..0.765 rows=984 loops=1)

  • Index Cond: ((evaluated_at >= '2019-03-11 12:30:00-05'::timestamp with time zone) AND (evaluated_at < '2019-03-11 13:30:00-05'::timestamp with time zone) AND (scalar_id = ANY ('{1,8}'::bigint[])))
65. 0.006 0.006 ↓ 0.0 0 1

Bitmap Index Scan on intraday_option_analysis_p2019_03_11_key (cost=0.00..49.88 rows=4 width=0) (actual time=0.006..0.006 rows=0 loops=1)

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

Bitmap Index Scan on intraday_option_analysis_p2019_03_11_key (cost=0.00..51.74 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: ((evaluated_at >= '2019-03-11 15:45:00-05'::timestamp with time zone) AND (evaluated_at < '2019-03-11 16:00:00-05'::timestamp with time zone) AND (scalar_id = ANY ('{1,8}'::bigint[])) AND (unit_scenario_id = 2))