explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cTOI

Settings
# exclusive inclusive rows x rows loops node
1. 0.309 799.992 ↑ 505,654.2 4 1

Hash Left Join (cost=9,217.83..448,391.44 rows=2,022,617 width=550) (actual time=799.756..799.992 rows=4 loops=1)

  • Hash Cond: (stockinfo_1.insid = s.insid)
  • Functions: 139
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 44.684 ms, Inlining 0.000 ms, Optimization 15.636 ms, Emission 741.386 ms, Total 801.706 ms
2. 0.074 799.085 ↑ 505,654.2 4 1

Merge Left Join (cost=9,127.94..43,478.84 rows=2,022,617 width=342) (actual time=798.905..799.085 rows=4 loops=1)

  • Merge Cond: (p.insid = w.insid)
3. 0.009 759.272 ↑ 552.2 4 1

Nested Loop Left Join (cost=0.83..94.37 rows=2,209 width=118) (actual time=759.213..759.272 rows=4 loops=1)

4. 0.013 759.211 ↑ 1.0 4 1

Nested Loop Left Join (cost=0.55..47.35 rows=4 width=82) (actual time=759.176..759.211 rows=4 loops=1)

5. 0.011 759.142 ↑ 1.0 4 1

Nested Loop (cost=0.14..13.60 rows=4 width=60) (actual time=759.123..759.142 rows=4 loops=1)

6. 759.063 759.063 ↑ 1.0 4 1

Index Scan using trade_positions_insid on trades_positions p (cost=0.14..12.45 rows=4 width=44) (actual time=759.053..759.063 rows=4 loops=1)

  • Filter: (trade_id = 9)
  • Rows Removed by Filter: 14
7. 0.040 0.068 ↑ 1.0 1 4

Materialize (cost=0.00..1.10 rows=1 width=20) (actual time=0.016..0.017 rows=1 loops=4)

8. 0.028 0.028 ↑ 1.0 1 1

Seq Scan on trades t (cost=0.00..1.10 rows=1 width=20) (actual time=0.027..0.028 rows=1 loops=1)

  • Filter: (trade_id = 9)
  • Rows Removed by Filter: 7
9. 0.056 0.056 ↑ 1.0 1 4

Index Scan using options_insid on options o (cost=0.42..8.44 rows=1 width=26) (actual time=0.014..0.014 rows=1 loops=4)

  • Index Cond: (insid = p.insid)
10. 0.012 0.052 ↑ 2.0 1 4

Append (cost=0.27..11.73 rows=2 width=40) (actual time=0.012..0.013 rows=1 loops=4)

11. 0.020 0.020 ↓ 0.0 0 4

Index Only Scan using stockinfo_insid on stockinfo stockinfo_1 (cost=0.27..3.29 rows=1 width=40) (actual time=0.005..0.005 rows=0 loops=4)

  • Index Cond: (insid = p.insid)
  • Heap Fetches: 0
12. 0.020 0.020 ↑ 1.0 1 4

Index Scan using options_insid on options options_1 (cost=0.42..8.44 rows=1 width=40) (actual time=0.004..0.005 rows=1 loops=4)

  • Index Cond: (insid = p.insid)
13. 0.116 39.739 ↑ 1,308.2 140 1

Materialize (cost=9,127.11..13,494.92 rows=183,141 width=228) (actual time=30.884..39.739 rows=140 loops=1)

14. 0.083 39.623 ↑ 1,308.2 140 1

Nested Loop Left Join (cost=9,127.11..13,037.07 rows=183,141 width=228) (actual time=30.880..39.623 rows=140 loops=1)

15. 0.157 31.420 ↑ 2.4 140 1

Merge Join (cost=9,126.84..9,148.05 rows=331 width=101) (actual time=30.845..31.420 rows=140 loops=1)

  • Merge Cond: (w.insid = q.insid)
16. 0.175 26.027 ↑ 2.4 140 1

Sort (cost=8,473.27..8,474.10 rows=333 width=77) (actual time=26.002..26.027 rows=140 loops=1)

  • Sort Key: w.insid
  • Sort Method: quicksort Memory: 62kB
17. 0.391 25.852 ↑ 1.0 321 1

Hash Right Join (cost=8,263.39..8,459.32 rows=333 width=77) (actual time=25.436..25.852 rows=321 loops=1)

  • Hash Cond: (g.insid = w.insid)
18. 0.692 0.692 ↑ 1.0 1,853 1

Seq Scan on greeks g (cost=0.00..111.53 rows=1,853 width=77) (actual time=0.014..0.692 rows=1,853 loops=1)

19. 0.069 24.769 ↑ 1.0 321 1

Hash (cost=8,259.25..8,259.25 rows=331 width=4) (actual time=24.769..24.769 rows=321 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
20. 0.043 24.700 ↑ 1.0 321 1

Subquery Scan on w (cost=8,252.63..8,259.25 rows=331 width=4) (actual time=24.606..24.700 rows=321 loops=1)

21. 0.248 24.657 ↑ 1.0 321 1

HashAggregate (cost=8,252.63..8,255.94 rows=331 width=36) (actual time=24.604..24.657 rows=321 loops=1)

  • Group Key: options_2.insid, options_2.binckid
22.          

CTE core

23. 0.047 17.122 ↓ 3.0 358 1

Append (cost=0.00..2,430.95 rows=121 width=4) (actual time=0.010..17.122 rows=358 loops=1)

24. 0.014 0.014 ↑ 1.0 18 1

Seq Scan on trades_positions (cost=0.00..1.18 rows=18 width=4) (actual time=0.009..0.014 rows=18 loops=1)

  • Filter: (closed IS NULL)
25. 0.023 0.023 ↑ 1.0 94 1

Seq Scan on stocksel (cost=0.00..1.94 rows=94 width=4) (actual time=0.006..0.023 rows=94 loops=1)

26. 17.038 17.038 ↓ 27.3 246 1

Seq Scan on watchlist_temps (cost=0.00..2,426.95 rows=9 width=4) (actual time=16.993..17.038 rows=246 loops=1)

  • Filter: (expiry > now())
  • Rows Removed by Filter: 118,649
27.          

CTE insids

28. 0.853 21.794 ↑ 90.7 607 1

Append (cost=0.00..1,807.05 rows=55,063 width=4) (actual time=0.012..21.794 rows=607 loops=1)

29. 18.467 18.467 ↓ 3.0 358 1

CTE Scan on core (cost=0.00..2.42 rows=121 width=4) (actual time=0.011..18.467 rows=358 loops=1)

30. 0.040 2.474 ↑ 220.7 249 1

Nested Loop (cost=3.00..979.89 rows=54,942 width=4) (actual time=0.294..2.474 rows=249 loops=1)

31. 0.308 0.358 ↓ 2.9 346 1

HashAggregate (cost=2.72..3.93 rows=121 width=4) (actual time=0.249..0.358 rows=346 loops=1)

  • Group Key: core_1.insid
32. 0.050 0.050 ↓ 3.0 358 1

CTE Scan on core core_1 (cost=0.00..2.42 rows=121 width=4) (actual time=0.001..0.050 rows=358 loops=1)

33. 0.346 2.076 ↑ 2.0 1 346

Append (cost=0.27..8.05 rows=2 width=8) (actual time=0.005..0.006 rows=1 loops=346)

34. 0.692 0.692 ↓ 0.0 0 346

Index Only Scan using stockinfo_insid on stockinfo stockinfo_3 (cost=0.27..0.42 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=346)

  • Index Cond: (insid = core_1.insid)
  • Filter: (insid <> insid)
  • Rows Removed by Filter: 0
  • Heap Fetches: 0
35. 1.038 1.038 ↑ 1.0 1 346

Index Scan using options_insid on options options_3 (cost=0.42..7.61 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=346)

  • Index Cond: (insid = core_1.insid)
  • Filter: (insid <> symbol_id)
36. 0.035 24.409 ↑ 1.0 321 1

Append (cost=1,239.34..4,012.98 rows=331 width=36) (actual time=22.318..24.409 rows=321 loops=1)

37. 0.304 23.395 ↓ 1.2 249 1

Nested Loop (cost=1,239.34..2,677.92 rows=200 width=11) (actual time=22.317..23.395 rows=249 loops=1)

38. 0.369 22.399 ↓ 1.7 346 1

HashAggregate (cost=1,238.92..1,240.92 rows=200 width=4) (actual time=22.305..22.399 rows=346 loops=1)

  • Group Key: insids.insid
39. 22.030 22.030 ↑ 90.7 607 1

CTE Scan on insids (cost=0.00..1,101.26 rows=55,063 width=4) (actual time=0.014..22.030 rows=607 loops=1)

40. 0.692 0.692 ↑ 1.0 1 346

Index Scan using options_insid on options options_2 (cost=0.42..7.18 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=346)

  • Index Cond: (insid = insids.insid)
41. 0.101 0.979 ↑ 1.8 72 1

Hash Join (cost=1,245.42..1,330.10 rows=131 width=10) (actual time=0.436..0.979 rows=72 loops=1)

  • Hash Cond: (stockinfo_2.insid = insids_1.insid)
42. 0.483 0.483 ↑ 1.0 317 1

Seq Scan on stockinfo stockinfo_2 (cost=0.00..83.84 rows=317 width=10) (actual time=0.020..0.483 rows=317 loops=1)

  • Filter: (binckid IS NOT NULL)
  • Rows Removed by Filter: 167
43. 0.049 0.395 ↓ 1.7 346 1

Hash (cost=1,242.92..1,242.92 rows=200 width=4) (actual time=0.395..0.395 rows=346 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
44. 0.276 0.346 ↓ 1.7 346 1

HashAggregate (cost=1,238.92..1,240.92 rows=200 width=4) (actual time=0.292..0.346 rows=346 loops=1)

  • Group Key: insids_1.insid
45. 0.070 0.070 ↑ 90.7 607 1

CTE Scan on insids insids_1 (cost=0.00..1,101.26 rows=55,063 width=4) (actual time=0.001..0.070 rows=607 loops=1)

46. 0.133 5.236 ↓ 4.2 830 1

Subquery Scan on q (cost=653.57..669.31 rows=199 width=28) (actual time=4.830..5.236 rows=830 loops=1)

  • Filter: (q.value IS NOT NULL)
47. 0.207 5.103 ↓ 4.2 830 1

Unique (cost=653.57..667.31 rows=200 width=68) (actual time=4.822..5.103 rows=830 loops=1)

48. 1.198 4.896 ↑ 2.8 975 1

Sort (cost=653.57..660.44 rows=2,747 width=68) (actual time=4.815..4.896 rows=975 loops=1)

  • Sort Key: "*SELECT* 1".insid, "*SELECT* 1"."timestamp" DESC
  • Sort Method: quicksort Memory: 290kB
49. 0.399 3.698 ↑ 1.1 2,472 1

Result (cost=216.06..496.67 rows=2,747 width=68) (actual time=1.762..3.698 rows=2,472 loops=1)

50. 0.233 3.299 ↑ 1.1 2,472 1

Append (cost=216.06..469.20 rows=2,747 width=28) (actual time=1.760..3.299 rows=2,472 loops=1)

51. 0.041 2.078 ↑ 1.8 346 1

Subquery Scan on *SELECT* 1 (cost=216.06..230.03 rows=621 width=44) (actual time=1.759..2.078 rows=346 loops=1)

52. 0.710 2.037 ↑ 1.8 346 1

HashAggregate (cost=216.06..223.82 rows=621 width=84) (actual time=1.757..2.037 rows=346 loops=1)

  • Group Key: quotes.insid
53. 1.327 1.327 ↓ 1.1 688 1

Seq Scan on quotes (cost=0.00..211.21 rows=646 width=23) (actual time=0.025..1.327 rows=688 loops=1)

  • Filter: (((type)::text = 'bid'::text) OR ((type)::text = 'ask'::text))
  • Rows Removed by Filter: 2,126
54. 0.263 0.988 ↑ 1.0 2,126 1

Subquery Scan on *SELECT* 2 (cost=0.00..225.44 rows=2,126 width=23) (actual time=0.018..0.988 rows=2,126 loops=1)

55. 0.725 0.725 ↑ 1.0 2,126 1

Seq Scan on quotes quotes_1 (cost=0.00..204.18 rows=2,126 width=63) (actual time=0.016..0.725 rows=2,126 loops=1)

  • Filter: ((type)::text = 'lst'::text)
  • Rows Removed by Filter: 688
56. 0.280 8.120 ↑ 2.0 1 140

Append (cost=0.27..3.43 rows=2 width=36) (actual time=0.056..0.058 rows=1 loops=140)

57. 7.560 7.560 ↓ 0.0 0 140

Index Only Scan using stockinfo_insid on stockinfo (cost=0.27..0.34 rows=1 width=36) (actual time=0.054..0.054 rows=0 loops=140)

  • Index Cond: (insid = w.insid)
  • Heap Fetches: 0
58. 0.280 0.280 ↑ 1.0 1 140

Index Only Scan using options_insid on options (cost=0.42..3.08 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=140)

  • Index Cond: (insid = w.insid)
  • Heap Fetches: 0
59. 0.123 0.598 ↑ 1.0 484 1

Hash (cost=83.84..83.84 rows=484 width=20) (actual time=0.598..0.598 rows=484 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
60. 0.475 0.475 ↑ 1.0 484 1

Seq Scan on stockinfo s (cost=0.00..83.84 rows=484 width=20) (actual time=0.013..0.475 rows=484 loops=1)

Execution time : 1,008.268 ms