explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ibG5

Settings
# exclusive inclusive rows x rows loops node
1. 6.722 54,782.661 ↑ 9.0 189 1

GroupAggregate (cost=17,486.46..17,635.39 rows=1,702 width=131) (actual time=54,775.007..54,782.661 rows=189 loops=1)

  • Group Key: p.brand_id, b.name
2. 31.474 54,775.939 ↓ 7.6 12,968 1

Sort (cost=17,486.46..17,490.72 rows=1,702 width=89) (actual time=54,774.969..54,775.939 rows=12,968 loops=1)

  • Sort Key: p.brand_id, b.name
  • Sort Method: quicksort Memory: 1995kB
3. 24.046 54,744.465 ↓ 7.6 12,968 1

Nested Loop Left Join (cost=27.45..17,395.13 rows=1,702 width=89) (actual time=14.960..54,744.465 rows=12,968 loops=1)

  • Join Filter: (((aw.status)::text = 'ZONED'::text) OR ((aw.status)::text = 'AUTHORIZED'::text))
  • Rows Removed by Join Filter: 1534
4. 36,146.914 54,681.515 ↓ 7.6 12,968 1

Nested Loop Left Join (cost=27.02..3,000.46 rows=1,702 width=101) (actual time=14.955..54,681.515 rows=12,968 loops=1)

  • Join Filter: (ppo.product_id = p.id)
  • Rows Removed by Join Filter: 381879260
5. 11.430 184.881 ↓ 7.6 12,968 1

Nested Loop Left Join (cost=26.60..2,966.49 rows=1,702 width=70) (actual time=0.137..184.881 rows=12,968 loops=1)

6. 11.307 43.771 ↓ 7.6 12,968 1

Nested Loop (cost=26.17..959.30 rows=1,702 width=43) (actual time=0.126..43.771 rows=12,968 loops=1)

  • Join Filter: (bm.brand_id = p.brand_id)
7. 0.496 3.964 ↓ 2.9 190 1

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

8. 1.297 1.378 ↓ 2.9 190 1

Bitmap Heap Scan on brand_managers bm (cost=25.46..218.50 rows=65 width=16) (actual time=0.104..1.378 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
9. 0.081 0.081 ↓ 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.081..0.081 rows=768 loops=1)

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

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

  • Index Cond: (id = bm.brand_id)
11. 28.500 28.500 ↓ 2.6 68 190

Index Scan using index_products_on_brand_id on products p (cost=0.42..4.11 rows=26 width=32) (actual time=0.012..0.150 rows=68 loops=190)

  • Index Cond: (brand_id = b.id)
12. 129.680 129.680 ↓ 0.0 0 12,968

Index Scan using index_authorized_weeks_on_year_and_week_and_product_id on authorized_weeks aw (cost=0.43..1.17 rows=1 width=43) (actual time=0.009..0.010 rows=0 loops=12,968)

  • Index Cond: ((year = 2019) AND (week = 36) AND (product_id = p.id))
  • Filter: ((retail_chain_id = '7b918c85-40a2-4ab9-b3da-f46164871b30'::uuid) AND (distribution_center_id = 'a33226ec-e547-47c9-922d-7aefacbd5a48'::uuid) AND (((status)::text = 'ZONED'::text) OR ((status)::text = 'AUTHORIZED'::text)))
  • Rows Removed by Filter: 0
13. 18,342.068 18,349.720 ↓ 29,448.0 29,448 12,968

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

14. 7.652 7.652 ↓ 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..7.652 rows=29,448 loops=1)

  • Index Cond: (period_id = 'c918fca9-9647-4fbe-9264-560ece0d119e'::uuid)
15. 38.904 38.904 ↓ 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.003 rows=0 loops=12,968)

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