explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6A5O6

Settings
# exclusive inclusive rows x rows loops node
1. 6.252 54,681.725 ↑ 1.1 189 1

GroupAggregate (cost=17,030.73..17,077.61 rows=200 width=120) (actual time=54,674.578..54,681.725 rows=189 loops=1)

  • Group Key: p.brand_id
2.          

CTE filtered_products

3. 6.904 42.241 ↓ 13.7 12,968 1

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

4. 0.600 3.797 ↓ 2.9 190 1

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

5. 1.225 1.297 ↓ 2.9 190 1

Bitmap Heap Scan on brand_managers bm (cost=25.46..218.50 rows=65 width=16) (actual time=0.094..1.297 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.072 0.072 ↓ 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.072..0.072 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. 31.540 31.540 ↓ 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.012..0.166 rows=68 loops=190)

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

Sort (cost=16,075.65..16,078.02 rows=947 width=78) (actual time=54,674.547..54,675.473 rows=12,968 loops=1)

  • Sort Key: p.brand_id
  • Sort Method: quicksort Memory: 1453kB
10. 23.658 54,656.874 ↓ 13.7 12,968 1

Nested Loop Left Join (cost=1.40..16,028.84 rows=947 width=78) (actual time=14.200..54,656.874 rows=12,968 loops=1)

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

Nested Loop Left Join (cost=0.98..8,024.32 rows=947 width=79) (actual time=14.196..54,594.312 rows=12,968 loops=1)

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

Nested Loop Left Join (cost=0.55..8,001.67 rows=947 width=48) (actual time=0.130..201.643 rows=12,968 loops=1)

13. 58.497 58.497 ↓ 13.7 12,968 1

CTE Scan on filtered_products p (cost=0.00..18.94 rows=947 width=32) (actual time=0.116..58.497 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,290.358 18,297.848 ↓ 29,448.0 29,448 12,968

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

16. 7.490 7.490 ↓ 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.016..7.490 rows=29,448 loops=1)

  • Index Cond: (period_id = 'c918fca9-9647-4fbe-9264-560ece0d119e'::uuid)
17. 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.225 ms
Execution time : 54,682.465 ms