explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XEFg

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 50.977 ↓ 2.0 2 1

Unique (cost=1,336,415.77..1,336,415.78 rows=1 width=9) (actual time=50.975..50.977 rows=2 loops=1)

2. 0.015 50.974 ↓ 2.0 2 1

Sort (cost=1,336,415.77..1,336,415.77 rows=1 width=9) (actual time=50.974..50.974 rows=2 loops=1)

  • Sort Key: (((NOT ((productfrom.width)::text IS DISTINCT FROM (productto.width)::text)) AND (NOT ((productfrom.depth)::text IS DISTINCT FROM (productto.depth)::text)) AND (NOT ((productfrom.length)::text IS DISTINCT FROM (productto.length)::text)) AND (NOT ((productfrom.height)::text IS DISTINCT FROM (productto.height)::text)) AND (NOT ((productfrom.diameter)::text IS DISTINCT FROM (productto.diameter)::text)))) DESC, (((productto.custom_program IS NULL) = (productfrom.custom_program IS NULL))) DESC, productto.article_number
  • Sort Method: quicksort Memory: 25kB
3. 0.008 50.959 ↓ 2.0 2 1

Nested Loop Semi Join (cost=1,428.53..1,336,415.76 rows=1 width=9) (actual time=5.220..50.959 rows=2 loops=1)

4. 0.032 50.747 ↑ 2.0 2 1

Nested Loop Left Join (cost=1,426.13..1,336,366.30 rows=4 width=71) (actual time=5.155..50.747 rows=2 loops=1)

  • Filter: ((fromisleather.name)::text IS DISTINCT FROM (toisleather.name)::text)
  • Rows Removed by Filter: 44
5. 23.182 50.577 ↓ 11.5 46 1

Nested Loop (cost=1,425.70..1,336,358.34 rows=4 width=83) (actual time=5.145..50.577 rows=46 loops=1)

  • Join Filter: ((productto.product_id <> productfrom.product_id) AND (NOT ((productto.collection)::text IS DISTINCT FROM (productfrom.collection)::text)) AND (NOT (((SubPlan 1))::text IS DISTINCT FROM ((SubPlan 2))::text)))
  • Rows Removed by Join Filter: 75,832
6. 0.003 0.026 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.85..16.92 rows=1 width=56) (actual time=0.023..0.026 rows=1 loops=1)

7. 0.016 0.016 ↑ 1.0 1 1

Index Scan using web_product_article_num_uk on web_product productfrom (cost=0.42..8.44 rows=1 width=44) (actual time=0.013..0.016 rows=1 loops=1)

  • Index Cond: (((article_number)::text = '10009994'::text) AND (release_id = 390))
8. 0.007 0.007 ↓ 0.0 0 1

Index Only Scan using web_product_type_pk on web_product_type fromisleather (cost=0.43..8.45 rows=1 width=28) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: ((product_id = productfrom.product_id) AND (release_id = productfrom.release_id) AND (release_id = 390) AND (name = 'LEATHER'::text))
  • Heap Fetches: 0
9. 18.042 20.943 ↑ 1.0 75,878 1

Bitmap Heap Scan on web_product productto (cost=1,424.85..31,742.92 rows=75,926 width=51) (actual time=3.791..20.943 rows=75,878 loops=1)

  • Recheck Cond: (release_id = 390)
  • Heap Blocks: exact=7,648
10. 2.901 2.901 ↑ 1.0 75,878 1

Bitmap Index Scan on web_product_release_id_index (cost=0.00..1,405.87 rows=75,926 width=0) (actual time=2.901..2.901 rows=75,878 loops=1)

  • Index Cond: (release_id = 390)
11.          

SubPlan (for Nested Loop)

12. 2.754 2.754 ↑ 1.0 1 918

Index Scan using web_product_attribute_pk on web_product_attribute (cost=0.56..8.58 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=918)

  • Index Cond: ((productfrom.product_id = product_id) AND (productfrom.release_id = release_id) AND ((characteristic_key)::text = 'EPM_SUBSET'::text))
13. 3.672 3.672 ↑ 1.0 1 918

Index Scan using web_product_attribute_pk on web_product_attribute web_product_attribute_1 (cost=0.56..8.58 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=918)

  • Index Cond: ((productto.product_id = product_id) AND (productto.release_id = release_id) AND ((characteristic_key)::text = 'EPM_SUBSET'::text))
14. 0.138 0.138 ↓ 0.0 0 46

Index Only Scan using web_product_type_pk on web_product_type toisleather (cost=0.43..1.98 rows=1 width=28) (actual time=0.003..0.003 rows=0 loops=46)

  • Index Cond: ((product_id = productto.product_id) AND (release_id = productto.release_id) AND (release_id = 390) AND (name = 'LEATHER'::text))
  • Heap Fetches: 2
15. 0.008 0.204 ↑ 1.0 1 2

Nested Loop (cost=2.40..12.35 rows=1 width=16) (actual time=0.102..0.102 rows=1 loops=2)

  • Join Filter: ((rls.valid_from >= s.valid_from) AND (rls.valid_from <= s.valid_to))
16. 0.004 0.180 ↑ 1.0 1 2

Nested Loop (cost=2.12..10.69 rows=1 width=56) (actual time=0.090..0.090 rows=1 loops=2)

  • Join Filter: ((rls.valid_from >= c.valid_from) AND (rls.valid_from <= c.valid_to))
17. 0.004 0.162 ↑ 4.0 1 2

Nested Loop (cost=1.84..9.33 rows=4 width=64) (actual time=0.081..0.081 rows=1 loops=2)

18. 0.006 0.136 ↑ 4.0 1 2

Nested Loop (cost=1.55..5.56 rows=4 width=40) (actual time=0.068..0.068 rows=1 loops=2)

19. 0.006 0.118 ↑ 4.0 1 2

Nested Loop (cost=1.26..3.93 rows=4 width=40) (actual time=0.059..0.059 rows=1 loops=2)

20. 0.006 0.058 ↑ 1.0 1 2

Nested Loop (cost=0.84..2.16 rows=1 width=48) (actual time=0.029..0.029 rows=1 loops=2)

21. 0.006 0.042 ↑ 1.0 1 2

Nested Loop (cost=0.71..1.98 rows=1 width=56) (actual time=0.021..0.021 rows=1 loops=2)

22. 0.020 0.020 ↑ 1.0 1 2

Index Scan using product_group_product_product_id_idx on product_group_product pgp (cost=0.42..1.15 rows=1 width=24) (actual time=0.010..0.010 rows=1 loops=2)

  • Index Cond: (product_id = productto.product_id)
  • Filter: ((release_id = 390) AND (deleted_flag = 0))
  • Rows Removed by Filter: 0
23. 0.016 0.016 ↑ 1.0 1 2

Index Scan using product_group_pk on product_group pg (cost=0.29..0.83 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=2)

  • Index Cond: ((id = pgp.product_group_id) AND (release_id = 390))
  • Filter: (deleted_flag = 0)
24. 0.010 0.010 ↑ 1.0 1 2

Index Scan using release_valid_from_uk on release rls (cost=0.13..0.16 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=2)

  • Index Cond: ((valid_from >= pg.valid_from) AND (valid_from <= pg.valid_to))
  • Filter: (id = 390)
  • Rows Removed by Filter: 2
25. 0.054 0.054 ↑ 4.0 1 2

Index Scan using subcategory_set_product_group_product_group_id_idx on subcategory_set_product_group sspg (cost=0.42..1.73 rows=4 width=24) (actual time=0.027..0.027 rows=1 loops=2)

  • Index Cond: (product_group_id = pgp.product_group_id)
  • Filter: ((release_id = 390) AND (deleted_flag = 0))
  • Rows Removed by Filter: 18
26. 0.012 0.012 ↑ 1.0 1 2

Index Scan using subcategory_set_pk on subcategory_set ss (cost=0.29..0.41 rows=1 width=24) (actual time=0.006..0.006 rows=1 loops=2)

  • Index Cond: ((id = sspg.subcategory_set_id) AND (release_id = 390))
  • Filter: (deleted_flag = 0)
27. 0.022 0.022 ↑ 1.0 1 2

Index Scan using category_subcategory_subcategory_id_idx on category_subcategory cs (cost=0.29..0.93 rows=1 width=24) (actual time=0.011..0.011 rows=1 loops=2)

  • Index Cond: (subcategory_id = ss.subcategory_id)
  • Filter: ((release_id = 390) AND (deleted_flag = 0))
  • Rows Removed by Filter: 3
28. 0.014 0.014 ↑ 1.0 1 2

Index Scan using category_pk on category c (cost=0.28..0.32 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=2)

  • Index Cond: ((id = cs.category_id) AND (release_id = 390))
  • Filter: (deleted_flag = 0)
29. 0.016 0.016 ↑ 1.0 1 2

Index Scan using subcategory_pk on subcategory s (cost=0.29..1.65 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=2)

  • Index Cond: ((id = ss.subcategory_id) AND (release_id = 390))
  • Filter: (deleted_flag = 0)
Planning time : 56.443 ms
Execution time : 51.116 ms