explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JVPDz : Optimization for: Optimization for: plan #O6Dq; plan #cjsx

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 5,514.304 13,402.009 ↓ 2.0 2 1

Nested Loop (cost=14.88..21,911.86 rows=1 width=5,371) (actual time=4,160.617..13,402.009 rows=2 loops=1)

  • Join Filter: (prodentity1_.prod_id = prod.prod_id)
  • Rows Removed by Join Filter: 38,299,269
2. 358.807 6,041.957 ↓ 1,845.7 461,437 1

Nested Loop (cost=14.03..21,564.09 rows=250 width=13) (actual time=0.137..6,041.957 rows=461,437 loops=1)

  • Join Filter: (gnl_char_val.char_val_id = gnl_char_val_lang.char_val_id)
3. 187.933 4,760.276 ↓ 1,788.5 461,437 1

Nested Loop (cost=13.75..21,478.29 rows=258 width=28) (actual time=0.132..4,760.276 rows=461,437 loops=1)

4. 328.112 4,110.906 ↓ 1,788.5 461,437 1

Nested Loop (cost=13.60..21,435.74 rows=258 width=22) (actual time=0.129..4,110.906 rows=461,437 loops=1)

5. 279.426 3,321.235 ↓ 529.9 461,559 1

Nested Loop (cost=13.46..21,287.73 rows=871 width=29) (actual time=0.126..3,321.235 rows=461,559 loops=1)

6. 234.693 1,195.573 ↓ 529.9 461,559 1

Hash Join (cost=13.02..19,889.33 rows=871 width=16) (actual time=0.121..1,195.573 rows=461,559 loops=1)

  • Hash Cond: (prod_char_val.st_id = charvalstatus.gnl_st_id)
7. 218.605 960.819 ↓ 254.4 969,723 1

Nested Loop (cost=0.85..19,867.03 rows=3,812 width=23) (actual time=0.056..960.819 rows=969,723 loops=1)

8. 0.001 0.051 ↑ 1.0 1 1

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

9. 0.043 0.043 ↑ 1.0 1 1

Seq Scan on gnl_char (cost=0.00..8.36 rows=1 width=6) (actual time=0.007..0.043 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. 742.163 742.163 ↓ 146.0 969,723 1

Index Scan using idx_prod_char_val_v02 on prod_char_val (cost=0.57..19,787.95 rows=6,641 width=26) (actual time=0.040..742.163 rows=969,723 loops=1)

  • Index Cond: ((char_id = gnl_char_lang.char_id) AND ((val)::text = ANY ('{IpGateway,IpStb}'::text[])))
12. 0.014 0.061 ↑ 1.0 77 1

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

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

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

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

Index Scan using pk_prod on prod (cost=0.43..1.61 rows=1 width=13) (actual time=0.004..0.004 rows=1 loops=461,559)

  • Index Cond: (prod_id = prod_char_val.prod_id)
15. 461.559 461.559 ↑ 1.0 1 461,559

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=461,559)

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

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=461,437)

  • Index Cond: (char_val_id = prod_char_val.char_val_id)
  • Heap Fetches: 0
17. 922.874 922.874 ↑ 1.0 1 461,437

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=461,437)

  • 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. 1,845.629 1,845.748 ↓ 2.1 83 461,437

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

19. 0.047 0.119 ↓ 2.1 83 1

Nested Loop (cost=0.85..201.42 rows=39 width=5,371) (actual time=0.021..0.119 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.065 0.065 ↓ 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.065 rows=83 loops=1)

  • Index Cond: (cust_id = custentity0_.cust_id)
Planning time : 7.243 ms
Execution time : 13,402.595 ms