explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HXWr

Settings
# exclusive inclusive rows x rows loops node
1. 8.501 141,452.347 ↓ 842.0 842 1

Nested Loop (cost=18.14..53.15 rows=1 width=1,548) (actual time=10,425.895..141,452.347 rows=842 loops=1)

  • Join Filter: (rl.gnl_role_spec_id = grs.gnl_role_spec_id)
  • Rows Removed by Join Filter: 5984
2. 29,583.297 141,432.058 ↓ 842.0 842 1

Nested Loop (cost=18.14..50.44 rows=1 width=1,256) (actual time=10,425.868..141,432.058 rows=842 loops=1)

  • Join Filter: ((psss.srvc_spec_id = rl.row_id) AND (grsl.gnl_role_spec_id = rl.gnl_role_spec_id) AND (gt.gnl_tp_id = rl.rel_tp_id))
  • Rows Removed by Join Filter: 87567158
3. 1,150.568 3,264.441 ↓ 3,502,720.0 3,502,720 1

Nested Loop (cost=18.14..48.47 rows=1 width=1,140) (actual time=0.409..3,264.441 rows=3,502,720 loops=1)

4. 15.672 83.793 ↓ 8,320.0 8,320 1

Nested Loop (cost=18.14..35.05 rows=1 width=976) (actual time=0.392..83.793 rows=8,320 loops=1)

  • Join Filter: ((pol.lang)::text = (grsl.lang)::text)
  • Rows Removed by Join Filter: 8320
5. 3.429 52.521 ↓ 520.0 520 1

Nested Loop (cost=18.14..33.64 rows=1 width=522) (actual time=0.375..52.521 rows=520 loops=1)

6. 1.819 41.812 ↓ 520.0 520 1

Nested Loop (cost=17.99..33.35 rows=1 width=540) (actual time=0.367..41.812 rows=520 loops=1)

7. 2.654 32.193 ↓ 260.0 260 1

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

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

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

9. 1.518 20.395 ↓ 260.0 260 1

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

10. 1.539 15.237 ↓ 260.0 260 1

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

11. 2.159 10.122 ↓ 447.0 447 1

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

12. 0.466 2.599 ↓ 447.0 447 1

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

13. 0.205 0.393 ↓ 29.0 29 1

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

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

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

15. 0.025 0.116 ↓ 58.0 58 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
16. 0.091 0.091 ↓ 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.055..0.091 rows=58 loops=1)

  • Index Cond: (((shrt_code)::text = 'ACTV'::text) AND (is_actv = '1'::numeric))
17. 1.740 1.740 ↓ 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.018..0.060 rows=15 loops=29)

  • Index Cond: (srvc_spec_id = sp.srvc_spec_id)
  • Filter: (is_actv = '1'::numeric)
18. 5.364 5.364 ↑ 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.012..0.012 rows=1 loops=447)

  • Index Cond: (prod_spec_id = psss.prod_spec_id)
  • Heap Fetches: 447
19. 3.576 3.576 ↑ 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.007..0.008 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.835 6.240 ↓ 7.0 7 260

Hash Join (cost=8.30..9.62 rows=1 width=18) (actual time=0.018..0.024 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.020 0.065 ↓ 58.0 58 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
25. 0.045 0.045 ↓ 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.022..0.045 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=486) (actual time=0.015..0.030 rows=2 loops=260)

  • Index Cond: (prod_ofr_id = po.prod_ofr_id)
  • Filter: (is_actv = '1'::numeric)
27. 7.280 7.280 ↑ 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.014..0.014 rows=1 loops=520)

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

Seq Scan on gnl_role_spec_lang grsl (cost=0.00..1.40 rows=1 width=504) (actual time=0.007..0.030 rows=32 loops=520)

  • Filter: (is_actv = '1'::numeric)
29. 2,030.080 2,030.080 ↓ 210.5 421 8,320

Seq Scan on gnl_tp gt (cost=0.00..13.40 rows=2 width=164) (actual time=0.006..0.244 rows=421 loops=8,320)

  • Filter: (is_actv = '1'::numeric)
  • Rows Removed by Filter: 5
30. 108,584.320 108,584.320 ↓ 25.0 25 3,502,720

Seq Scan on gnl_spec_role_rel rl (cost=0.00..1.94 rows=1 width=152) (actual time=0.005..0.031 rows=25 loops=3,502,720)

  • Filter: ((is_actv = '1'::numeric) AND (data_tp_id = '110'::numeric))
  • Rows Removed by Filter: 38
31. 11.788 11.788 ↓ 8.0 8 842

Seq Scan on gnl_role_spec grs (cost=0.00..2.70 rows=1 width=328) (actual time=0.010..0.014 rows=8 loops=842)

  • Filter: (is_actv = '1'::numeric)