explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mDJs

Settings
# exclusive inclusive rows x rows loops node
1. 0.038 2,349.756 ↓ 89.0 178 1

Unique (cost=284,485.83..284,485.85 rows=2 width=9) (actual time=2,349.709..2,349.756 rows=178 loops=1)

2. 0.475 2,349.718 ↓ 89.0 178 1

Sort (cost=284,485.83..284,485.83 rows=2 width=9) (actual time=2,349.708..2,349.718 rows=178 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: 33kB
3. 573.133 2,349.243 ↓ 89.0 178 1

Nested Loop Semi Join (cost=4,523.44..284,485.82 rows=2 width=9) (actual time=332.878..2,349.243 rows=178 loops=1)

  • Join Filter: (productto.product_id = pgp.product_id)
  • Rows Removed by Join Filter: 10,187,672
4. 2.417 1,208.884 ↑ 5.4 209 1

Nested Loop Left Join (cost=1,002.40..273,471.53 rows=1,128 width=71) (actual time=276.825..1,208.884 rows=209 loops=1)

  • Filter: ((fromisleather.name)::text IS DISTINCT FROM (toisleather.name)::text)
  • Rows Removed by Filter: 2,212
5. 0.000 1,194.362 ↓ 2.0 2,421 1

Nested Loop (cost=1,001.97..269,342.60 rows=1,239 width=83) (actual time=24.991..1,194.362 rows=2,421 loops=1)

  • Join Filter: ((productto.product_id <> productfrom.product_id) AND ((productto.collection)::text <> (productfrom.collection)::text) AND (NOT ((subsetto.characteristic_value_key)::text IS DISTINCT FROM (subsetfrom.characteristic_value_key)::text)))
  • Rows Removed by Join Filter: 73,457
6. 0.001 0.051 ↑ 1.0 1 1

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

7. 0.003 0.038 ↑ 1.0 1 1

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

8. 0.021 0.021 ↑ 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.019..0.021 rows=1 loops=1)

  • Index Cond: (((article_number)::text = '10009994'::text) AND (release_id = 390))
9. 0.014 0.014 ↑ 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.014..0.014 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))
10. 0.012 0.012 ↓ 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.012..0.012 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
11. 0.000 1,213.232 ↑ 1.0 75,878 1

Gather (cost=1,000.56..267,988.39 rows=75,926 width=55) (actual time=0.978..1,213.232 rows=75,878 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 13.229 1,497.232 ↑ 1.3 25,293 3 / 3

Nested Loop Left Join (cost=0.56..259,395.79 rows=31,636 width=55) (actual time=3.848..1,497.232 rows=25,293 loops=3)

13. 42.321 42.321 ↑ 1.3 25,293 3 / 3

Parallel Seq Scan on web_product productto (cost=0.00..31,745.96 rows=31,636 width=51) (actual time=0.011..42.321 rows=25,293 loops=3)

  • Filter: (release_id = 390)
  • Rows Removed by Filter: 126,833
14. 1,441.682 1,441.682 ↑ 1.0 1 75,878 / 3

Index Scan using web_product_attribute_pk on web_product_attribute subsetto (cost=0.56..7.20 rows=1 width=20) (actual time=0.057..0.057 rows=1 loops=75,878)

  • 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. 12.105 12.105 ↓ 0.0 0 2,421

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.005..0.005 rows=0 loops=2,421)

  • Index Cond: ((product_id = productto.product_id) AND (release_id = productto.release_id) AND (release_id = 390) AND (name = 'LEATHER'::text))
  • Heap Fetches: 209
16. 403.266 567.226 ↓ 413.1 48,746 209

Materialize (cost=3,521.05..9,017.99 rows=118 width=16) (actual time=0.170..2.714 rows=48,746 loops=209)

17. 0.000 163.960 ↓ 1,329.6 156,894 1

Gather (cost=3,521.05..9,017.40 rows=118 width=16) (actual time=35.589..163.960 rows=156,894 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
18. 9.577 177.630 ↓ 1,067.3 52,298 3 / 3

Nested Loop (cost=2,521.05..8,005.60 rows=49 width=16) (actual time=42.449..177.630 rows=52,298 loops=3)

  • Join Filter: (pg.id = pgp.product_group_id)
19. 0.000 84.815 ↓ 544.0 4,896 3 / 3

Nested Loop (cost=2,520.62..7,817.93 rows=9 width=32) (actual time=42.401..84.815 rows=4,896 loops=3)

  • Join Filter: ((rls.valid_from >= pg.valid_from) AND (rls.valid_from <= pg.valid_to))
  • Rows Removed by Join Filter: 3,086
20. 2.432 61.077 ↓ 93.3 8,025 3 / 3

Hash Join (cost=2,520.33..7,579.65 rows=86 width=32) (actual time=42.371..61.077 rows=8,025 loops=3)

  • Hash Cond: (sspg.subcategory_set_id = ss.id)
21. 16.438 16.438 ↑ 1.3 7,989 3 / 3

Parallel Seq Scan on subcategory_set_product_group sspg (cost=0.00..5,020.49 rows=10,125 width=24) (actual time=0.013..16.438 rows=7,989 loops=3)

  • Filter: ((release_id = 390) AND (deleted_flag = 0))
  • Rows Removed by Filter: 65,584
22. 1.059 42.207 ↓ 72.3 3,111 3 / 3

Hash (cost=2,519.80..2,519.80 rows=43 width=32) (actual time=42.207..42.207 rows=3,111 loops=3)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 227kB
23. 0.000 41.148 ↓ 72.3 3,111 3 / 3

Nested Loop (cost=16.87..2,519.80 rows=43 width=32) (actual time=0.308..41.148 rows=3,111 loops=3)

24. 2.173 22.019 ↓ 69.6 3,201 3 / 3

Nested Loop (cost=16.58..2,149.67 rows=46 width=40) (actual time=0.274..22.019 rows=3,201 loops=3)

  • Join Filter: ((rls.valid_from >= s.valid_from) AND (rls.valid_from <= s.valid_to))
  • Rows Removed by Join Filter: 1,419
25. 0.687 5.911 ↓ 10.6 4,645 3 / 3

Nested Loop (cost=16.29..812.28 rows=438 width=32) (actual time=0.246..5.911 rows=4,645 loops=3)

26. 0.030 0.192 ↓ 6.8 68 3 / 3

Nested Loop (cost=5.10..36.10 rows=10 width=32) (actual time=0.065..0.192 rows=68 loops=3)

  • Join Filter: ((rls.valid_from >= c.valid_from) AND (rls.valid_from <= c.valid_to))
  • Rows Removed by Join Filter: 20
27. 0.017 0.017 ↑ 1.0 1 3 / 3

Seq Scan on release rls (cost=0.00..1.07 rows=1 width=16) (actual time=0.016..0.017 rows=1 loops=3)

  • Filter: (id = 390)
  • Rows Removed by Filter: 5
28. 0.105 0.145 ↑ 1.0 88 3 / 3

Bitmap Heap Scan on category c (cost=5.10..33.70 rows=88 width=32) (actual time=0.046..0.145 rows=88 loops=3)

  • Recheck Cond: (release_id = 390)
  • Filter: (deleted_flag = 0)
  • Rows Removed by Filter: 19
  • Heap Blocks: exact=13
29. 0.040 0.040 ↑ 1.0 107 3 / 3

Bitmap Index Scan on category_release_id_idx (cost=0.00..5.08 rows=107 width=0) (actual time=0.040..0.040 rows=107 loops=3)

  • Index Cond: (release_id = 390)
30. 2.856 5.032 ↓ 1.4 68 204 / 3

Bitmap Heap Scan on category_subcategory cs (cost=11.19..77.14 rows=48 width=24) (actual time=0.036..0.074 rows=68 loops=204)

  • Recheck Cond: ((category_id = c.id) AND (release_id = 390))
  • Filter: (deleted_flag = 0)
  • Rows Removed by Filter: 12
  • Heap Blocks: exact=1,194
31. 2.176 2.176 ↓ 1.4 80 204 / 3

Bitmap Index Scan on category_subcategory_pk (cost=0.00..11.18 rows=59 width=0) (actual time=0.032..0.032 rows=80 loops=204)

  • Index Cond: ((category_id = c.id) AND (release_id = 390))
32. 13.935 13.935 ↑ 1.0 1 13,935 / 3

Index Scan using subcategory_pk on subcategory s (cost=0.29..3.04 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=13,935)

  • Index Cond: ((id = cs.subcategory_id) AND (release_id = 390))
  • Filter: (deleted_flag = 0)
  • Rows Removed by Filter: 0
33. 19.206 19.206 ↑ 1.0 1 9,603 / 3

Index Scan using subcategory_set_subcategory_id_idx on subcategory_set ss (cost=0.29..8.04 rows=1 width=24) (actual time=0.004..0.006 rows=1 loops=9,603)

  • Index Cond: (subcategory_id = s.id)
  • Filter: ((release_id = 390) AND (deleted_flag = 0))
  • Rows Removed by Filter: 5
34. 24.076 24.076 ↑ 1.0 1 24,076 / 3

Index Scan using product_group_pk on product_group pg (cost=0.29..2.76 rows=1 width=32) (actual time=0.002..0.003 rows=1 loops=24,076)

  • Index Cond: ((id = sspg.product_group_id) AND (release_id = 390))
  • Filter: (deleted_flag = 0)
  • Rows Removed by Filter: 0
35. 83.238 83.238 ↓ 1.6 11 14,689 / 3

Index Scan using product_group_product_pk on product_group_product pgp (cost=0.42..20.76 rows=7 width=24) (actual time=0.008..0.017 rows=11 loops=14,689)

  • Index Cond: ((product_group_id = sspg.product_group_id) AND (release_id = 390))
  • Filter: (deleted_flag = 0)
  • Rows Removed by Filter: 11
Planning time : 89.994 ms
Execution time : 2,378.824 ms