explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SDUX

Settings
# exclusive inclusive rows x rows loops node
1. 16.206 1,517.008 ↓ 56.3 957 1

Hash Left Join (cost=56,974.84..57,306.45 rows=17 width=537) (actual time=1,443.529..1,517.008 rows=957 loops=1)

  • Hash Cond: (reticle_master.reticle_master_id = (ttod.reticle_master_id)::bytea)
2.          

CTE reticles

3. 0.881 25.831 ↑ 1.0 956 1

Nested Loop (cost=0.41..11,267.10 rows=962 width=19) (actual time=0.734..25.831 rows=956 loops=1)

4. 21.126 21.126 ↓ 1.0 956 1

Seq Scan on reticle_order reticle_order_1 (cost=0.00..4,821.32 rows=917 width=19) (actual time=0.720..21.126 rows=956 loops=1)

  • Filter: ((fracture_status)::text !~~ '%Completed%'::text)
  • Rows Removed by Filter: 60116
5. 3.824 3.824 ↑ 1.0 1 956

Index Scan using reticle_master_reticle_order_no on reticle_master reticle_master_1 (cost=0.41..7.02 rows=1 width=38) (actual time=0.004..0.004 rows=1 loops=956)

  • Index Cond: (reticle_order_no = reticle_order_1.reticle_order_no)
6.          

CTE ttod

7. 98.860 112.055 ↑ 1.0 956 1

Function Scan on sel_racetrack_twit_dates (cost=21.91..31.91 rows=1,000 width=188) (actual time=111.989..112.055 rows=956 loops=1)

8.          

Initplan (forFunction Scan)

9. 13.072 13.195 ↑ 1.0 1 1

Aggregate (cost=21.65..21.66 rows=1 width=32) (actual time=13.194..13.195 rows=1 loops=1)

10. 0.123 0.123 ↑ 1.0 956 1

CTE Scan on reticles reticles_1 (cost=0.00..19.24 rows=962 width=32) (actual time=0.001..0.123 rows=956 loops=1)

11.          

CTE foo

12. 1.062 62.732 ↑ 2.9 825 1

HashAggregate (cost=8,186.97..8,211.07 rows=2,410 width=19) (actual time=62.168..62.732 rows=825 loops=1)

  • Group Key: cd_structure_insp.reticle_master_id
13. 0.823 61.670 ↑ 1.1 2,227 1

Hash Join (cost=8,028.16..8,174.92 rows=2,410 width=19) (actual time=60.841..61.670 rows=2,227 loops=1)

  • Hash Cond: (reticles_2.reticle_master_id = cd_structure_insp.reticle_master_id)
14. 0.117 0.117 ↑ 1.0 956 1

CTE Scan on reticles reticles_2 (cost=0.00..19.24 rows=962 width=32) (actual time=0.000..0.117 rows=956 loops=1)

15. 20.745 60.730 ↓ 1.0 120,467 1

Hash (cost=6,524.04..6,524.04 rows=120,330 width=19) (actual time=60.730..60.730 rows=120,467 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 7024kB
16. 39.985 39.985 ↓ 1.0 120,467 1

Seq Scan on cd_structure_insp (cost=0.00..6,524.04 rows=120,330 width=19) (actual time=0.006..39.985 rows=120,467 loops=1)

  • Filter: ((cd_requirement)::text = ANY ('{Required,Informational}'::text[]))
  • Rows Removed by Filter: 19531
17.          

CTE bar

18. 10.063 1,213.079 ↓ 159.3 10,196 1

Nested Loop (cost=3,511.28..23,193.52 rows=64 width=38) (actual time=34.116..1,213.079 rows=10,196 loops=1)

  • Join Filter: ((rrl.mask_no)::text = (rd.mask_no)::text)
  • Rows Removed by Join Filter: 169616
19. 3.861 663.580 ↓ 29.9 179,812 1

Nested Loop (cost=3,510.87..16,412.98 rows=6,012 width=57) (actual time=34.092..663.580 rows=179,812 loops=1)

20. 65.910 120.268 ↓ 21.2 179,817 1

Hash Join (cost=3,510.45..8,412.66 rows=8,472 width=26) (actual time=34.075..120.268 rows=179,817 loops=1)

  • Hash Cond: (pm.proj_parent_id = pg.proj_id)
21. 20.384 20.384 ↓ 1.0 91,582 1

Seq Scan on proj_master pm (cost=0.00..4,475.35 rows=91,235 width=38) (actual time=0.002..20.384 rows=91,582 loops=1)

22. 3.392 33.974 ↓ 1.3 10,707 1

Hash (cost=3,403.63..3,403.63 rows=8,546 width=26) (actual time=33.974..33.974 rows=10,707 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 745kB
23. 6.248 30.582 ↓ 1.3 10,707 1

Merge Join (cost=1,701.34..3,403.63 rows=8,546 width=26) (actual time=12.657..30.582 rows=10,707 loops=1)

  • Merge Cond: (pg.reticle_request_id = rrl.reticle_request_id)
24. 10.158 10.158 ↑ 10.2 8,913 1

Index Scan using fki_proj_master_reticle_request_fk on proj_master pg (cost=0.29..16,219.78 rows=91,235 width=38) (actual time=0.007..10.158 rows=8,913 loops=1)

25. 8.363 14.176 ↓ 1.0 11,516 1

Sort (cost=1,701.05..1,729.31 rows=11,306 width=26) (actual time=12.643..14.176 rows=11,516 loops=1)

  • Sort Key: rrl.reticle_request_id
  • Sort Method: quicksort Memory: 1284kB
26. 5.813 5.813 ↓ 1.0 11,511 1

Seq Scan on reticle_request_layer rrl (cost=0.00..939.88 rows=11,306 width=26) (actual time=0.160..5.813 rows=11,511 loops=1)

  • Filter: (estimated_release_to_warp IS NOT NULL)
  • Rows Removed by Filter: 17577
27. 539.451 539.451 ↑ 1.0 1 179,817

Index Scan using reticle_master_pkey on reticle_master rm (cost=0.41..0.93 rows=1 width=50) (actual time=0.002..0.003 rows=1 loops=179,817)

  • Index Cond: (reticle_master_id = pm.proj_id)
28. 539.436 539.436 ↑ 1.0 1 179,812

Index Scan using reticle_design_pkey on reticle_design rd (cost=0.41..1.12 rows=1 width=22) (actual time=0.002..0.003 rows=1 loops=179,812)

  • Index Cond: (reticle_design_id = rm.reticle_design_id)
29.          

CTE whiteborder

30. 0.613 37.486 ↑ 1.2 956 1

HashAggregate (cost=7,723.02..7,801.28 rows=1,118 width=74) (actual time=37.278..37.486 rows=956 loops=1)

  • Group Key: rm_1.reticle_master_id, CASE WHEN ((lm.lookup_value_desc)::text = 'Negative'::text) THEN 'BLUE'::text WHEN ((((rm_1.reticle_mask_type)::text ~~ '%Attenuated Phase-Shift%'::text) OR ((rm_1.reticle_mask_type)::text ~~ '%Hi-T%'::text)) AND (rm_1.stepper_wavelength = '193'::numeric) AND ((lm.lookup_value_desc)::text = 'Positive'::text)
31. 1.181 36.873 ↑ 1.2 956 1

Hash Left Join (cost=115.59..7,717.43 rows=1,118 width=74) (actual time=2.437..36.873 rows=956 loops=1)

  • Hash Cond: ((rm_1.resist_type)::text = (lm.lookup_value)::text)
32. 0.533 34.707 ↑ 1.0 956 1

Hash Join (cost=57.19..7,541.09 rows=962 width=81) (actual time=1.436..34.707 rows=956 loops=1)

  • Hash Cond: (rd_1.design_master_id = dm.design_master_id)
33. 0.000 33.500 ↑ 1.0 956 1

Nested Loop (cost=0.83..7,471.50 rows=962 width=85) (actual time=0.754..33.500 rows=956 loops=1)

34. 0.606 29.680 ↑ 1.0 956 1

Nested Loop (cost=0.41..6,388.93 rows=962 width=85) (actual time=0.744..29.680 rows=956 loops=1)

35. 26.206 26.206 ↑ 1.0 956 1

CTE Scan on reticles reticles_3 (cost=0.00..19.24 rows=962 width=32) (actual time=0.735..26.206 rows=956 loops=1)

36. 2.868 2.868 ↑ 1.0 1 956

Index Scan using reticle_master_pkey on reticle_master rm_1 (cost=0.41..6.61 rows=1 width=85) (actual time=0.003..0.003 rows=1 loops=956)

  • Index Cond: (reticle_master_id = reticles_3.reticle_master_id)
37. 3.824 3.824 ↑ 1.0 1 956

Index Scan using reticle_design_pkey on reticle_design rd_1 (cost=0.41..1.12 rows=1 width=38) (actual time=0.003..0.004 rows=1 loops=956)

  • Index Cond: (reticle_design_id = rm_1.reticle_design_id)
38. 0.270 0.674 ↓ 1.0 909 1

Hash (cost=45.05..45.05 rows=905 width=34) (actual time=0.674..0.674 rows=909 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 68kB
39. 0.404 0.404 ↓ 1.0 909 1

Seq Scan on design_master dm (cost=0.00..45.05 rows=905 width=34) (actual time=0.005..0.404 rows=909 loops=1)

40. 0.465 0.985 ↑ 1.0 1,735 1

Hash (cost=36.51..36.51 rows=1,751 width=17) (actual time=0.985..0.985 rows=1,735 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 98kB
41. 0.520 0.520 ↑ 1.0 1,735 1

Seq Scan on lookup_master lm (cost=0.00..36.51 rows=1,751 width=17) (actual time=0.004..0.520 rows=1,735 loops=1)

42. 0.747 1,361.487 ↓ 56.3 957 1

Nested Loop (cost=6,437.48..6,550.50 rows=17 width=505) (actual time=1,330.956..1,361.487 rows=957 loops=1)

43. 0.726 1,355.955 ↓ 56.3 957 1

Hash Left Join (cost=6,437.06..6,538.62 rows=17 width=561) (actual time=1,330.946..1,355.955 rows=957 loops=1)

  • Hash Cond: ((reticle_master.reticle_ship_to_location)::text = (fab_info.complete_fab_name)::text)
44. 0.447 1,355.205 ↓ 56.3 957 1

Nested Loop Left Join (cost=6,432.89..6,534.22 rows=17 width=561) (actual time=1,330.909..1,355.205 rows=957 loops=1)

45. 0.723 1,349.973 ↓ 56.3 957 1

Hash Left Join (cost=6,432.47..6,525.45 rows=17 width=533) (actual time=1,330.898..1,349.973 rows=957 loops=1)

  • Hash Cond: (reticle_master.reticle_master_id = bar.reticle_master_id)
46. 0.484 124.769 ↓ 56.2 956 1

Nested Loop (cost=6,430.39..6,523.29 rows=17 width=529) (actual time=106.413..124.769 rows=956 loops=1)

47. 0.545 119.505 ↓ 56.2 956 1

Nested Loop (cost=6,429.98..6,511.12 rows=17 width=470) (actual time=106.403..119.505 rows=956 loops=1)

48. 0.539 116.092 ↓ 56.2 956 1

Nested Loop (cost=6,429.70..6,505.90 rows=17 width=479) (actual time=106.395..116.092 rows=956 loops=1)

49. 0.474 108.861 ↓ 56.2 956 1

Hash Left Join (cost=6,429.29..6,486.77 rows=17 width=451) (actual time=106.380..108.861 rows=956 loops=1)

  • Hash Cond: (reticle_master.vendor_mfg_site_id = vendor_mfg_site.vendor_mfg_site_id)
50. 0.897 108.377 ↓ 56.2 956 1

Hash Right Join (cost=6,427.88..6,485.13 rows=17 width=429) (actual time=106.360..108.377 rows=956 loops=1)

  • Hash Cond: (foo.reticle_master_id = reticle_master.reticle_master_id)
51. 63.297 63.297 ↑ 2.9 825 1

CTE Scan on foo (cost=0.00..48.20 rows=2,410 width=40) (actual time=62.170..63.297 rows=825 loops=1)

52. 0.378 44.183 ↓ 56.2 956 1

Hash (cost=6,427.67..6,427.67 rows=17 width=421) (actual time=44.183..44.183 rows=956 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 259kB
53. 0.488 43.805 ↓ 56.2 956 1

Hash Join (cost=6,400.95..6,427.67 rows=17 width=421) (actual time=42.876..43.805 rows=956 loops=1)

  • Hash Cond: (whiteborder.reticle_master_id = reticle_master.reticle_master_id)
54. 37.735 37.735 ↑ 1.2 956 1

CTE Scan on whiteborder (cost=0.00..22.36 rows=1,118 width=64) (actual time=37.280..37.735 rows=956 loops=1)

55. 0.465 5.582 ↑ 1.0 956 1

Hash (cost=6,388.93..6,388.93 rows=962 width=357) (actual time=5.582..5.582 rows=956 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 236kB
56. 0.206 5.117 ↑ 1.0 956 1

Nested Loop (cost=0.41..6,388.93 rows=962 width=357) (actual time=0.010..5.117 rows=956 loops=1)

57. 0.131 0.131 ↑ 1.0 956 1

CTE Scan on reticles (cost=0.00..19.24 rows=962 width=32) (actual time=0.001..0.131 rows=956 loops=1)

58. 4.780 4.780 ↑ 1.0 1 956

Index Scan using reticle_master_pkey on reticle_master (cost=0.41..6.61 rows=1 width=325) (actual time=0.004..0.005 rows=1 loops=956)

  • Index Cond: (reticle_master_id = reticles.reticle_master_id)
59. 0.003 0.010 ↑ 1.0 18 1

Hash (cost=1.18..1.18 rows=18 width=22) (actual time=0.010..0.010 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
60. 0.007 0.007 ↑ 1.0 18 1

Seq Scan on vendor_mfg_site (cost=0.00..1.18 rows=18 width=22) (actual time=0.004..0.007 rows=18 loops=1)

61. 6.692 6.692 ↑ 1.0 1 956

Index Scan using reticle_design_pkey on reticle_design (cost=0.41..1.12 rows=1 width=47) (actual time=0.006..0.007 rows=1 loops=956)

  • Index Cond: (reticle_design_id = reticle_master.reticle_design_id)
62. 2.868 2.868 ↑ 1.0 1 956

Index Scan using design_master_pkey on design_master (cost=0.28..0.30 rows=1 width=29) (actual time=0.003..0.003 rows=1 loops=956)

  • Index Cond: (design_master_id = reticle_design.design_master_id)
63. 4.780 4.780 ↑ 1.0 1 956

Index Scan using reticle_order_pkey on reticle_order (cost=0.41..0.71 rows=1 width=78) (actual time=0.004..0.005 rows=1 loops=956)

  • Index Cond: (reticle_order_no = reticle_master.reticle_order_no)
64. 4.083 1,224.481 ↓ 159.3 10,196 1

Hash (cost=1.28..1.28 rows=64 width=36) (actual time=1,224.481..1,224.481 rows=10,196 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 676kB
65. 1,220.398 1,220.398 ↓ 159.3 10,196 1

CTE Scan on bar (cost=0.00..1.28 rows=64 width=36) (actual time=34.118..1,220.398 rows=10,196 loops=1)

66. 4.785 4.785 ↑ 1.0 1 957

Index Scan using whiteboard_reticle_status_pk on whiteboard_reticle_status wrs (cost=0.41..0.51 rows=1 width=46) (actual time=0.004..0.005 rows=1 loops=957)

  • Index Cond: (reticle_master.reticle_master_id = reticle_master_id)
67. 0.010 0.024 ↑ 1.0 50 1

Hash (cost=3.52..3.52 rows=52 width=8) (actual time=0.024..0.024 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
68. 0.014 0.014 ↑ 1.0 52 1

Seq Scan on fab_info (cost=0.00..3.52 rows=52 width=8) (actual time=0.004..0.014 rows=52 loops=1)

69. 4.785 4.785 ↑ 1.0 1 957

Index Scan using proj_master_pkey on proj_master (cost=0.42..0.69 rows=1 width=27) (actual time=0.005..0.005 rows=1 loops=957)

  • Index Cond: (proj_id = reticle_master.reticle_master_id)
70. 0.228 112.519 ↑ 1.0 956 1

Hash (cost=20.00..20.00 rows=1,000 width=64) (actual time=112.519..112.519 rows=956 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 64kB
71. 112.291 112.291 ↑ 1.0 956 1

CTE Scan on ttod (cost=0.00..20.00 rows=1,000 width=64) (actual time=111.992..112.291 rows=956 loops=1)

72.          

SubPlan (forHash Left Join)

73. 14.344 26.796 ↑ 1.0 1 957

Hash Join (cost=8.31..12.42 rows=1 width=28) (actual time=0.024..0.028 rows=1 loops=957)

  • Hash Cond: (ns.node_series_id = nl.node_series_id)
74. 8.624 8.624 ↓ 1.0 153 616

Seq Scan on node_series ns (cost=0.00..3.52 rows=152 width=42) (actual time=0.002..0.014 rows=153 loops=616)

75. 0.957 3.828 ↑ 1.0 1 957

Hash (cost=8.30..8.30 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=957)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
76. 2.871 2.871 ↑ 1.0 1 957

Index Scan using node_layer_pkey on node_layer nl (cost=0.29..8.30 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=957)

  • Index Cond: (node_layer_id = reticle_master.node_layer_id_overlay)