explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ipt9 : V6

Settings
# exclusive inclusive rows x rows loops node
1. 21.953 691.718 ↓ 276.1 7,178 1

Sort (cost=33,680.41..33,680.48 rows=26 width=295) (actual time=691.226..691.718 rows=7,178 loops=1)

  • Sort Key: fi.facility_code_, product.product_code_, identifer.frame_no_
  • Sort Method: quicksort Memory: 2099kB
2. 40.183 669.765 ↓ 276.1 7,178 1

Nested Loop Left Join (cost=2.95..33,679.80 rows=26 width=295) (actual time=387.999..669.765 rows=7,178 loops=1)

  • Join Filter: ((color.color_id_)::text = (product.color_id_)::text)
  • Rows Removed by Join Filter: 315832
3. 69.725 615.226 ↓ 276.1 7,178 1

Nested Loop Left Join (cost=2.95..33,629.55 rows=26 width=234) (actual time=387.982..615.226 rows=7,178 loops=1)

  • Join Filter: ((pcs3.product_category_id_)::text = (mktpci3.product_category_id_)::text)
  • Rows Removed by Join Filter: 588596
4. 3.372 516.789 ↓ 276.1 7,178 1

Nested Loop Left Join (cost=2.95..33,579.62 rows=26 width=247) (actual time=387.927..516.789 rows=7,178 loops=1)

5. 27.823 499.061 ↓ 276.1 7,178 1

Nested Loop Left Join (cost=2.53..33,565.82 rows=26 width=258) (actual time=387.919..499.061 rows=7,178 loops=1)

  • Join Filter: ((pcs2.product_category_id_)::text = (mktpci2.product_category_id_)::text)
  • Rows Removed by Join Filter: 186628
6. 3.679 464.060 ↓ 276.1 7,178 1

Nested Loop Left Join (cost=2.53..33,539.05 rows=26 width=271) (actual time=387.871..464.060 rows=7,178 loops=1)

7. 6.988 446.025 ↓ 276.1 7,178 1

Nested Loop Left Join (cost=2.11..33,525.25 rows=26 width=245) (actual time=387.861..446.025 rows=7,178 loops=1)

  • Join Filter: ((pcs1.product_category_id_)::text = (mktpci1.product_category_id_)::text)
  • Rows Removed by Join Filter: 57424
8. 0.000 431.859 ↓ 276.1 7,178 1

Nested Loop Left Join (cost=2.11..33,506.33 rows=26 width=258) (actual time=387.786..431.859 rows=7,178 loops=1)

9. 43.267 410.600 ↓ 276.1 7,178 1

Nested Loop (cost=1.69..33,492.53 rows=26 width=232) (actual time=387.749..410.600 rows=7,178 loops=1)

  • Join Filter: ((spi.facility_id_)::text = (fi.facility_id_)::text)
  • Rows Removed by Join Filter: 380434
10. 0.025 0.025 ↑ 1.4 54 1

Seq Scan on facility_info fi (cost=0.00..2.78 rows=78 width=59) (actual time=0.006..0.025 rows=54 loops=1)

11. 28.224 367.308 ↓ 276.1 7,178 54

Materialize (cost=1.69..33,459.40 rows=26 width=247) (actual time=0.003..6.802 rows=7,178 loops=54)

12. 7.187 339.084 ↓ 276.1 7,178 1

Nested Loop (cost=1.69..33,459.27 rows=26 width=247) (actual time=0.167..339.084 rows=7,178 loops=1)

  • Join Filter: ((spsi.serialized_product_id_)::text = (identifer.serialized_product_id_)::text)
13. 3.207 202.693 ↓ 83.5 7,178 1

Nested Loop (cost=1.27..33,385.59 rows=86 width=251) (actual time=0.141..202.693 rows=7,178 loops=1)

14. 4.791 177.952 ↓ 3.9 7,178 1

Nested Loop (cost=0.85..32,465.12 rows=1,824 width=178) (actual time=0.124..177.952 rows=7,178 loops=1)

15. 65.491 65.491 ↓ 1.2 7,178 1

Index Only Scan using serialized_product_status_info_idx02 on serialized_product_status_info spsi (cost=0.42..10,936.01 rows=6,162 width=36) (actual time=0.087..65.491 rows=7,178 loops=1)

  • Index Cond: ((site_id_ = 'VA2301'::text) AND (serialized_product_status_id_ = 'C047ONHAND'::text))
  • Heap Fetches: 7178
16. 107.670 107.670 ↑ 1.0 1 7,178

Index Scan using pk_serialized_product_info on serialized_product_info spi (cost=0.42..3.48 rows=1 width=142) (actual time=0.015..0.015 rows=1 loops=7,178)

  • Index Cond: ((serialized_product_id_)::text = (spsi.serialized_product_id_)::text)
  • Filter: ((site_id_)::text = 'VA2301'::text)
17. 21.534 21.534 ↑ 1.0 1 7,178

Index Scan using pk_product on product (cost=0.42..0.49 rows=1 width=110) (actual time=0.003..0.003 rows=1 loops=7,178)

  • Index Cond: ((product_id_)::text = (spi.product_id_)::text)
  • Filter: ((product_classification_id_)::text = 'C059GOODS'::text)
18. 129.204 129.204 ↑ 1.0 1 7,178

Index Scan using seria_pro_ide_idx03 on serialized_product_identifier identifer (cost=0.42..0.84 rows=1 width=104) (actual time=0.018..0.018 rows=1 loops=7,178)

  • Index Cond: ((serialized_product_id_)::text = (spi.serialized_product_id_)::text)
  • Filter: ((site_id_)::text = 'VA2301'::text)
19. 21.534 21.534 ↑ 1.0 1 7,178

Index Only Scan using product_category_info_idx01 on product_category_info mktpci1 (cost=0.42..0.52 rows=1 width=63) (actual time=0.002..0.003 rows=1 loops=7,178)

  • Index Cond: ((product_id_ = (spi.product_id_)::text) AND (product_category_type_id_ = 'MKTCATEGORY1'::text))
  • Heap Fetches: 7178
20. 7.092 7.178 ↑ 1.1 9 7,178

Materialize (cost=0.00..15.05 rows=10 width=45) (actual time=0.000..0.001 rows=9 loops=7,178)

21. 0.086 0.086 ↑ 1.1 9 1

Seq Scan on product_category_setting pcs1 (cost=0.00..15.00 rows=10 width=45) (actual time=0.064..0.086 rows=9 loops=1)

  • Filter: ((product_category_type_id_)::text = 'MKTCATEGORY1'::text)
  • Rows Removed by Filter: 364
22. 14.356 14.356 ↑ 1.0 1 7,178

Index Only Scan using product_category_info_idx01 on product_category_info mktpci2 (cost=0.42..0.52 rows=1 width=63) (actual time=0.002..0.002 rows=1 loops=7,178)

  • Index Cond: ((product_id_ = (spi.product_id_)::text) AND (product_category_type_id_ = 'MKTCATEGORY2'::text))
  • Heap Fetches: 7178
23. 7.113 7.178 ↑ 1.1 27 7,178

Materialize (cost=0.00..15.15 rows=30 width=45) (actual time=0.000..0.001 rows=27 loops=7,178)

24. 0.065 0.065 ↑ 1.1 27 1

Seq Scan on product_category_setting pcs2 (cost=0.00..15.00 rows=30 width=45) (actual time=0.039..0.065 rows=27 loops=1)

  • Filter: ((product_category_type_id_)::text = 'MKTCATEGORY2'::text)
  • Rows Removed by Filter: 346
25. 14.356 14.356 ↑ 1.0 1 7,178

Index Only Scan using product_category_info_idx01 on product_category_info mktpci3 (cost=0.42..0.52 rows=1 width=63) (actual time=0.002..0.002 rows=1 loops=7,178)

  • Index Cond: ((product_id_ = (spi.product_id_)::text) AND (product_category_type_id_ = 'MKTCATEGORY3'::text))
  • Heap Fetches: 7178
26. 28.634 28.712 ↑ 1.1 83 7,178

Materialize (cost=0.00..15.45 rows=89 width=45) (actual time=0.000..0.004 rows=83 loops=7,178)

27. 0.078 0.078 ↑ 1.1 83 1

Seq Scan on product_category_setting pcs3 (cost=0.00..15.00 rows=89 width=45) (actual time=0.045..0.078 rows=83 loops=1)

  • Filter: ((product_category_type_id_)::text = 'MKTCATEGORY3'::text)
  • Rows Removed by Filter: 290
28. 14.346 14.356 ↑ 2.2 45 7,178

Materialize (cost=0.00..11.50 rows=100 width=196) (actual time=0.000..0.002 rows=45 loops=7,178)

29. 0.010 0.010 ↑ 2.2 45 1

Seq Scan on color_info color (cost=0.00..11.00 rows=100 width=196) (actual time=0.004..0.010 rows=45 loops=1)

Planning time : 5.458 ms
Execution time : 692.351 ms