explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dYhM

Settings
# exclusive inclusive rows x rows loops node
1. 63,362.277 294,221.042 ↓ 1,684.0 1,684 1

Nested Loop (cost=18.14..54.86 rows=1 width=1,548) (actual time=21,594.001..294,221.042 rows=1,684 loops=1)

  • Join Filter: ((psss.srvc_spec_id = rl.row_id) AND (grs.gnl_role_spec_id = rl.gnl_role_spec_id) AND (gt.gnl_tp_id = rl.rel_tp_id))
  • Rows Removed by Join Filter: 175134316
2. 2,470.986 6,684.685 ↓ 7,005,440.0 7,005,440 1

Nested Loop (cost=18.14..52.18 rows=1 width=1,468) (actual time=0.743..6,684.685 rows=7,005,440 loops=1)

3. 121.791 386.499 ↓ 16,640.0 16,640 1

Nested Loop (cost=18.14..38.76 rows=1 width=1,304) (actual time=0.723..386.499 rows=16,640 loops=1)

  • Join Filter: (grs.gnl_role_spec_id = grsl.gnl_role_spec_id)
  • Rows Removed by Join Filter: 249600
4. 13.594 89.988 ↓ 8,320.0 8,320 1

Nested Loop (cost=18.14..36.35 rows=1 width=800) (actual time=0.688..89.988 rows=8,320 loops=1)

5. 3.989 54.034 ↓ 520.0 520 1

Nested Loop (cost=18.14..33.64 rows=1 width=472) (actual time=0.665..54.034 rows=520 loops=1)

6. 1.809 43.285 ↓ 520.0 520 1

Nested Loop (cost=17.99..33.35 rows=1 width=490) (actual time=0.637..43.285 rows=520 loops=1)

7. 2.519 33.676 ↓ 260.0 260 1

Nested Loop Left Join (cost=17.71..32.50 rows=1 width=72) (actual time=0.595..33.676 rows=260 loops=1)

  • Join Filter: (psrs.rsrc_spec_id = r.rsrc_spec_id)
  • Rows Removed by Join Filter: 1819
8. 1.443 25.177 ↓ 260.0 260 1

Nested Loop Left Join (cost=9.41..22.87 rows=1 width=90) (actual time=0.418..25.177 rows=260 loops=1)

9. 1.486 22.174 ↓ 260.0 260 1

Nested Loop (cost=9.28..22.70 rows=1 width=90) (actual time=0.389..22.174 rows=260 loops=1)

10. 1.620 17.048 ↓ 260.0 260 1

Nested Loop (cost=9.13..22.44 rows=1 width=108) (actual time=0.355..17.048 rows=260 loops=1)

11. 2.178 11.405 ↓ 447.0 447 1

Nested Loop (cost=8.85..22.02 rows=1 width=90) (actual time=0.322..11.405 rows=447 loops=1)

12. 0.489 2.969 ↓ 447.0 447 1

Nested Loop (cost=8.58..13.71 rows=1 width=54) (actual time=0.279..2.969 rows=447 loops=1)

13. 0.257 0.508 ↓ 29.0 29 1

Hash Join (cost=8.30..12.14 rows=1 width=18) (actual time=0.238..0.508 rows=29 loops=1)

  • Hash Cond: (sp.st_id = s_1.gnl_st_id)
14. 0.100 0.100 ↑ 2.3 29 1

Seq Scan on srvc_spec sp (cost=0.00..3.66 rows=66 width=36) (actual time=0.043..0.100 rows=29 loops=1)

15. 0.044 0.151 ↓ 58.0 58 1

Hash (cost=8.29..8.29 rows=1 width=18) (actual time=0.151..0.151 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
16. 0.107 0.107 ↓ 58.0 58 1

Index Scan using nui_gnl_st_03 on gnl_st s_1 (cost=0.27..8.29 rows=1 width=18) (actual time=0.062..0.107 rows=58 loops=1)

  • Index Cond: (((shrt_code)::text = 'ACTV'::text) AND (is_actv = '1'::numeric))
17. 1.972 1.972 ↓ 15.0 15 29

Index Scan using ref_132_fk on prod_spec_srvc_spec psss (cost=0.27..1.57 rows=1 width=36) (actual time=0.019..0.068 rows=15 loops=29)

  • Index Cond: (srvc_spec_id = sp.srvc_spec_id)
  • Filter: (is_actv = '1'::numeric)
18. 6.258 6.258 ↑ 1.0 1 447

Index Only Scan using nui_prod_spec_02 on prod_spec p (cost=0.28..8.29 rows=1 width=36) (actual time=0.014..0.014 rows=1 loops=447)

  • Index Cond: (prod_spec_id = psss.prod_spec_id)
  • Heap Fetches: 447
19. 4.023 4.023 ↑ 3.0 1 447

Index Scan using ref_111_fk on prod_ofr po (cost=0.28..0.39 rows=3 width=54) (actual time=0.008..0.009 rows=1 loops=447)

  • Index Cond: (prod_spec_id = p.prod_spec_id)
20. 3.640 3.640 ↑ 1.0 1 260

Index Scan using gnl_st_pkey on gnl_st s (cost=0.15..0.20 rows=1 width=18) (actual time=0.014..0.014 rows=1 loops=260)

  • Index Cond: (gnl_st_id = p.st_id)
  • Filter: ((is_actv = '1'::numeric) AND ((shrt_code)::text = 'ACTV'::text))
21. 1.560 1.560 ↓ 0.0 0 260

Index Scan using ref_133_fk on prod_spec_rsrc_spec psrs (cost=0.13..0.16 rows=1 width=36) (actual time=0.006..0.006 rows=0 loops=260)

  • Index Cond: (p.prod_spec_id = prod_spec_id)
  • Filter: (is_actv = '1'::numeric)
22. 3.545 5.980 ↓ 7.0 7 260

Hash Join (cost=8.30..9.62 rows=1 width=18) (actual time=0.017..0.023 rows=7 loops=260)

  • Hash Cond: (r.st_id = s_2.gnl_st_id)
23. 2.340 2.340 ↑ 3.6 7 260

Seq Scan on rsrc_spec r (cost=0.00..1.25 rows=25 width=36) (actual time=0.008..0.009 rows=7 loops=260)

24. 0.036 0.095 ↓ 58.0 58 1

Hash (cost=8.29..8.29 rows=1 width=18) (actual time=0.095..0.095 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
25. 0.059 0.059 ↓ 58.0 58 1

Index Scan using nui_gnl_st_03 on gnl_st s_2 (cost=0.27..8.29 rows=1 width=18) (actual time=0.033..0.059 rows=58 loops=1)

  • Index Cond: (((shrt_code)::text = 'ACTV'::text) AND (is_actv = '1'::numeric))
26. 7.800 7.800 ↓ 2.0 2 260

Index Scan using idx_pol_prod_ofr_id on prod_ofr_lang pol (cost=0.28..0.83 rows=1 width=436) (actual time=0.014..0.030 rows=2 loops=260)

  • Index Cond: (prod_ofr_id = po.prod_ofr_id)
  • Filter: (is_actv = '1'::numeric)
27. 6.760 6.760 ↑ 1.0 1 520

Index Scan using gnl_st_pkey on gnl_st gs (cost=0.15..0.22 rows=1 width=18) (actual time=0.013..0.013 rows=1 loops=520)

  • Index Cond: (gnl_st_id = po.st_id)
  • Filter: (((shrt_code)::text <> 'DEL'::text) AND (is_actv = '1'::numeric))
28. 22.360 22.360 ↓ 16.0 16 520

Seq Scan on gnl_role_spec grs (cost=0.00..2.70 rows=1 width=328) (actual time=0.010..0.043 rows=16 loops=520)

  • Filter: (is_actv = '1'::numeric)
29. 174.720 174.720 ↓ 32.0 32 8,320

Seq Scan on gnl_role_spec_lang grsl (cost=0.00..2.40 rows=1 width=504) (actual time=0.006..0.021 rows=32 loops=8,320)

  • Filter: (is_actv = '1'::numeric)
30. 3,827.200 3,827.200 ↓ 210.5 421 16,640

Seq Scan on gnl_tp gt (cost=0.00..13.40 rows=2 width=164) (actual time=0.005..0.230 rows=421 loops=16,640)

  • Filter: (is_actv = '1'::numeric)
  • Rows Removed by Filter: 5
31. 224,174.080 224,174.080 ↓ 25.0 25 7,005,440

Seq Scan on gnl_spec_role_rel rl (cost=0.00..2.66 rows=1 width=152) (actual time=0.008..0.032 rows=25 loops=7,005,440)

  • Filter: ((is_actv = '1'::numeric) AND (data_tp_id = '110'::numeric))
  • Rows Removed by Filter: 38