explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iBI9

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 7,357.715 ↓ 2.0 2 1

Sort (cost=70,667.94..70,667.94 rows=1 width=5,371) (actual time=7,357.715..7,357.715 rows=2 loops=1)

  • Sort Key: prodentity1_.prod_id DESC
  • Sort Method: quicksort Memory: 26kB
2. 59.657 7,357.704 ↓ 2.0 2 1

Hash Join (cost=49,771.05..70,667.93 rows=1 width=5,371) (actual time=1,381.182..7,357.704 rows=2 loops=1)

  • Hash Cond: (prod.prod_id = prodentity1_.prod_id)
3. 154.201 7,297.853 ↓ 1,845.5 461,387 1

Hash Join (cost=49,569.14..70,465.07 rows=250 width=13) (actual time=247.677..7,297.853 rows=461,387 loops=1)

  • Hash Cond: (prod.st_id = prodstatus.gnl_st_id)
4. 316.888 7,143.577 ↓ 547.5 461,509 1

Nested Loop (cost=49,555.83..70,449.52 rows=843 width=20) (actual time=247.598..7,143.577 rows=461,509 loops=1)

5. 138.402 5,442.162 ↓ 547.5 461,509 1

Hash Join (cost=49,555.40..69,094.81 rows=843 width=7) (actual time=247.585..5,442.162 rows=461,509 loops=1)

  • Hash Cond: (prod_char_val.char_val_id = gnl_char_val_lang.char_val_id)
6. 201.568 5,303.341 ↓ 530.5 461,509 1

Hash Join (cost=49,512.58..69,036.93 rows=870 width=16) (actual time=247.162..5,303.341 rows=461,509 loops=1)

  • Hash Cond: (prod_char_val.st_id = charvalstatus.gnl_st_id)
7. 136.019 5,101.716 ↓ 254.7 969,556 1

Nested Loop (cost=49,500.40..69,014.64 rows=3,807 width=23) (actual time=247.083..5,101.716 rows=969,556 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.012..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.005..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. 4,724.266 4,965.649 ↓ 146.2 969,556 1

Bitmap Heap Scan on prod_char_val (cost=49,500.13..68,935.65 rows=6,632 width=26) (actual time=247.069..4,965.649 rows=969,556 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,872,464
  • Heap Blocks: exact=22,805 lossy=371,743
12. 9.470 241.383 ↓ 0.0 0 1

BitmapAnd (cost=49,500.13..49,500.13 rows=6,640 width=0) (actual time=241.383..241.383 rows=0 loops=1)

13. 131.305 131.305 ↑ 1.2 1,017,966 1

Bitmap Index Scan on idx_prod_char_val_v02 (cost=0.00..24,656.10 rows=1,268,791 width=0) (actual time=131.305..131.305 rows=1,017,966 loops=1)

  • Index Cond: (char_id = gnl_char_lang.char_id)
14. 0.001 100.608 ↓ 0.0 0 1

BitmapOr (cost=24,842.12..24,842.12 rows=1,029,201 width=0) (actual time=100.608..100.608 rows=0 loops=1)

15. 49.217 49.217 ↑ 1.0 373,859 1

Bitmap Index Scan on idx_prod_char_val_val (cost=0.00..8,988.17 rows=380,214 width=0) (actual time=49.217..49.217 rows=373,859 loops=1)

  • Index Cond: ((val)::text = 'IpGateway'::text)
16. 51.390 51.390 ↑ 1.1 600,138 1

Bitmap Index Scan on idx_prod_char_val_val (cost=0.00..15,339.97 rows=648,987 width=0) (actual time=51.390..51.390 rows=600,138 loops=1)

  • Index Cond: ((val)::text = 'IpStb'::text)
17. 0.013 0.057 ↑ 1.0 77 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
18. 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.001..0.044 rows=77 loops=1)

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

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

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

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

  • Hash Cond: (gnl_char_val_lang.char_val_id = gnl_char_val.char_val_id)
21. 0.133 0.133 ↑ 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.133 rows=375 loops=1)

  • Filter: ((is_actv = '1'::numeric) AND ((lang)::text = 'en'::text))
  • Rows Removed by Filter: 387
22. 0.070 0.114 ↑ 1.0 387 1

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

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

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

24. 1,384.527 1,384.527 ↑ 1.0 1 461,509

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

  • Index Cond: (prod_id = prod_char_val.prod_id)
25. 0.017 0.075 ↑ 1.0 100 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
26. 0.058 0.058 ↑ 1.0 100 1

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
28. 0.064 0.132 ↓ 2.1 83 1

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

29. 0.008 0.008 ↑ 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.008..0.008 rows=1 loops=1)

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

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