explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YIY8 : 2

Settings
# exclusive inclusive rows x rows loops node
1. 156.762 866.306 ↓ 1.1 61,568 1

Sort (cost=536,840.14..536,984.69 rows=57,819 width=133) (actual time=850.604..866.306 rows=61,568 loops=1)

  • Sort Key: (COALESCE(pr.product_code_, ''::character varying))
  • Sort Method: external merge Disk: 7088kB
2. 134.719 709.544 ↓ 1.1 61,568 1

Hash Left Join (cost=23,428.93..529,441.87 rows=57,819 width=133) (actual time=316.138..709.544 rows=61,568 loops=1)

  • Hash Cond: ((pr.product_id_)::text = (prabc.product_id_)::text)
3. 89.139 451.669 ↓ 1.1 61,568 1

Hash Join (cost=23,422.26..32,986.80 rows=57,819 width=131) (actual time=316.068..451.669 rows=61,568 loops=1)

  • Hash Cond: ((pcilarge.product_id_)::text = (pr.product_id_)::text)
4. 46.629 46.629 ↓ 1.0 61,568 1

Seq Scan on product_category_info pcilarge (cost=0.00..5,816.21 rows=61,562 width=63) (actual time=0.135..46.629 rows=61,568 loops=1)

  • Filter: ((product_category_type_id_)::text = 'PARTLARGEGROUP'::text)
  • Rows Removed by Filter: 64929
5. 31.184 315.901 ↓ 1.1 61,568 1

Hash (cost=21,504.19..21,504.19 rows=58,085 width=142) (actual time=315.901..315.901 rows=61,568 loops=1)

  • Buckets: 4096 Batches: 4 Memory Usage: 2668kB
6. 74.976 284.717 ↓ 1.1 61,568 1

Hash Join (cost=12,223.19..21,504.19 rows=58,085 width=142) (actual time=160.540..284.717 rows=61,568 loops=1)

  • Hash Cond: ((pcimiddle.product_id_)::text = (pr.product_id_)::text)
7. 49.305 49.305 ↓ 1.0 61,568 1

Seq Scan on product_category_info pcimiddle (cost=0.00..5,816.21 rows=61,355 width=63) (actual time=0.074..49.305 rows=61,568 loops=1)

  • Filter: ((product_category_type_id_)::text = 'PARTMIDDLEGROUP'::text)
  • Rows Removed by Filter: 64929
8. 24.038 160.436 ↑ 1.0 61,568 1

Hash (cost=10,642.22..10,642.22 rows=62,718 width=79) (actual time=160.436..160.436 rows=61,568 loops=1)

  • Buckets: 4096 Batches: 2 Memory Usage: 3427kB
9. 64.717 136.398 ↑ 1.0 61,568 1

Hash Left Join (cost=3,678.70..10,642.22 rows=62,718 width=79) (actual time=34.677..136.398 rows=61,568 loops=1)

  • Hash Cond: ((pr.product_id_)::text = (ypr.product_id_)::text)
10. 37.644 37.644 ↑ 1.0 61,568 1

Seq Scan on product pr (cost=0.00..3,736.74 rows=62,718 width=73) (actual time=0.601..37.644 rows=61,568 loops=1)

  • Filter: (((site_id_)::text = '0000'::text) AND ((product_classification_id_)::text = 'C059PART'::text))
  • Rows Removed by Filter: 3540
11. 17.850 34.037 ↓ 1.0 63,674 1

Hash (cost=2,323.31..2,323.31 rows=63,631 width=43) (actual time=34.037..34.037 rows=63,674 loops=1)

  • Buckets: 8192 Batches: 2 Memory Usage: 2349kB
12. 16.187 16.187 ↓ 1.0 63,674 1

Seq Scan on yimm_product_info ypr (cost=0.00..2,323.31 rows=63,631 width=43) (actual time=0.004..16.187 rows=63,674 loops=1)

13. 0.000 0.020 ↓ 0.0 0 1

Hash (cost=6.66..6.66 rows=1 width=39) (actual time=0.020..0.020 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
14. 0.001 0.020 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.83..6.66 rows=1 width=39) (actual time=0.020..0.020 rows=0 loops=1)

  • Join Filter: (((abc.site_id_)::text = (prabc.site_id_)::text) AND ((abc.abc_id_)::text = (prabc.abc_id_)::text))
15. 0.019 0.019 ↓ 0.0 0 1

Index Only Scan using product_abc_info_idx01 on product_abc_info prabc (cost=0.55..2.57 rows=1 width=75) (actual time=0.019..0.019 rows=0 loops=1)

  • Index Cond: (site_id_ = 'JA0022'::text)
  • Heap Fetches: 0
16. 0.000 0.000 ↓ 0.0 0

Index Scan using "index_siteId_05" on abc_definition_info abc (cost=0.28..4.07 rows=1 width=40) (never executed)

  • Index Cond: ((site_id_)::text = 'JA0022'::text)
17.          

SubPlan (forHash Left Join)

18. 61.568 61.568 ↑ 1.0 1 61,568

Index Scan using pk_product_category on product_category_setting pcslarge (cost=0.27..4.29 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=61,568)

  • Index Cond: ((product_category_id_)::text = (pcilarge.product_category_id_)::text)
19. 61.568 61.568 ↑ 1.0 1 61,568

Index Scan using pk_product_category on product_category_setting pcsmiddle (cost=0.27..4.29 rows=1 width=3) (actual time=0.001..0.001 rows=1 loops=61,568)

  • Index Cond: ((product_category_id_)::text = (pcimiddle.product_category_id_)::text)