explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O6Dq

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 61.622 7,392.689 ↓ 2.0 2 1

Hash Join (cost=49,772.78..70,669.65 rows=1 width=5,371) (actual time=1,385.411..7,392.689 rows=2 loops=1)

  • Hash Cond: (prod.prod_id = prodentity1_.prod_id)
2. 149.871 7,330.892 ↓ 1,845.6 461,395 1

Hash Join (cost=49,570.87..70,466.79 rows=250 width=13) (actual time=249.488..7,330.892 rows=461,395 loops=1)

  • Hash Cond: (prod.st_id = prodstatus.gnl_st_id)
3. 307.866 7,180.949 ↓ 547.5 461,517 1

Nested Loop (cost=49,557.56..70,451.24 rows=843 width=20) (actual time=249.410..7,180.949 rows=461,517 loops=1)

4. 142.719 5,488.532 ↓ 547.5 461,517 1

Hash Join (cost=49,557.13..69,096.55 rows=843 width=7) (actual time=249.397..5,488.532 rows=461,517 loops=1)

  • Hash Cond: (prod_char_val.char_val_id = gnl_char_val_lang.char_val_id)
5. 204.249 5,345.414 ↓ 530.5 461,517 1

Hash Join (cost=49,514.31..69,038.67 rows=870 width=16) (actual time=248.994..5,345.414 rows=461,517 loops=1)

  • Hash Cond: (prod_char_val.st_id = charvalstatus.gnl_st_id)
6. 131.128 5,141.109 ↓ 254.7 969,592 1

Nested Loop (cost=49,502.13..69,016.38 rows=3,807 width=23) (actual time=248.914..5,141.109 rows=969,592 loops=1)

7. 0.001 0.039 ↑ 1.0 1 1

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

8. 0.031 0.031 ↑ 1.0 1 1

Seq Scan on gnl_char (cost=0.00..8.36 rows=1 width=6) (actual time=0.005..0.031 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. 4,766.541 5,009.942 ↓ 146.2 969,592 1

Bitmap Heap Scan on prod_char_val (cost=49,501.86..68,937.39 rows=6,632 width=26) (actual time=248.900..5,009.942 rows=969,592 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,871,737
  • Heap Blocks: exact=22,833 lossy=371,742
11. 9.407 243.401 ↓ 0.0 0 1

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

12. 135.961 135.961 ↑ 1.2 1,018,050 1

Bitmap Index Scan on idx_prod_char_val_v02 (cost=0.00..24,657.63 rows=1,268,822 width=0) (actual time=135.961..135.961 rows=1,018,050 loops=1)

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

BitmapOr (cost=24,842.32..24,842.32 rows=1,029,226 width=0) (actual time=98.033..98.033 rows=0 loops=1)

14. 48.872 48.872 ↑ 1.0 373,894 1

Bitmap Index Scan on idx_prod_char_val_val (cost=0.00..8,988.25 rows=380,224 width=0) (actual time=48.872..48.872 rows=373,894 loops=1)

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

Bitmap Index Scan on idx_prod_char_val_val (cost=0.00..15,340.08 rows=649,002 width=0) (actual time=49.159..49.159 rows=600,187 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.001..0.044 rows=77 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
19. 0.104 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.132 0.132 ↑ 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.132 rows=375 loops=1)

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

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

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

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

23. 1,384.551 1,384.551 ↑ 1.0 1 461,517

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

  • 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.003..0.056 rows=100 loops=1)

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

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

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

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

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