explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cjsx : Optimization for: plan #O6Dq

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,915.969 4,798.676 ↑ 1.0 1 1

Nested Loop (cost=14.88..10,044.88 rows=1 width=5,371) (actual time=4,137.703..4,798.676 rows=1 loops=1)

  • Join Filter: (prodentity1_.prod_id = prod.prod_id)
  • Rows Removed by Join Filter: 14,956,433
2. 113.772 2,161.915 ↓ 1,958.7 180,198 1

Nested Loop (cost=14.03..9,789.54 rows=92 width=13) (actual time=0.149..2,161.915 rows=180,198 loops=1)

  • Join Filter: (gnl_char_val.char_val_id = gnl_char_val_lang.char_val_id)
3. 50.819 1,687.747 ↓ 1,896.8 180,198 1

Nested Loop (cost=13.75..9,757.93 rows=95 width=28) (actual time=0.143..1,687.747 rows=180,198 loops=1)

4. 102.853 1,456.730 ↓ 1,896.8 180,198 1

Nested Loop (cost=13.60..9,742.26 rows=95 width=22) (actual time=0.129..1,456.730 rows=180,198 loops=1)

5. 61.245 1,173.622 ↓ 559.8 180,255 1

Nested Loop (cost=13.46..9,687.54 rows=322 width=29) (actual time=0.125..1,173.622 rows=180,255 loops=1)

6. 80.471 391.357 ↓ 559.8 180,255 1

Hash Join (cost=13.02..8,699.94 rows=322 width=16) (actual time=0.118..391.357 rows=180,255 loops=1)

  • Hash Cond: (prod_char_val.st_id = charvalstatus.gnl_st_id)
7. 76.366 310.816 ↓ 264.1 372,158 1

Nested Loop (cost=0.85..8,684.02 rows=1,409 width=23) (actual time=0.043..310.816 rows=372,158 loops=1)

8. 0.002 0.048 ↑ 1.0 1 1

Nested Loop (cost=0.28..12.67 rows=1 width=15) (actual time=0.015..0.048 rows=1 loops=1)

9. 0.039 0.039 ↑ 1.0 1 1

Seq Scan on gnl_char (cost=0.00..8.36 rows=1 width=6) (actual time=0.008..0.039 rows=1 loops=1)

  • Filter: ((shrt_code)::text = 'deviceType'::text)
  • Rows Removed by Filter: 268
10. 0.007 0.007 ↑ 1.0 1 1

Index Only Scan using idx_gnl_char_lang_char_id_lang_is_actv on gnl_char_lang (cost=0.28..4.30 rows=1 width=9) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: ((char_id = gnl_char.char_id) AND (lang = 'en'::text) AND (is_actv = '1'::numeric))
  • Heap Fetches: 0
11. 234.402 234.402 ↓ 151.7 372,158 1

Index Scan using idx_prod_char_val_v02 on prod_char_val (cost=0.57..8,646.82 rows=2,453 width=26) (actual time=0.027..234.402 rows=372,158 loops=1)

  • Index Cond: ((char_id = gnl_char_lang.char_id) AND ((val)::text = 'IpGateway'::text))
12. 0.013 0.070 ↑ 1.0 77 1

Hash (cost=11.21..11.21 rows=77 width=5) (actual time=0.070..0.070 rows=77 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
13. 0.057 0.057 ↑ 1.0 77 1

Seq Scan on gnl_st charvalstatus (cost=0.00..11.21 rows=77 width=5) (actual time=0.002..0.057 rows=77 loops=1)

  • Filter: ((shrt_code)::text = ANY ('{ACTV,PNDG}'::text[]))
  • Rows Removed by Filter: 260
14. 721.020 721.020 ↑ 1.0 1 180,255

Index Scan using pk_prod on prod (cost=0.43..3.07 rows=1 width=13) (actual time=0.004..0.004 rows=1 loops=180,255)

  • Index Cond: (prod_id = prod_char_val.prod_id)
15. 180.255 180.255 ↑ 1.0 1 180,255

Index Scan using gnl_st_pkey on gnl_st prodstatus (cost=0.15..0.17 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=180,255)

  • Index Cond: (gnl_st_id = prod.st_id)
  • Filter: ((shrt_code)::text = ANY ('{ACTV,PNDG,SPND,CNCL}'::text[]))
  • Rows Removed by Filter: 0
16. 180.198 180.198 ↑ 1.0 1 180,198

Index Only Scan using gnl_char_val_pkey on gnl_char_val (cost=0.15..0.17 rows=1 width=6) (actual time=0.001..0.001 rows=1 loops=180,198)

  • Index Cond: (char_val_id = prod_char_val.char_val_id)
  • Heap Fetches: 0
17. 360.396 360.396 ↑ 1.0 1 180,198

Index Scan using idx_gcvl_char_val_id on gnl_char_val_lang (cost=0.28..0.32 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=180,198)

  • Index Cond: (char_val_id = prod_char_val.char_val_id)
  • Filter: ((is_actv = '1'::numeric) AND ((lang)::text = 'en'::text))
  • Rows Removed by Filter: 1
18. 720.677 720.792 ↓ 2.1 83 180,198

Materialize (cost=0.85..201.62 rows=39 width=5,371) (actual time=0.000..0.004 rows=83 loops=180,198)

19. 0.047 0.115 ↓ 2.1 83 1

Nested Loop (cost=0.85..201.42 rows=39 width=5,371) (actual time=0.020..0.115 rows=83 loops=1)

20. 0.007 0.007 ↑ 1.0 1 1

Index Scan using idx_cust_party_id on cust custentity0_ (cost=0.42..8.44 rows=1 width=6) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (party_id = '109367'::numeric)
21. 0.061 0.061 ↓ 1.0 83 1

Index Scan using idx_prod_cust_id on prod prodentity1_ (cost=0.43..192.17 rows=82 width=5,371) (actual time=0.012..0.061 rows=83 loops=1)

  • Index Cond: (cust_id = custentity0_.cust_id)
Planning time : 7.550 ms
Execution time : 4,798.795 ms