explain.depesz.com

PostgreSQL's explain analyze made readable

Result: riMQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 53.254 ↓ 2.0 2 1

Unique (cost=40,246.06..40,246.08 rows=1 width=9) (actual time=53.253..53.254 rows=2 loops=1)

2. 0.015 53.253 ↓ 2.0 2 1

Sort (cost=40,246.06..40,246.07 rows=1 width=9) (actual time=53.253..53.253 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.007 53.238 ↓ 2.0 2 1

Nested Loop Semi Join (cost=1,429.52..40,246.05 rows=1 width=9) (actual time=7.801..53.238 rows=2 loops=1)

4. 0.010 53.049 ↑ 6.5 2 1

Nested Loop Left Join (cost=1,427.25..39,045.08 rows=13 width=71) (actual time=7.704..53.049 rows=2 loops=1)

  • Filter: ((fromisleather.name)::text IS DISTINCT FROM (toisleather.name)::text)
  • Rows Removed by Filter: 44
5. 0.616 52.855 ↓ 3.3 46 1

Nested Loop Left Join (cost=1,426.82..38,998.42 rows=14 width=83) (actual time=7.693..52.855 rows=46 loops=1)

  • Filter: (NOT ((subsetto.characteristic_value_key)::text IS DISTINCT FROM (subsetfrom.characteristic_value_key)::text))
  • Rows Removed by Filter: 872
6. 23.381 47.649 ↓ 1.1 918 1

Nested Loop (cost=1,426.26..32,907.32 rows=845 width=87) (actual time=6.771..47.649 rows=918 loops=1)

  • Join Filter: ((productto.product_id <> productfrom.product_id) AND (NOT ((productto.collection)::text IS DISTINCT FROM (productfrom.collection)::text)))
  • Rows Removed by Join Filter: 74,960
7. 0.001 0.041 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.41..25.50 rows=1 width=60) (actual time=0.040..0.041 rows=1 loops=1)

8. 0.002 0.032 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.98..17.03 rows=1 width=48) (actual time=0.030..0.032 rows=1 loops=1)

9. 0.018 0.018 ↑ 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.016..0.018 rows=1 loops=1)

  • Index Cond: (((article_number)::text = '10009994'::text) AND (release_id = 390))
10. 0.012 0.012 ↑ 1.0 1 1

Index Scan using web_product_attribute_pk on web_product_attribute subsetfrom (cost=0.56..8.58 rows=1 width=20) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: ((productfrom.product_id = product_id) AND (productfrom.release_id = release_id) AND (release_id = 390) AND ((characteristic_key)::text = 'EPM_SUBSET'::text))
11. 0.008 0.008 ↓ 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.008..0.008 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
12. 19.321 24.227 ↑ 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=6.115..24.227 rows=75,878 loops=1)

  • Recheck Cond: (release_id = 390)
  • Heap Blocks: exact=7,648
13. 4.906 4.906 ↑ 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=4.906..4.906 rows=75,878 loops=1)

  • Index Cond: (release_id = 390)
14. 4.590 4.590 ↑ 1.0 1 918

Index Scan using web_product_attribute_pk on web_product_attribute subsetto (cost=0.56..7.20 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=918)

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

Index Only Scan using web_product_type_pk on web_product_type toisleather (cost=0.43..3.32 rows=1 width=28) (actual time=0.004..0.004 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
16. 0.006 0.182 ↑ 1.0 1 2

Nested Loop (cost=2.27..92.37 rows=1 width=16) (actual time=0.091..0.091 rows=1 loops=2)

  • Join Filter: ((rls.valid_from >= pg.valid_from) AND (rls.valid_from <= pg.valid_to))
17. 0.004 0.164 ↑ 1.0 1 2

Nested Loop (cost=1.98..89.68 rows=1 width=48) (actual time=0.082..0.082 rows=1 loops=2)

  • Join Filter: ((rls.valid_from >= c.valid_from) AND (rls.valid_from <= c.valid_to))
18. 0.010 0.148 ↑ 1.0 1 2

Nested Loop (cost=1.71..87.52 rows=1 width=64) (actual time=0.074..0.074 rows=1 loops=2)

19. 0.002 0.120 ↑ 1.0 1 2

Nested Loop (cost=1.42..78.25 rows=1 width=72) (actual time=0.060..0.060 rows=1 loops=2)

  • Join Filter: ((rls.valid_from >= s.valid_from) AND (rls.valid_from <= s.valid_to))
20. 0.012 0.012 ↑ 1.0 1 2

Seq Scan on release rls (cost=0.00..1.07 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=2)

  • Filter: (id = 390)
  • Rows Removed by Filter: 3
21. 0.004 0.106 ↑ 4.0 1 2

Nested Loop (cost=1.42..77.12 rows=4 width=80) (actual time=0.053..0.053 rows=1 loops=2)

22. 0.004 0.090 ↑ 4.0 1 2

Nested Loop (cost=1.13..65.10 rows=4 width=56) (actual time=0.045..0.045 rows=1 loops=2)

23. 0.006 0.074 ↑ 4.0 1 2

Nested Loop (cost=0.84..55.13 rows=4 width=48) (actual time=0.037..0.037 rows=1 loops=2)

24. 0.018 0.018 ↑ 1.0 1 2

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

  • Index Cond: (product_id = productto.product_id)
  • Filter: ((release_id = 390) AND (deleted_flag = 0))
  • Rows Removed by Filter: 0
25. 0.050 0.050 ↑ 4.0 1 2

Index Scan using subcategory_set_product_group_product_group_id_idx on subcategory_set_product_group sspg (cost=0.42..40.18 rows=4 width=24) (actual time=0.025..0.025 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..2.49 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.012 0.012 ↑ 1.0 1 2

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

  • Index Cond: ((id = ss.subcategory_id) AND (release_id = 390))
  • Filter: (deleted_flag = 0)
28. 0.018 0.018 ↑ 1.0 1 2

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

  • Index Cond: (subcategory_id = s.id)
  • Filter: ((release_id = 390) AND (deleted_flag = 0))
  • Rows Removed by Filter: 3
29. 0.012 0.012 ↑ 1.0 1 2

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

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

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

  • Index Cond: ((id = pgp.product_group_id) AND (release_id = 390))
  • Filter: (deleted_flag = 0)
Planning time : 83.562 ms
Execution time : 53.438 ms