explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bTBm

Settings
# exclusive inclusive rows x rows loops node
1. 6.631 54,681.371 ↑ 1.1 189 1

GroupAggregate (cost=17,030.73..17,079.97 rows=200 width=152) (actual time=54,673.777..54,681.371 rows=189 loops=1)

  • Group Key: p.brand_id, p.brand_name
2.          

CTE filtered_products

3. 6.715 39.642 ↓ 13.7 12,968 1

Nested Loop (cost=26.17..955.07 rows=947 width=43) (actual time=0.148..39.642 rows=12,968 loops=1)

4. 0.603 3.857 ↓ 2.9 190 1

Nested Loop (cost=25.75..670.98 rows=65 width=43) (actual time=0.137..3.857 rows=190 loops=1)

5. 1.249 1.354 ↓ 2.9 190 1

Bitmap Heap Scan on brand_managers bm (cost=25.46..218.50 rows=65 width=16) (actual time=0.129..1.354 rows=190 loops=1)

  • Recheck Cond: (company_id = '641ccf80-aa13-4f52-be37-5ba43d029b43'::uuid)
  • Filter: ((retail_chain_id = '7b918c85-40a2-4ab9-b3da-f46164871b30'::uuid) AND (distribution_center_id = 'a33226ec-e547-47c9-922d-7aefacbd5a48'::uuid))
  • Rows Removed by Filter: 482
  • Heap Blocks: exact=140
6. 0.105 0.105 ↓ 1.1 768 1

Bitmap Index Scan on index_brand_managers_on_company_id (cost=0.00..25.45 rows=688 width=0) (actual time=0.105..0.105 rows=768 loops=1)

  • Index Cond: (company_id = '641ccf80-aa13-4f52-be37-5ba43d029b43'::uuid)
7. 1.900 1.900 ↑ 1.0 1 190

Index Scan using brands_pkey on brands b (cost=0.29..6.95 rows=1 width=27) (actual time=0.009..0.010 rows=1 loops=190)

  • Index Cond: (id = bm.brand_id)
8. 29.070 29.070 ↓ 2.6 68 190

Index Scan using index_products_on_brand_id on products p_1 (cost=0.42..4.11 rows=26 width=32) (actual time=0.011..0.153 rows=68 loops=190)

  • Index Cond: (brand_id = b.id)
9. 21.622 54,674.740 ↓ 13.7 12,968 1

Sort (cost=16,075.65..16,078.02 rows=947 width=110) (actual time=54,673.742..54,674.740 rows=12,968 loops=1)

  • Sort Key: p.brand_id, p.brand_name
  • Sort Method: quicksort Memory: 1995kB
10. 38.157 54,653.118 ↓ 13.7 12,968 1

Nested Loop Left Join (cost=1.40..16,028.84 rows=947 width=110) (actual time=17.778..54,653.118 rows=12,968 loops=1)

  • Join Filter: (aw.id IS NOT NULL)
  • Rows Removed by Join Filter: 1534
11. 36,026.609 54,589.025 ↓ 13.7 12,968 1

Nested Loop Left Join (cost=0.98..8,024.32 rows=947 width=111) (actual time=17.774..54,589.025 rows=12,968 loops=1)

  • Join Filter: (ppo.product_id = p.id)
  • Rows Removed by Join Filter: 381879260
12. 14.982 199.728 ↓ 13.7 12,968 1

Nested Loop Left Join (cost=0.55..8,001.67 rows=947 width=80) (actual time=0.163..199.728 rows=12,968 loops=1)

13. 55.066 55.066 ↓ 13.7 12,968 1

CTE Scan on filtered_products p (cost=0.00..18.94 rows=947 width=64) (actual time=0.150..55.066 rows=12,968 loops=1)

14. 129.680 129.680 ↓ 0.0 0 12,968

Index Scan using index_authorized_weeks_on_references_and_week on authorized_weeks aw (cost=0.55..8.42 rows=1 width=32) (actual time=0.010..0.010 rows=0 loops=12,968)

  • Index Cond: ((retail_chain_id = '7b918c85-40a2-4ab9-b3da-f46164871b30'::uuid) AND (distribution_center_id = 'a33226ec-e547-47c9-922d-7aefacbd5a48'::uuid) AND (year = 2019) AND (week = 36) AND (product_id = p.id))
15. 18,351.821 18,362.688 ↓ 29,448.0 29,448 12,968

Materialize (cost=0.42..8.45 rows=1 width=47) (actual time=0.000..1.416 rows=29,448 loops=12,968)

16. 10.867 10.867 ↓ 29,448.0 29,448 1

Index Scan using index_period_product_overrides_on_period_id on period_product_overrides ppo (cost=0.42..8.44 rows=1 width=47) (actual time=0.015..10.867 rows=29,448 loops=1)

  • Index Cond: (period_id = 'c918fca9-9647-4fbe-9264-560ece0d119e'::uuid)
17. 25.936 25.936 ↓ 0.0 0 12,968

Index Scan using period_product_overrides_pkey on period_product_overrides aut_ppo (cost=0.42..8.44 rows=1 width=31) (actual time=0.002..0.002 rows=0 loops=12,968)

  • Index Cond: (id = ppo.id)
Planning time : 1.219 ms
Execution time : 54,682.161 ms