explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hSkS

Settings
# exclusive inclusive rows x rows loops node
1. 9.171 36,437.720 ↑ 799,852.6 14,551 1

Merge Right Join (cost=126,782,077.21..1,349,042,370.70 rows=11,638,655,620 width=409) (actual time=36,425.361..36,437.720 rows=14,551 loops=1)

  • Merge Cond: ((fwd.id = ranked_analysis.forward_buckets_id) AND (fwd.evaluated_at = ranked_analysis.evaluated_at))
2. 8,724.788 13,199.204 ↑ 18,449,080.0 1 1

Sort (cost=3,725,046.22..3,771,168.92 rows=18,449,080 width=16) (actual time=13,199.204..13,199.204 rows=1 loops=1)

  • Sort Key: fwd.id, fwd.evaluated_at
  • Sort Method: external sort Disk: 469312kB
3. 1,051.584 4,474.416 ↑ 1.0 18,447,410 1

Append (cost=0.00..867,926.80 rows=18,449,080 width=16) (actual time=0.008..4,474.416 rows=18,447,410 loops=1)

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

Seq Scan on intraday_forward_buckets_p2019_03_07 fwd (cost=0.00..114,916.59 rows=2,442,959 width=16) (actual time=0.008..456.785 rows=2,442,959 loops=1)

5. 1,353.700 1,353.700 ↑ 1.0 7,248,596 1

Seq Scan on intraday_forward_buckets_p2019_03_08 fwd_1 (cost=0.00..341,030.96 rows=7,248,596 width=16) (actual time=0.033..1,353.700 rows=7,248,596 loops=1)

6. 0.007 0.007 ↓ 0.0 0 1

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

7. 0.002 0.002 ↓ 0.0 0 1

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

8. 1,612.325 1,612.325 ↑ 1.0 8,755,855 1

Seq Scan on intraday_forward_buckets_p2019_03_11 fwd_4 (cost=0.00..411,903.05 rows=8,755,905 width=16) (actual time=0.030..1,612.325 rows=8,755,855 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=16) (actual time=0.007..0.007 rows=0 loops=1)

10. 0.002 0.002 ↓ 0.0 0 1

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

11. 0.002 0.002 ↓ 0.0 0 1

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

12. 0.002 0.002 ↓ 0.0 0 1

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

13. 1.578 23,229.345 ↑ 1,734.2 14,551 1

Materialize (cost=123,057,030.99..123,183,201.57 rows=25,234,116 width=433) (actual time=23,226.135..23,229.345 rows=14,551 loops=1)

14. 10.374 23,227.767 ↑ 1,734.2 14,551 1

Sort (cost=123,057,030.99..123,120,116.28 rows=25,234,116 width=433) (actual time=23,226.130..23,227.767 rows=14,551 loops=1)

  • Sort Key: ranked_analysis.forward_buckets_id, ranked_analysis.evaluated_at
  • Sort Method: external sort Disk: 2064kB
15. 3.854 23,217.393 ↑ 1,734.2 14,551 1

Hash Left Join (cost=97,287,503.07..104,947,230.22 rows=25,234,116 width=433) (actual time=22,172.649..23,217.393 rows=14,551 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. 4.031 23,213.518 ↑ 1,734.2 14,551 1

Hash Left Join (cost=97,287,174.45..104,757,639.42 rows=25,234,116 width=425) (actual time=22,172.612..23,213.518 rows=14,551 loops=1)

  • Hash Cond: (p.rds_id = ranked_analysis.rds_id)
17. 173.819 23,209.445 ↑ 1,734.2 14,551 1

Hash Join (cost=97,283,665.53..104,650,670.63 rows=25,234,116 width=169) (actual time=22,172.555..23,209.445 rows=14,551 loops=1)

  • Hash Cond: (p.rds_id = o.rds_id)
18. 28.450 21,207.506 ↑ 92.9 271,663 1

Subquery Scan on p (cost=96,973,091.83..103,281,620.83 rows=25,234,116 width=84) (actual time=20,342.652..21,207.506 rows=271,663 loops=1)

19. 683.977 21,179.056 ↑ 92.9 271,663 1

GroupAggregate (cost=96,973,091.83..103,029,279.67 rows=25,234,116 width=140) (actual time=20,342.650..21,179.056 rows=271,663 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: 146535
20. 704.809 20,495.079 ↑ 438.7 575,202 1

Sort (cost=96,973,091.83..97,603,944.73 rows=252,341,160 width=84) (actual time=20,342.600..20,495.079 rows=575,202 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: 44872kB
21. 47.252 19,790.270 ↑ 438.7 575,202 1

Result (cost=0.00..13,458,195.20 rows=252,341,160 width=84) (actual time=2,866.277..19,790.270 rows=575,202 loops=1)

22. 33.037 19,743.018 ↑ 438.7 575,202 1

Append (cost=0.00..10,934,783.60 rows=252,341,160 width=84) (actual time=2,866.275..19,743.018 rows=575,202 loops=1)

23. 3,521.675 3,521.675 ↑ 454.2 100,696 1

Seq Scan on top_day_slasher_positions_positions_cm (cost=0.00..1,981,808.40 rows=45,734,040 width=84) (actual time=2,866.272..3,521.675 rows=100,696 loops=1)

24. 3,844.025 3,844.025 ↑ 431.5 114,689 1

Seq Scan on top_day_slasher_positions_positions_etfs (cost=0.00..2,144,357.80 rows=49,485,180 width=84) (actual time=3,817.070..3,844.025 rows=114,689 loops=1)

25. 4,867.920 4,867.920 ↑ 438.9 141,956 1

Seq Scan on top_day_slasher_positions_positions_fxoptions (cost=0.00..2,699,726.90 rows=62,301,390 width=84) (actual time=3,051.748..4,867.920 rows=141,956 loops=1)

26. 2,773.918 2,773.918 ↑ 432.8 82,516 1

Seq Scan on top_day_slasher_positions_positions_other (cost=0.00..1,547,525.20 rows=35,712,120 width=84) (actual time=1,217.983..2,773.918 rows=82,516 loops=1)

27. 4,702.443 4,702.443 ↑ 436.7 135,345 1

Seq Scan on top_day_slasher_positions_positions_sg (cost=0.00..2,561,365.30 rows=59,108,430 width=84) (actual time=4,231.586..4,702.443 rows=135,345 loops=1)

28. 174.564 1,828.120 ↑ 2.3 588,036 1

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

  • Buckets: 32768 Batches: 64 Memory Usage: 1260kB
29. 462.058 1,653.556 ↑ 2.3 588,036 1

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

  • Hash Cond: (o.option_listing_id = ol.id)
30. 392.164 392.164 ↓ 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.009..392.164 rows=1,559,343 loops=1)

31. 8.820 799.334 ↑ 3.5 32,598 1

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

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

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

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

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

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

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

  • Hash Cond: (ol.underlying_rds_id = f.rds_id)
35. 31.191 46.085 ↑ 1.1 102,445 1

Hash Join (cost=3,784.42..7,944.98 rows=112,981 width=44) (actual time=6.078..46.085 rows=102,445 loops=1)

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

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

37. 1.143 6.047 ↑ 1.1 6,829 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 414kB
38. 4.904 4.904 ↑ 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.007..4.904 rows=6,829 loops=1)

39. 224.397 570.501 ↓ 1.1 1,117,298 1

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

  • Buckets: 65536 Batches: 32 Memory Usage: 2946kB
40. 346.104 346.104 ↓ 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.007..346.104 rows=1,117,298 loops=1)

41. 7.301 42.358 ↑ 1.0 47,898 1

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

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

43. 0.014 0.025 ↑ 1.0 19 1

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

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

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

45. 0.001 0.042 ↓ 0.0 0 1

Hash (cost=3,508.84..3,508.84 rows=7 width=272) (actual time=0.042..0.042 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
46. 0.003 0.041 ↓ 0.0 0 1

Subquery Scan on ranked_analysis (cost=3,457.42..3,508.84 rows=7 width=272) (actual time=0.041..0.041 rows=0 loops=1)

  • Filter: (ranked_analysis.rank = 1)
47. 0.003 0.038 ↓ 0.0 0 1

WindowAgg (cost=3,457.42..3,491.70 rows=1,371 width=304) (actual time=0.038..0.038 rows=0 loops=1)

48. 0.007 0.035 ↓ 0.0 0 1

Sort (cost=3,457.42..3,460.85 rows=1,371 width=272) (actual time=0.035..0.035 rows=0 loops=1)

  • Sort Key: a.unit_scenario_id, a.scalar_id, a.rds_id, a.evaluated_at DESC
  • Sort Method: quicksort Memory: 25kB
49. 0.001 0.028 ↓ 0.0 0 1

Result (cost=132.55..3,385.99 rows=1,371 width=272) (actual time=0.028..0.028 rows=0 loops=1)

50. 0.001 0.027 ↓ 0.0 0 1

Append (cost=132.55..3,372.28 rows=1,371 width=272) (actual time=0.027..0.027 rows=0 loops=1)

51. 0.003 0.026 ↓ 0.0 0 1

Bitmap Heap Scan on intraday_option_analysis_p2019_03_11 a (cost=132.55..3,372.28 rows=1,371 width=272) (actual time=0.026..0.026 rows=0 loops=1)

  • Recheck Cond: (((evaluated_at >= '2019-03-11 13:45:00-05'::timestamp with time zone) AND (evaluated_at < '2019-03-11 14:45: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)))
52. 0.002 0.023 ↓ 0.0 0 1

BitmapOr (cost=132.55..132.55 rows=1,374 width=0) (actual time=0.023..0.023 rows=0 loops=1)

53. 0.013 0.013 ↓ 0.0 0 1

Bitmap Index Scan on intraday_option_analysis_p2019_03_11_key (cost=0.00..43.27 rows=685 width=0) (actual time=0.013..0.013 rows=0 loops=1)

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

Bitmap Index Scan on intraday_option_analysis_p2019_03_11_key (cost=0.00..43.27 rows=685 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))
55. 0.002 0.002 ↓ 0.0 0 1

Bitmap Index Scan on intraday_option_analysis_p2019_03_11_key (cost=0.00..44.98 rows=3 width=0) (actual time=0.002..0.002 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.000 0.021 ↓ 0.0 0 1

Hash (cost=328.60..328.60 rows=1 width=32) (actual time=0.021..0.021 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
57. 0.001 0.021 ↓ 0.0 0 1

Subquery Scan on ranked_analysis_1 (cost=328.08..328.60 rows=1 width=32) (actual time=0.020..0.021 rows=0 loops=1)

  • Filter: (ranked_analysis_1.rank = 1)
58. 0.002 0.020 ↓ 0.0 0 1

WindowAgg (cost=328.08..328.43 rows=14 width=304) (actual time=0.020..0.020 rows=0 loops=1)

59. 0.003 0.018 ↓ 0.0 0 1

Sort (cost=328.08..328.11 rows=14 width=48) (actual time=0.018..0.018 rows=0 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: 25kB
60. 0.001 0.015 ↓ 0.0 0 1

Result (cost=273.33..327.81 rows=14 width=48) (actual time=0.015..0.015 rows=0 loops=1)

61. 0.000 0.014 ↓ 0.0 0 1

Append (cost=273.33..327.67 rows=14 width=48) (actual time=0.014..0.014 rows=0 loops=1)

62. 0.002 0.014 ↓ 0.0 0 1

Bitmap Heap Scan on intraday_option_analysis_p2019_03_11 a_1 (cost=273.33..327.67 rows=14 width=48) (actual time=0.013..0.014 rows=0 loops=1)

  • Recheck Cond: (((evaluated_at >= '2019-03-11 13:45:00-05'::timestamp with time zone) AND (evaluated_at < '2019-03-11 14:45: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[]))
63. 0.000 0.012 ↓ 0.0 0 1

BitmapOr (cost=273.33..273.33 rows=14 width=0) (actual time=0.012..0.012 rows=0 loops=1)

64. 0.005 0.005 ↓ 0.0 0 1

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

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

Bitmap Index Scan on intraday_option_analysis_p2019_03_11_key (cost=0.00..89.97 rows=7 width=0) (actual time=0.004..0.004 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.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on intraday_option_analysis_p2019_03_11_key (cost=0.00..93.39 rows=1 width=0) (actual time=0.003..0.003 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))