explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6QrP

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Hash Join (cost=5,545,172.23..5,545,173.59 rows=1 width=135) (actual rows= loops=)

  • Hash Cond: (mpv.affiliate_merchant_id = a.id)
2. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,472,121.15..1,472,121.75 rows=200 width=12) (actual rows= loops=)

  • Group Key: mpv.affiliate_merchant_id
3. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,465,074.89..1,468,094.72 rows=1,006,609 width=25) (actual rows= loops=)

  • Group Key: mpv.merchant_product_id, mpv.color, mpv.affiliate_merchant_id
4. 0.000 0.000 ↓ 0.0

Index Scan using index_merchant_product_variations_on_active on merchant_product_variations mpv (cost=0.11..1,463,265.36 rows=1,206,349 width=17) (actual rows= loops=)

  • Index Cond: (active = true)
  • Filter: (active AND (affiliate_merchant_id = ANY ('{2,8,9,11,13,14,17,20,21,22,23,31,32,33,35,36,37,38}'::integer[])))
5. 0.000 0.000 ↓ 0.0

Hash (cost=4,073,051.07..4,073,051.07 rows=1 width=677) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,072,946.75..4,073,051.07 rows=1 width=677) (actual rows= loops=)

  • Join Filter: (a.id = mpv_2.affiliate_merchant_id)
7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,623,320.01..2,623,424.04 rows=1 width=665) (actual rows= loops=)

  • Join Filter: (a.id = merchant_products_2.affiliate_merchant_id)
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,235,194.96..2,235,298.63 rows=1 width=637) (actual rows= loops=)

  • Join Filter: (a.id = merchant_products_1.affiliate_merchant_id)
9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,853,631.32..1,853,734.64 rows=1 width=625) (actual rows= loops=)

  • Join Filter: (a.id = merchant_products.affiliate_merchant_id)
10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,468,617.56..1,468,720.54 rows=1 width=617) (actual rows= loops=)

  • Join Filter: (a.id = mpv_1.affiliate_merchant_id)
11. 0.000 0.000 ↓ 0.0

Merge Join (cost=4,145.85..4,248.53 rows=1 width=605) (actual rows= loops=)

  • Merge Cond: (a.id = a_1.id)
12. 0.000 0.000 ↓ 0.0

Subquery Scan on a (cost=2,072.93..2,124.24 rows=30 width=312) (actual rows= loops=)

  • Filter: (a.rn = 1)
13. 0.000 0.000 ↓ 0.0

WindowAgg (cost=2,072.93..2,103.11 rows=6,037 width=352) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Sort (cost=2,072.93..2,075.94 rows=6,037 width=324) (actual rows= loops=)

  • Sort Key: fil.affiliate_merchant_id, fil.created_at DESC
15. 0.000 0.000 ↓ 0.0

Hash Join (cost=13.37..1,997.10 rows=6,037 width=324) (actual rows= loops=)

  • Hash Cond: (fil.affiliate_merchant_id = afm.id)
16. 0.000 0.000 ↓ 0.0

Seq Scan on feed_import_logs fil (cost=0.00..1,955.31 rows=13,752 width=301) (actual rows= loops=)

  • Filter: (status = 4)
17. 0.000 0.000 ↓ 0.0

Hash (cost=13.31..13.31 rows=18 width=23) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Seq Scan on affiliate_merchants afm (cost=0.00..13.31 rows=18 width=23) (actual rows= loops=)

  • Filter: (id = ANY ('{2,8,9,11,13,14,17,20,21,22,23,31,32,33,35,36,37,38}'::integer[]))
19. 0.000 0.000 ↓ 0.0

Materialize (cost=2,072.93..2,124.25 rows=30 width=293) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Subquery Scan on a_1 (cost=2,072.93..2,124.24 rows=30 width=293) (actual rows= loops=)

  • Filter: (a_1.rn = 2)
21. 0.000 0.000 ↓ 0.0

WindowAgg (cost=2,072.93..2,103.11 rows=6,037 width=365) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Sort (cost=2,072.93..2,075.94 rows=6,037 width=305) (actual rows= loops=)

  • Sort Key: fil_1.affiliate_merchant_id, fil_1.created_at DESC
23. 0.000 0.000 ↓ 0.0

Hash Join (cost=13.37..1,997.10 rows=6,037 width=305) (actual rows= loops=)

  • Hash Cond: (fil_1.affiliate_merchant_id = afm_1.id)
24. 0.000 0.000 ↓ 0.0

Seq Scan on feed_import_logs fil_1 (cost=0.00..1,955.31 rows=13,752 width=301) (actual rows= loops=)

  • Filter: (status = 4)
25. 0.000 0.000 ↓ 0.0

Hash (cost=13.31..13.31 rows=18 width=4) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Seq Scan on affiliate_merchants afm_1 (cost=0.00..13.31 rows=18 width=4) (actual rows= loops=)

  • Filter: (id = ANY ('{2,8,9,11,13,14,17,20,21,22,23,31,32,33,35,36,37,38}'::integer[]))
27. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,464,471.71..1,464,471.81 rows=31 width=12) (actual rows= loops=)

  • Group Key: mpv_1.affiliate_merchant_id
28. 0.000 0.000 ↓ 0.0

Index Scan using index_merchant_product_variations_on_active on merchant_product_variations mpv_1 (cost=0.11..1,463,265.36 rows=1,206,349 width=4) (actual rows= loops=)

  • Index Cond: (active = true)
  • Filter: (active AND (affiliate_merchant_id = ANY ('{2,8,9,11,13,14,17,20,21,22,23,31,32,33,35,36,37,38}'::integer[])))
29. 0.000 0.000 ↓ 0.0

HashAggregate (cost=385,013.75..385,013.86 rows=37 width=12) (actual rows= loops=)

  • Group Key: merchant_products.affiliate_merchant_id
30. 0.000 0.000 ↓ 0.0

Index Scan using index_merchant_products_on_active on merchant_products (cost=0.09..384,364.20 rows=649,552 width=4) (actual rows= loops=)

  • Index Cond: (active = true)
  • Filter: (active AND (affiliate_merchant_id = ANY ('{2,8,9,11,13,14,17,20,21,22,23,31,32,33,35,36,37,38}'::integer[])))
31. 0.000 0.000 ↓ 0.0

HashAggregate (cost=381,563.64..381,563.75 rows=37 width=12) (actual rows= loops=)

  • Group Key: merchant_products_1.affiliate_merchant_id
32. 0.000 0.000 ↓ 0.0

Index Scan using index_merchant_products_on_active on merchant_products merchant_products_1 (cost=0.09..381,094.74 rows=468,895 width=4) (actual rows= loops=)

  • Index Cond: (active = true)
  • Filter: (active AND (brand_id IS NOT NULL) AND (brand_id <> 0))
33. 0.000 0.000 ↓ 0.0

HashAggregate (cost=388,125.05..388,125.16 rows=37 width=28) (actual rows= loops=)

  • Group Key: merchant_products_2.affiliate_merchant_id
34. 0.000 0.000 ↓ 0.0

Index Scan using index_merchant_products_on_active on merchant_products merchant_products_2 (cost=0.09..381,094.74 rows=585,859 width=89) (actual rows= loops=)

  • Index Cond: (active = true)
  • Filter: (active AND (category_id IS NOT NULL) AND (category_id <> 0))
35. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,449,626.74..1,449,626.83 rows=31 width=12) (actual rows= loops=)

  • Group Key: mpv_2.affiliate_merchant_id
36. 0.000 0.000 ↓ 0.0

Index Scan using index_merchant_product_variations_on_active on merchant_product_variations mpv_2 (cost=0.11..1,446,156.73 rows=3,470,011 width=4) (actual rows= loops=)

  • Index Cond: (active = true)
  • Filter: (active AND (color_group IS NOT NULL))