explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c2EZ : Optimization for: plan #O6Dq

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.012 7,367.381 ↓ 2.0 2 1

Sort (cost=70,669.79..70,669.80 rows=1 width=5,371) (actual time=7,367.380..7,367.381 rows=2 loops=1)

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

Hash Join (cost=49,772.92..70,669.78 rows=1 width=5,371) (actual time=1,356.675..7,367.369 rows=2 loops=1)

  • Hash Cond: (prod.prod_id = prodentity1_.prod_id)
3. 153.656 7,308.877 ↓ 1,845.6 461,397 1

Hash Join (cost=49,571.01..70,466.92 rows=250 width=13) (actual time=247.261..7,308.877 rows=461,397 loops=1)

  • Hash Cond: (prod.st_id = prodstatus.gnl_st_id)
4. 341.783 7,155.149 ↓ 547.5 461,519 1

Nested Loop (cost=49,557.70..70,451.37 rows=843 width=20) (actual time=247.185..7,155.149 rows=461,519 loops=1)

5. 140.215 5,428.809 ↓ 547.5 461,519 1

Hash Join (cost=49,557.27..69,096.69 rows=843 width=7) (actual time=247.172..5,428.809 rows=461,519 loops=1)

  • Hash Cond: (prod_char_val.char_val_id = gnl_char_val_lang.char_val_id)
6. 203.080 5,288.197 ↓ 530.5 461,519 1

Hash Join (cost=49,514.45..69,038.81 rows=870 width=16) (actual time=246.772..5,288.197 rows=461,519 loops=1)

  • Hash Cond: (prod_char_val.st_id = charvalstatus.gnl_st_id)
7. 133.360 5,085.060 ↓ 254.7 969,599 1

Nested Loop (cost=49,502.27..69,016.52 rows=3,807 width=23) (actual time=246.693..5,085.060 rows=969,599 loops=1)

8. 0.002 0.045 ↑ 1.0 1 1

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

9. 0.037 0.037 ↑ 1.0 1 1

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

  • Filter: ((shrt_code)::text = 'deviceType'::text)
  • Rows Removed by Filter: 268
10. 0.006 0.006 ↑ 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.006 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,710.665 4,951.655 ↓ 146.2 969,599 1

Bitmap Heap Scan on prod_char_val (cost=49,502.00..68,937.53 rows=6,632 width=26) (actual time=246.680..4,951.655 rows=969,599 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,465
  • Heap Blocks: exact=22,838 lossy=371,742
12. 9.663 240.990 ↓ 0.0 0 1

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

13. 132.254 132.254 ↑ 1.2 1,018,068 1

Bitmap Index Scan on idx_prod_char_val_v02 (cost=0.00..24,657.72 rows=1,268,830 width=0) (actual time=132.254..132.254 rows=1,018,068 loops=1)

  • Index Cond: (char_id = gnl_char_lang.char_id)
14. 0.002 99.073 ↓ 0.0 0 1

BitmapOr (cost=24,842.37..24,842.37 rows=1,029,232 width=0) (actual time=99.073..99.073 rows=0 loops=1)

15. 48.314 48.314 ↑ 1.0 373,903 1

Bitmap Index Scan on idx_prod_char_val_val (cost=0.00..8,988.26 rows=380,226 width=0) (actual time=48.314..48.314 rows=373,903 loops=1)

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

Bitmap Index Scan on idx_prod_char_val_val (cost=0.00..15,340.11 rows=649,006 width=0) (actual time=50.757..50.757 rows=600,196 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.067 0.397 ↑ 1.0 375 1

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

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

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
23. 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)

24. 1,384.557 1,384.557 ↑ 1.0 1 461,519

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

  • Index Cond: (prod_id = prod_char_val.prod_id)
25. 0.017 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
26. 0.055 0.055 ↑ 1.0 100 1

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

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

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

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

Nested Loop (cost=0.85..201.42 rows=39 width=5,371) (actual time=0.022..0.120 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.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.201 ms
Execution time : 7,369.757 ms