explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Boui : 2

Settings
# exclusive inclusive rows x rows loops node
1. 149.983 629.235 ↓ 1.1 61,568 1

Sort (cost=40,733.18..40,877.73 rows=57,819 width=94) (actual time=610.861..629.235 rows=61,568 loops=1)

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

Hash Left Join (cost=20,549.03..34,042.41 rows=57,819 width=94) (actual time=254.718..479.252 rows=61,568 loops=1)

  • Hash Cond: ((pr.product_id_)::text = (ypr.product_id_)::text)
3. 13.097 359.053 ↓ 1.1 61,568 1

Hash Left Join (cost=16,870.33..27,088.18 rows=57,819 width=88) (actual time=218.382..359.053 rows=61,568 loops=1)

  • Hash Cond: ((pr.product_id_)::text = (prabc.product_id_)::text)
4. 67.861 345.929 ↓ 1.1 61,568 1

Hash Join (cost=16,863.66..26,864.68 rows=57,819 width=86) (actual time=218.343..345.929 rows=61,568 loops=1)

  • Hash Cond: ((pcilarge.product_id_)::text = (pr.product_id_)::text)
5. 17.191 60.122 ↓ 1.0 61,568 1

Hash Join (cost=16.63..6,679.32 rows=61,562 width=47) (actual time=0.332..60.122 rows=61,568 loops=1)

  • Hash Cond: ((pcilarge.product_category_id_)::text = (pcslarge.product_category_id_)::text)
6. 42.728 42.728 ↓ 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.121..42.728 rows=61,568 loops=1)

  • Filter: ((product_category_type_id_)::text = 'PARTLARGEGROUP'::text)
  • Rows Removed by Filter: 64929
7. 0.069 0.203 ↓ 1.1 357 1

Hash (cost=12.39..12.39 rows=339 width=42) (actual time=0.203..0.203 rows=357 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
8. 0.134 0.134 ↓ 1.1 357 1

Seq Scan on product_category_setting pcslarge (cost=0.00..12.39 rows=339 width=42) (actual time=0.049..0.134 rows=357 loops=1)

9. 27.765 217.946 ↓ 1.1 61,568 1

Hash (cost=15,098.97..15,098.97 rows=58,085 width=113) (actual time=217.946..217.946 rows=61,568 loops=1)

  • Buckets: 4096 Batches: 4 Memory Usage: 2223kB
10. 68.204 190.181 ↓ 1.1 61,568 1

Hash Join (cost=5,334.34..15,098.97 rows=58,085 width=113) (actual time=58.689..190.181 rows=61,568 loops=1)

  • Hash Cond: ((pcimiddle.product_id_)::text = (pr.product_id_)::text)
11. 17.576 63.624 ↓ 1.0 61,568 1

Hash Join (cost=16.63..6,676.47 rows=61,355 width=40) (actual time=0.252..63.624 rows=61,568 loops=1)

  • Hash Cond: ((pcimiddle.product_category_id_)::text = (pcsmiddle.product_category_id_)::text)
12. 45.892 45.892 ↓ 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.081..45.892 rows=61,568 loops=1)

  • Filter: ((product_category_type_id_)::text = 'PARTMIDDLEGROUP'::text)
  • Rows Removed by Filter: 64929
13. 0.104 0.156 ↓ 1.1 357 1

Hash (cost=12.39..12.39 rows=339 width=35) (actual time=0.156..0.156 rows=357 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
14. 0.052 0.052 ↓ 1.1 357 1

Seq Scan on product_category_setting pcsmiddle (cost=0.00..12.39 rows=339 width=35) (actual time=0.001..0.052 rows=357 loops=1)

15. 21.388 58.353 ↑ 1.0 61,568 1

Hash (cost=3,736.74..3,736.74 rows=62,718 width=73) (actual time=58.353..58.353 rows=61,568 loops=1)

  • Buckets: 4096 Batches: 2 Memory Usage: 3233kB
16. 36.965 36.965 ↑ 1.0 61,568 1

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

  • Filter: (((site_id_)::text = '0000'::text) AND ((product_classification_id_)::text = 'C059PART'::text))
  • Rows Removed by Filter: 3540
17. 0.000 0.027 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
18. 0.001 0.027 ↓ 0.0 0 1

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

  • Join Filter: (((abc.site_id_)::text = (prabc.site_id_)::text) AND ((abc.abc_id_)::text = (prabc.abc_id_)::text))
19. 0.026 0.026 ↓ 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.026..0.026 rows=0 loops=1)

  • Index Cond: (site_id_ = 'JA0022'::text)
  • Heap Fetches: 0
20. 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)
21. 19.000 36.300 ↓ 1.0 63,674 1

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

  • Buckets: 8192 Batches: 2 Memory Usage: 2349kB
22. 17.300 17.300 ↓ 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.010..17.300 rows=63,674 loops=1)

Planning time : 3.947 ms
Execution time : 635.001 ms