explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H2m1

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

Unique (cost=208,519.30..208,604.25 rows=2,614 width=105) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=208,519.30..208,525.83 rows=2,614 width=105) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=184,967.66..208,370.93 rows=2,614 width=105) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=184,967.23..192,242.83 rows=2,604 width=94) (actual rows= loops=)

  • Join Filter: (products_1.id = skus.product_id)
5. 0.000 0.000 ↓ 0.0

Merge Join (cost=184,966.81..189,393.87 rows=796 width=88) (actual rows= loops=)

  • Merge Cond: (products_1.id = products.id)
6. 0.000 0.000 ↓ 0.0

Unique (cost=171,958.69..173,645.53 rows=217,938 width=9) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Sort (cost=171,958.69..172,520.97 rows=224,913 width=9) (actual rows= loops=)

  • Sort Key: products_1.id, products_1.is_blemished
8. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=69,881.08..148,118.54 rows=224,913 width=9) (actual rows= loops=)

  • Hash Cond: (s.product_id = products_1.id)
  • Filter: (s.is_active OR ((products_1.type)::text = 'SuperProduct'::text))
9. 0.000 0.000 ↓ 0.0

Seq Scan on skus s (cost=0.00..61,062.34 rows=994,834 width=9) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Hash (cost=67,879.97..67,879.97 rows=108,969 width=17) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on products products_1 (cost=2,712.40..67,879.97 rows=108,969 width=17) (actual rows= loops=)

  • Recheck Cond: (is_active AND is_reviewed)
12. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_products_id_is_active_and_reviewed (cost=0.00..2,685.16 rows=167,347 width=0) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Sort (cost=13,008.12..13,012.13 rows=1,603 width=80) (actual rows= loops=)

  • Sort Key: products.id
14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=62.86..12,922.79 rows=1,603 width=80) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Index Scan using brands_pkey on brands (cost=0.28..54.36 rows=8 width=16) (actual rows= loops=)

  • Index Cond: (id = ANY ('{14082,16499,16632,17396,17760,16575,17945,18007}'::integer[]))
16. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on products (cost=62.58..1,606.46 rows=209 width=72) (actual rows= loops=)

  • Recheck Cond: (brand_id = brands.id)
  • Filter: (((type)::text <> 'SuperProduct'::text) AND (NOT (hashed SubPlan 3)))
17. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_products_brand_id (cost=0.00..11.57 rows=420 width=0) (actual rows= loops=)

  • Index Cond: (brand_id = brands.id)
18.          

SubPlan (forBitmap Heap Scan)

19. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_fspo_facet_set_id on facet_set_product_orders (cost=0.43..47.64 rows=1,326 width=8) (actual rows= loops=)

  • Index Cond: (facet_set_id = 2671)
20. 0.000 0.000 ↓ 0.0

Index Scan using idx_skus_product_id on skus (cost=0.42..3.55 rows=2 width=22) (actual rows= loops=)

  • Index Cond: (product_id = products.id)
21. 0.000 0.000 ↓ 0.0

Index Scan using idx_raw_skus_sku_id on raw_skus (cost=0.43..0.66 rows=2 width=19) (actual rows= loops=)

  • Index Cond: (sku_id = skus.id)
  • Filter: (distributor_id <> ALL ('{4,61}'::bigint[]))
22.          

SubPlan (forNested Loop)

23. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_facets_products_facet_id_product_id on facets_products (cost=0.43..4.45 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((facet_id = 2094) AND (product_id = products.id))
24. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_facets_products_facet_id_product_id on facets_products facets_products_1 (cost=0.43..24.30 rows=450 width=8) (actual rows= loops=)

  • Index Cond: (facet_id = 2094)