explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aumn

Settings
# exclusive inclusive rows x rows loops node
1. 14.225 366.678 ↓ 56.3 957 1

Nested Loop Left Join (cost=41,208.62..41,540.23 rows=17 width=537) (actual time=304.801..366.678 rows=957 loops=1)

2.          

CTE reticles

3. 0.699 22.154 ↑ 1.0 956 1

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

4. 17.631 17.631 ↓ 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.462..17.631 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. 84.051 97.205 ↑ 1.0 956 1

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

8.          

Initplan (forFunction Scan)

9. 13.041 13.154 ↑ 1.0 1 1

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

10. 0.113 0.113 ↑ 1.0 956 1

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

11.          

CTE foo

12. 1.056 57.207 ↑ 2.9 825 1

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

  • Group Key: cd_structure_insp.reticle_master_id
13. 0.771 56.151 ↑ 1.1 2,227 1

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

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

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

15. 18.829 55.251 ↓ 1.0 120,467 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 7024kB
16. 36.422 36.422 ↓ 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.005..36.422 rows=120,467 loops=1)

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

CTE bar

18. 8.039 110.480 ↓ 404.0 404 1

Nested Loop (cost=6,262.36..7,429.34 rows=1 width=38) (actual time=40.359..110.480 rows=404 loops=1)

  • Join Filter: ((rrl.mask_no)::text = (rd.mask_no)::text)
  • Rows Removed by Join Filter: 10675
19. 9.410 80.283 ↓ 124.5 11,079 1

Nested Loop (cost=6,261.95..7,328.97 rows=89 width=57) (actual time=40.329..80.283 rows=11,079 loops=1)

  • Join Filter: (reticles_3.reticle_master_id = rm.reticle_master_id)
20. 3.720 48.715 ↓ 124.5 11,079 1

Hash Join (cost=6,261.53..7,244.70 rows=89 width=58) (actual time=40.313..48.715 rows=11,079 loops=1)

  • Hash Cond: (rrl.reticle_request_id = pg.reticle_request_id)
21. 5.353 5.353 ↓ 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.181..5.353 rows=11,511 loops=1)

  • Filter: (estimated_release_to_warp IS NOT NULL)
  • Rows Removed by Filter: 17577
22. 0.278 39.642 ↑ 1.3 730 1

Hash (cost=6,249.61..6,249.61 rows=954 width=70) (actual time=39.642..39.642 rows=730 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 72kB
23. 0.185 39.364 ↓ 1.0 956 1

Nested Loop (cost=5,616.20..6,249.61 rows=954 width=70) (actual time=35.680..39.364 rows=956 loops=1)

24. 0.874 36.311 ↑ 1.0 956 1

Hash Join (cost=5,615.79..5,648.26 rows=962 width=70) (actual time=35.662..36.311 rows=956 loops=1)

  • Hash Cond: (reticles_3.reticle_master_id = pm.proj_id)
25. 0.155 0.155 ↑ 1.0 956 1

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

26. 19.663 35.282 ↓ 1.0 91,582 1

Hash (cost=4,475.35..4,475.35 rows=91,235 width=38) (actual time=35.282..35.282 rows=91,582 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 7270kB
27. 15.619 15.619 ↓ 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..15.619 rows=91,582 loops=1)

28. 2.868 2.868 ↑ 1.0 1 956

Index Scan using proj_master_pkey on proj_master pg (cost=0.42..0.62 rows=1 width=38) (actual time=0.003..0.003 rows=1 loops=956)

  • Index Cond: (proj_id = pm.proj_parent_id)
29. 22.158 22.158 ↑ 1.0 1 11,079

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

  • Index Cond: (reticle_master_id = pm.proj_id)
30. 22.158 22.158 ↑ 1.0 1 11,079

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

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

CTE whiteborder

32. 0.621 32.788 ↑ 1.2 956 1

HashAggregate (cost=7,723.02..7,801.28 rows=1,118 width=74) (actual time=32.575..32.788 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)
33. 1.147 32.167 ↑ 1.2 956 1

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

  • Hash Cond: ((rm_1.resist_type)::text = (lm.lookup_value)::text)
34. 0.506 30.475 ↑ 1.0 956 1

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

  • Hash Cond: (rd_1.design_master_id = dm.design_master_id)
35. 0.788 29.588 ↑ 1.0 956 1

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

36. 0.513 25.932 ↑ 1.0 956 1

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

37. 22.551 22.551 ↑ 1.0 956 1

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

38. 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_4.reticle_master_id)
39. 2.868 2.868 ↑ 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.003 rows=1 loops=956)

  • Index Cond: (reticle_design_id = rm_1.reticle_design_id)
40. 0.169 0.381 ↓ 1.0 909 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 68kB
41. 0.212 0.212 ↓ 1.0 909 1

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

42. 0.282 0.545 ↑ 1.0 1,735 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 98kB
43. 0.263 0.263 ↑ 1.0 1,735 1

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

44. 0.873 325.657 ↓ 56.3 957 1

Hash Left Join (cost=6,467.51..6,578.61 rows=17 width=509) (actual time=304.748..325.657 rows=957 loops=1)

  • Hash Cond: (reticle_master.reticle_master_id = (ttod.reticle_master_id)::bytea)
45. 0.349 227.126 ↓ 56.3 957 1

Nested Loop (cost=6,435.01..6,539.68 rows=17 width=477) (actual time=207.079..227.126 rows=957 loops=1)

46. 0.562 222.949 ↓ 56.3 957 1

Hash Left Join (cost=6,434.60..6,527.80 rows=17 width=533) (actual time=207.067..222.949 rows=957 loops=1)

  • Hash Cond: ((reticle_master.reticle_ship_to_location)::text = (fab_info.complete_fab_name)::text)
47. 0.549 222.364 ↓ 56.3 957 1

Hash Left Join (cost=6,430.43..6,523.40 rows=17 width=533) (actual time=207.026..222.364 rows=957 loops=1)

  • Hash Cond: (reticle_master.reticle_master_id = bar.reticle_master_id)
48. 1.188 110.926 ↓ 56.2 956 1

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

49. 0.702 106.870 ↓ 56.2 956 1

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

50. 1.118 104.256 ↓ 56.2 956 1

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

51. 0.445 98.358 ↓ 56.2 956 1

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

  • Hash Cond: (reticle_master.vendor_mfg_site_id = vendor_mfg_site.vendor_mfg_site_id)
52. 0.799 97.902 ↓ 56.2 956 1

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

  • Hash Cond: (foo.reticle_master_id = reticle_master.reticle_master_id)
53. 57.719 57.719 ↑ 2.9 825 1

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

54. 0.379 39.384 ↓ 56.2 956 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 259kB
55. 0.460 39.005 ↓ 56.2 956 1

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

  • Hash Cond: (whiteborder.reticle_master_id = reticle_master.reticle_master_id)
56. 33.030 33.030 ↑ 1.2 956 1

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

57. 0.455 5.515 ↑ 1.0 956 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 236kB
58. 0.142 5.060 ↑ 1.0 956 1

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

59. 0.138 0.138 ↑ 1.0 956 1

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

60. 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)
61. 0.006 0.011 ↑ 1.0 18 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
62. 0.005 0.005 ↑ 1.0 18 1

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

63. 4.780 4.780 ↑ 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.005..0.005 rows=1 loops=956)

  • Index Cond: (reticle_design_id = reticle_master.reticle_design_id)
64. 1.912 1.912 ↑ 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.002..0.002 rows=1 loops=956)

  • Index Cond: (design_master_id = reticle_design.design_master_id)
65. 2.868 2.868 ↑ 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.003..0.003 rows=1 loops=956)

  • Index Cond: (reticle_order_no = reticle_master.reticle_order_no)
66. 0.178 110.889 ↓ 404.0 404 1

Hash (cost=0.02..0.02 rows=1 width=36) (actual time=110.889..110.889 rows=404 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
67. 110.711 110.711 ↓ 404.0 404 1

CTE Scan on bar (cost=0.00..0.02 rows=1 width=36) (actual time=40.361..110.711 rows=404 loops=1)

68. 0.011 0.023 ↑ 1.0 50 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
69. 0.012 0.012 ↑ 1.0 52 1

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

70. 3.828 3.828 ↑ 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.004..0.004 rows=1 loops=957)

  • Index Cond: (proj_id = reticle_master.reticle_master_id)
71. 0.238 97.658 ↑ 1.0 956 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 64kB
72. 97.420 97.420 ↑ 1.0 956 1

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

73. 3.828 3.828 ↑ 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.003..0.004 rows=1 loops=957)

  • Index Cond: (reticle_master.reticle_master_id = reticle_master_id)
74.          

SubPlan (forNested Loop Left Join)

75. 12.705 22.968 ↑ 1.0 1 957

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

  • Hash Cond: (ns.node_series_id = nl.node_series_id)
76. 7.392 7.392 ↓ 1.0 153 616

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

77. 0.000 2.871 ↑ 1.0 1 957

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
78. 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.002..0.003 rows=1 loops=957)

  • Index Cond: (node_layer_id = reticle_master.node_layer_id_overlay)