explain.depesz.com

PostgreSQL's explain analyze made readable

Result: byGl : Optimization for: plan #KT0

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 4,662.731 11,734.572 ↓ 2.0 2 1

Nested Loop (cost=14.88..21,912.06 rows=1 width=5,371) (actual time=4,047.121..11,734.572 rows=2 loops=1)

  • Join Filter: (prodentity1_.prod_id = prod.prod_id)
  • Rows Removed by Join Filter: 38,300,431
2. 178.640 5,226.037 ↓ 1,845.8 461,451 1

Nested Loop (cost=14.03..21,564.29 rows=250 width=13) (actual time=0.127..5,226.037 rows=461,451 loops=1)

  • Join Filter: (gnl_char_val.char_val_id = gnl_char_val_lang.char_val_id)
3. 118.901 4,124.495 ↓ 1,788.6 461,451 1

Nested Loop (cost=13.75..21,478.49 rows=258 width=28) (actual time=0.122..4,124.495 rows=461,451 loops=1)

4. 229.307 3,544.143 ↓ 1,788.6 461,451 1

Nested Loop (cost=13.60..21,435.93 rows=258 width=22) (actual time=0.119..3,544.143 rows=461,451 loops=1)

5. 456.323 2,853.263 ↓ 529.9 461,573 1

Nested Loop (cost=13.46..21,287.92 rows=871 width=29) (actual time=0.116..2,853.263 rows=461,573 loops=1)

6. 205.674 1,012.221 ↓ 529.9 461,573 1

Hash Join (cost=13.02..19,889.56 rows=871 width=16) (actual time=0.110..1,012.221 rows=461,573 loops=1)

  • Hash Cond: (prod_char_val.st_id = charvalstatus.gnl_st_id)
7. 201.415 806.485 ↓ 254.3 969,774 1

Nested Loop (cost=0.85..19,867.25 rows=3,813 width=23) (actual time=0.043..806.485 rows=969,774 loops=1)

8. 0.002 0.042 ↑ 1.0 1 1

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

9. 0.033 0.033 ↑ 1.0 1 1

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

Index Scan using idx_prod_char_val_v02 on prod_char_val (cost=0.57..19,788.17 rows=6,641 width=26) (actual time=0.028..605.028 rows=969,774 loops=1)

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

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

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

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

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

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

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

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,573)

  • 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.451 461.451 ↑ 1.0 1 461,451

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,451)

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

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.001..0.002 rows=1 loops=461,451)

  • 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.673 1,845.804 ↓ 2.1 83 461,451

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

19. 0.049 0.131 ↓ 2.1 83 1

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

20. 0.006 0.006 ↑ 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.006..0.006 rows=1 loops=1)

  • Index Cond: (party_id = '109367'::numeric)
21. 0.076 0.076 ↓ 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.076 rows=83 loops=1)

  • Index Cond: (cust_id = custentity0_.cust_id)
Planning time : 7.159 ms
Execution time : 11,735.176 ms