explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i7Re : v5

Settings
# exclusive inclusive rows x rows loops node
1. 0.403 115.166 ↓ 39.0 156 1

Sort (cost=62,488.40..62,488.41 rows=4 width=290) (actual time=115.160..115.166 rows=156 loops=1)

  • Sort Key: fi.facility_code_, product.product_code_, identifer.frame_no_
  • Sort Method: quicksort Memory: 66kB
2. 0.185 114.763 ↓ 39.0 156 1

Nested Loop Left Join (cost=10,074.33..62,488.36 rows=4 width=290) (actual time=39.283..114.763 rows=156 loops=1)

3. 0.095 114.422 ↓ 39.0 156 1

Nested Loop Left Join (cost=10,074.19..62,487.68 rows=4 width=229) (actual time=39.224..114.422 rows=156 loops=1)

4. 0.021 114.327 ↓ 39.0 156 1

Nested Loop Left Join (cost=10,073.91..62,486.33 rows=4 width=242) (actual time=39.222..114.327 rows=156 loops=1)

5. 0.091 113.994 ↓ 39.0 156 1

Nested Loop Left Join (cost=10,073.50..62,484.19 rows=4 width=253) (actual time=39.213..113.994 rows=156 loops=1)

6. 0.027 113.903 ↓ 39.0 156 1

Nested Loop Left Join (cost=10,073.22..62,482.83 rows=4 width=266) (actual time=39.211..113.903 rows=156 loops=1)

7. 0.095 113.564 ↓ 39.0 156 1

Nested Loop Left Join (cost=10,072.81..62,480.68 rows=4 width=240) (actual time=39.199..113.564 rows=156 loops=1)

8. 0.146 113.469 ↓ 39.0 156 1

Nested Loop Left Join (cost=10,072.53..62,479.33 rows=4 width=253) (actual time=39.196..113.469 rows=156 loops=1)

9. 0.076 112.855 ↓ 39.0 156 1

Nested Loop (cost=10,072.12..62,477.19 rows=4 width=227) (actual time=39.172..112.855 rows=156 loops=1)

10. 0.168 112.467 ↓ 39.0 156 1

Nested Loop (cost=10,071.85..62,475.99 rows=4 width=242) (actual time=39.153..112.467 rows=156 loops=1)

11. 0.213 111.675 ↓ 2.0 156 1

Nested Loop (cost=10,071.43..62,437.92 rows=77 width=169) (actual time=39.104..111.675 rows=156 loops=1)

  • Join Filter: ((spsi.serialized_product_id_)::text = (spi.serialized_product_id_)::text)
12. 25.255 109.434 ↑ 3.5 156 1

Hash Join (cost=10,071.00..61,752.66 rows=542 width=141) (actual time=39.055..109.434 rows=156 loops=1)

  • Hash Cond: ((identifer.serialized_product_id_)::text = (spsi.serialized_product_id_)::text)
13. 46.535 62.899 ↓ 1.0 151,655 1

Bitmap Heap Scan on serialized_product_identifier identifer (cost=1,997.28..52,544.31 rows=150,562 width=104) (actual time=17.402..62.899 rows=151,655 loops=1)

  • Recheck Cond: ((site_id_)::text = 'VA2301'::text)
  • Heap Blocks: exact=6449
14. 16.364 16.364 ↓ 1.0 151,655 1

Bitmap Index Scan on seria_pro_ide_idx99 (cost=0.00..1,959.64 rows=150,562 width=0) (actual time=16.364..16.364 rows=151,655 loops=1)

  • Index Cond: ((site_id_)::text = 'VA2301'::text)
15. 0.107 21.280 ↑ 24.8 156 1

Hash (cost=8,025.32..8,025.32 rows=3,872 width=37) (actual time=21.280..21.280 rows=156 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
16. 21.173 21.173 ↑ 24.8 156 1

Index Only Scan using serialized_product_status_info_idx02 on serialized_product_status_info spsi (cost=0.55..8,025.32 rows=3,872 width=37) (actual time=0.116..21.173 rows=156 loops=1)

  • Index Cond: ((site_id_ = 'VA2301'::text) AND (serialized_product_status_id_ = 'C047ONHAND'::text))
  • Heap Fetches: 156
17. 2.028 2.028 ↑ 1.0 1 156

Index Scan using pk_serialized_product_info on serialized_product_info spi (cost=0.43..1.25 rows=1 width=139) (actual time=0.013..0.013 rows=1 loops=156)

  • Index Cond: ((serialized_product_id_)::text = (identifer.serialized_product_id_)::text)
  • Filter: ((site_id_)::text = 'VA2301'::text)
18. 0.624 0.624 ↑ 1.0 1 156

Index Scan using pk_product on product (cost=0.41..0.48 rows=1 width=110) (actual time=0.004..0.004 rows=1 loops=156)

  • Index Cond: ((product_id_)::text = (spi.product_id_)::text)
  • Filter: ((product_classification_id_)::text = 'C059GOODS'::text)
19. 0.312 0.312 ↑ 1.0 1 156

Index Scan using pk_facility_info on facility_info fi (cost=0.27..0.29 rows=1 width=59) (actual time=0.002..0.002 rows=1 loops=156)

  • Index Cond: ((facility_id_)::text = (spi.facility_id_)::text)
20. 0.468 0.468 ↓ 0.0 0 156

Index Only Scan using product_category_info_idx01 on product_category_info mktpci1 (cost=0.42..0.53 rows=1 width=63) (actual time=0.003..0.003 rows=0 loops=156)

  • Index Cond: ((product_id_ = (spi.product_id_)::text) AND (product_category_type_id_ = 'MKTCATEGORY1'::text))
  • Heap Fetches: 0
21. 0.000 0.000 ↓ 0.0 0 156

Index Scan using pk_product_category on product_category_setting pcs1 (cost=0.27..0.33 rows=1 width=45) (actual time=0.000..0.000 rows=0 loops=156)

  • Index Cond: ((product_category_id_)::text = (mktpci1.product_category_id_)::text)
  • Filter: ((product_category_type_id_)::text = 'MKTCATEGORY1'::text)
22. 0.312 0.312 ↓ 0.0 0 156

Index Only Scan using product_category_info_idx01 on product_category_info mktpci2 (cost=0.42..0.53 rows=1 width=63) (actual time=0.002..0.002 rows=0 loops=156)

  • Index Cond: ((product_id_ = (spi.product_id_)::text) AND (product_category_type_id_ = 'MKTCATEGORY2'::text))
  • Heap Fetches: 0
23. 0.000 0.000 ↓ 0.0 0 156

Index Scan using pk_product_category on product_category_setting pcs2 (cost=0.27..0.33 rows=1 width=45) (actual time=0.000..0.000 rows=0 loops=156)

  • Index Cond: ((product_category_id_)::text = (mktpci2.product_category_id_)::text)
  • Filter: ((product_category_type_id_)::text = 'MKTCATEGORY2'::text)
24. 0.312 0.312 ↓ 0.0 0 156

Index Only Scan using product_category_info_idx01 on product_category_info mktpci3 (cost=0.42..0.53 rows=1 width=63) (actual time=0.002..0.002 rows=0 loops=156)

  • Index Cond: ((product_id_ = (spi.product_id_)::text) AND (product_category_type_id_ = 'MKTCATEGORY3'::text))
  • Heap Fetches: 0
25. 0.000 0.000 ↓ 0.0 0 156

Index Scan using pk_product_category on product_category_setting pcs3 (cost=0.27..0.33 rows=1 width=45) (actual time=0.000..0.000 rows=0 loops=156)

  • Index Cond: ((product_category_id_)::text = (mktpci3.product_category_id_)::text)
  • Filter: ((product_category_type_id_)::text = 'MKTCATEGORY3'::text)
26. 0.156 0.156 ↑ 1.0 1 156

Index Scan using pk_color_info on color_info color (cost=0.14..0.16 rows=1 width=196) (actual time=0.001..0.001 rows=1 loops=156)

  • Index Cond: ((color_id_)::text = (product.color_id_)::text)
Planning time : 9.587 ms
Execution time : 115.625 ms