explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2hGA

Settings
# exclusive inclusive rows x rows loops node
1. 324.555 18,654.832 ↓ 93,479.0 93,479 1

GroupAggregate (cost=64.05..64.10 rows=1 width=178) (actual time=18,046.984..18,654.832 rows=93,479 loops=1)

  • Group Key: commission.c_id, ((commission.c_cpt_date + critical_pull_time.cpt_time)), commission_sla_config.csc_sla_config_id, commission_config.cc_type
  • Filter: (sum(CASE WHEN ((commission_item_disposition_info.cii_commission_item_id IS NOT NULL) AND ((fixed_location.fl_type <> 'SHELF'::zal_data.fixed_location_type) OR (fixed_location.fl_type IS NULL))) THEN 1 ELSE 0 END) < 1)
2.          

Initplan (forGroupAggregate)

3. 236.885 236.885 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=32) (actual time=236.885..236.885 rows=1 loops=1)

4. 1,173.223 18,093.392 ↓ 505,505.0 505,505 1

Sort (cost=63.79..63.79 rows=1 width=300) (actual time=18,046.923..18,093.392 rows=505,505 loops=1)

  • Sort Key: commission.c_id, ((commission.c_cpt_date + critical_pull_time.cpt_time)), commission_sla_config.csc_sla_config_id, commission_config.cc_type
  • Sort Method: quicksort Memory: 272833kB
5. 17.475 16,920.169 ↓ 505,505.0 505,505 1

Nested Loop Left Join (cost=2.85..63.78 rows=1 width=300) (actual time=238.475..16,920.169 rows=505,505 loops=1)

6. 2,488.710 15,891.684 ↓ 505,505.0 505,505 1

Nested Loop Left Join (cost=2.42..57.32 rows=1 width=284) (actual time=238.450..15,891.684 rows=505,505 loops=1)

  • Join Filter: (commission_config.cc_id = commission.c_commission_config_id)
  • Rows Removed by Join Filter: 34374340
7. 0.000 11,380.954 ↓ 505,505.0 505,505 1

Nested Loop Left Join (cost=2.42..54.76 rows=1 width=280) (actual time=238.435..11,380.954 rows=505,505 loops=1)

8. 451.610 8,875.058 ↓ 505,505.0 505,505 1

Nested Loop Left Join (cost=1.99..53.47 rows=1 width=280) (actual time=238.424..8,875.058 rows=505,505 loops=1)

9. 495.244 2,862.893 ↓ 505,505.0 505,505 1

Nested Loop Left Join (cost=1.56..52.99 rows=1 width=276) (actual time=238.401..2,862.893 rows=505,505 loops=1)

  • Filter: ((commission_item.ci_status = ANY ('{NOT_DISPOSED,CANCELLED}'::zal_data.commission_item_status[])) OR (commission_item_disposition_info.cii_fixed_location_barcode IS NOT NULL))
10. 220.143 1,862.144 ↓ 505,505.0 505,505 1

Nested Loop (cost=1.12..51.86 rows=1 width=203) (actual time=238.390..1,862.144 rows=505,505 loops=1)

11. 170.199 1,136.496 ↓ 505,505.0 505,505 1

Nested Loop (cost=0.85..45.56 rows=1 width=203) (actual time=238.381..1,136.496 rows=505,505 loops=1)

12. 498.902 498.902 ↓ 93,479.0 93,479 1

Index Scan using commission_c_id_idx2 on commission (cost=0.42..37.28 rows=1 width=138) (actual time=238.365..498.902 rows=93,479 loops=1)

  • Index Cond: (c_id = ANY ($0))
13. 467.395 467.395 ↓ 2.5 5 93,479

Index Scan using commission_item_ci_commission_id_idx on commission_item (cost=0.43..8.26 rows=2 width=73) (actual time=0.003..0.005 rows=5 loops=93,479)

  • Index Cond: (ci_commission_id = commission.c_id)
  • Filter: (ci_status = ANY ('{DISPOSED,NOT_DISPOSED,CANCELLED}'::zal_data.commission_item_status[]))
14. 505.505 505.505 ↑ 1.0 1 505,505

Index Scan using critical_pull_time_pkey on critical_pull_time (cost=0.27..6.29 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=505,505)

  • Index Cond: (cpt_id = commission.c_cpt_id)
15. 505.505 505.505 ↑ 1.0 1 505,505

Index Scan using commission_item_disposition_info_pkey on commission_item_disposition_info (cost=0.43..1.11 rows=1 width=73) (actual time=0.001..0.001 rows=1 loops=505,505)

  • Index Cond: (cii_commission_item_id = commission_item.ci_id)
16. 5,560.555 5,560.555 ↑ 1.0 1 505,505

Index Scan using fixed_location_fl_barcode_key on fixed_location (cost=0.43..0.47 rows=1 width=36) (actual time=0.011..0.011 rows=1 loops=505,505)

  • Index Cond: (commission_item_disposition_info.cii_fixed_location_barcode = fl_barcode)
17. 2,527.525 2,527.525 ↑ 1.0 1 505,505

Index Scan using v_article_volume_information_article_simple_id_idx on v_article_volume_information (cost=0.44..1.29 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=505,505)

  • Index Cond: (avi_article_simple_id = commission_item.ci_article_simple_id)
18. 2,022.020 2,022.020 ↑ 1.0 69 505,505

Seq Scan on commission_config (cost=0.00..1.69 rows=69 width=8) (actual time=0.001..0.004 rows=69 loops=505,505)

19. 1,011.010 1,011.010 ↑ 1.0 1 505,505

Index Scan using commission_sla_config_csc_commission_id_key on commission_sla_config (cost=0.43..6.45 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=505,505)

  • Index Cond: (csc_commission_id = commission.c_id)