explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KT0

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 62.481 7,738.627 ↓ 2.0 2 1

Hash Join (cost=49,775.27..70,675.95 rows=1 width=5,371) (actual time=1,405.940..7,738.627 rows=2 loops=1)

  • Hash Cond: (prod.prod_id = prodentity1_.prod_id)
2. 156.470 7,675.968 ↓ 1,845.8 461,445 1

Hash Join (cost=49,573.36..70,473.09 rows=250 width=13) (actual time=235.045..7,675.968 rows=461,445 loops=1)

  • Hash Cond: (prod.st_id = prodstatus.gnl_st_id)
3. 339.273 7,519.426 ↓ 547.5 461,567 1

Nested Loop (cost=49,560.05..70,457.54 rows=843 width=20) (actual time=234.968..7,519.426 rows=461,567 loops=1)

4. 143.122 5,795.452 ↓ 547.5 461,567 1

Hash Join (cost=49,559.62..69,102.91 rows=843 width=7) (actual time=234.955..5,795.452 rows=461,567 loops=1)

  • Hash Cond: (prod_char_val.char_val_id = gnl_char_val_lang.char_val_id)
5. 210.847 5,651.934 ↓ 530.5 461,567 1

Hash Join (cost=49,516.80..69,045.02 rows=870 width=16) (actual time=234.555..5,651.934 rows=461,567 loops=1)

  • Hash Cond: (prod_char_val.st_id = charvalstatus.gnl_st_id)
6. 138.819 5,441.031 ↓ 254.7 969,732 1

Nested Loop (cost=49,504.62..69,022.73 rows=3,808 width=23) (actual time=234.475..5,441.031 rows=969,732 loops=1)

7. 0.001 0.044 ↑ 1.0 1 1

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

8. 0.036 0.036 ↑ 1.0 1 1

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

  • Filter: ((shrt_code)::text = 'deviceType'::text)
  • Rows Removed by Filter: 268
9. 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.005..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
10. 5,073.252 5,302.168 ↓ 146.2 969,732 1

Bitmap Heap Scan on prod_char_val (cost=49,504.35..68,943.73 rows=6,633 width=26) (actual time=234.462..5,302.168 rows=969,732 loops=1)

  • Recheck Cond: ((char_id = gnl_char_lang.char_id) AND (((val)::text = 'IpGateway'::text) OR ((val)::text = 'IpStb'::text)))
  • Rows Removed by Index Recheck: 24,892,012
  • Heap Blocks: exact=21,969 lossy=371,271
11. 9.874 228.916 ↓ 0.0 0 1

BitmapAnd (cost=49,504.35..49,504.35 rows=6,641 width=0) (actual time=228.916..228.916 rows=0 loops=1)

12. 125.147 125.147 ↑ 1.3 1,013,717 1

Bitmap Index Scan on idx_prod_char_val_v02 (cost=0.00..24,659.34 rows=1,268,942 width=0) (actual time=125.147..125.147 rows=1,013,717 loops=1)

  • Index Cond: (char_id = gnl_char_lang.char_id)
13. 0.002 93.895 ↓ 0.0 0 1

BitmapOr (cost=24,843.10..24,843.10 rows=1,029,323 width=0) (actual time=93.895..93.895 rows=0 loops=1)

14. 46.871 46.871 ↑ 1.0 374,031 1

Bitmap Index Scan on idx_prod_char_val_val (cost=0.00..8,988.52 rows=380,260 width=0) (actual time=46.871..46.871 rows=374,031 loops=1)

  • Index Cond: ((val)::text = 'IpGateway'::text)
15. 47.022 47.022 ↑ 1.1 600,344 1

Bitmap Index Scan on idx_prod_char_val_val (cost=0.00..15,340.55 rows=649,064 width=0) (actual time=47.022..47.022 rows=600,344 loops=1)

  • Index Cond: ((val)::text = 'IpStb'::text)
16. 0.012 0.056 ↑ 1.0 77 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
17. 0.044 0.044 ↑ 1.0 77 1

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

  • Filter: ((shrt_code)::text = ANY ('{ACTV,PNDG}'::text[]))
  • Rows Removed by Filter: 260
18. 0.064 0.396 ↑ 1.0 375 1

Hash (cost=38.13..38.13 rows=375 width=15) (actual time=0.396..0.396 rows=375 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
19. 0.106 0.332 ↑ 1.0 375 1

Hash Join (cost=15.71..38.13 rows=375 width=15) (actual time=0.101..0.332 rows=375 loops=1)

  • Hash Cond: (gnl_char_val_lang.char_val_id = gnl_char_val.char_val_id)
20. 0.131 0.131 ↑ 1.0 375 1

Seq Scan on gnl_char_val_lang (cost=0.00..21.43 rows=375 width=9) (actual time=0.002..0.131 rows=375 loops=1)

  • Filter: ((is_actv = '1'::numeric) AND ((lang)::text = 'en'::text))
  • Rows Removed by Filter: 387
21. 0.053 0.095 ↑ 1.0 387 1

Hash (cost=10.87..10.87 rows=387 width=6) (actual time=0.095..0.095 rows=387 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
22. 0.042 0.042 ↑ 1.0 387 1

Seq Scan on gnl_char_val (cost=0.00..10.87 rows=387 width=6) (actual time=0.001..0.042 rows=387 loops=1)

23. 1,384.701 1,384.701 ↑ 1.0 1 461,567

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

  • Index Cond: (prod_id = prod_char_val.prod_id)
24. 0.016 0.072 ↑ 1.0 100 1

Hash (cost=12.05..12.05 rows=100 width=5) (actual time=0.072..0.072 rows=100 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
25. 0.056 0.056 ↑ 1.0 100 1

Seq Scan on gnl_st prodstatus (cost=0.00..12.05 rows=100 width=5) (actual time=0.004..0.056 rows=100 loops=1)

  • Filter: ((shrt_code)::text = ANY ('{ACTV,PNDG,SPND,CNCL}'::text[]))
  • Rows Removed by Filter: 237
26. 0.058 0.178 ↓ 2.1 83 1

Hash (cost=201.42..201.42 rows=39 width=5,371) (actual time=0.178..0.178 rows=83 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
27. 0.051 0.120 ↓ 2.1 83 1

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

28. 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)
29. 0.062 0.062 ↓ 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.062 rows=83 loops=1)

  • Index Cond: (cust_id = custentity0_.cust_id)
Planning time : 7.241 ms
Execution time : 7,741.430 ms