explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Mcuw

Settings
# exclusive inclusive rows x rows loops node
1. 1.537 47.255 ↑ 1.0 1 1

Aggregate (cost=2,866.57..2,866.58 rows=1 width=32) (actual time=47.255..47.255 rows=1 loops=1)

  • Index Cond: ((channel_structure_id = mwc_1.channel_id) AND (conditions[array_length(conditions, 1)] = mwc_1.channel_code))
2.          

CTE structure_thesaurus

3. 0.817 7.373 ↑ 1.7 419 1

Recursive Union (cost=0.99..160.14 rows=721 width=26) (actual time=0.042..7.373 rows=419 loops=1)

4. 0.001 0.043 ↑ 1.0 1 1

Nested Loop (cost=0.99..1.64 rows=1 width=26) (actual time=0.036..0.043 rows=1 loops=1)

5. 0.003 0.026 ↑ 1.0 1 1

Nested Loop (cost=0.56..1.00 rows=1 width=8) (actual time=0.024..0.026 rows=1 loops=1)

6. 0.013 0.013 ↑ 1.0 1 1

Index Scan using structure_mapping_pkey on structure_mapping sm_1 (cost=0.28..0.49 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1)

  • Index Cond: (id = 1284)
7. 0.010 0.010 ↑ 1.0 1 1

Index Scan using structure_pkey on structure s_1 (cost=0.28..0.50 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (id = sm_1.channel_structure_id)
8. 0.016 0.016 ↑ 1.0 1 1

Index Scan using thesaurus_line_pkey on thesaurus_line tl (cost=0.43..0.63 rows=1 width=22) (actual time=0.011..0.016 rows=1 loops=1)

  • Index Cond: (id = s_1.channel_thesaurus_line_id)
9. 0.541 6.513 ↓ 1.9 139 3

Nested Loop (cost=0.71..14.41 rows=72 width=26) (actual time=1.581..2.171 rows=139 loops=3)

10. 0.809 3.039 ↓ 14.0 140 3

Nested Loop (cost=0.28..4.98 rows=10 width=8) (actual time=0.010..1.013 rows=140 loops=3)

11. 0.135 0.135 ↓ 14.0 140 3

WorkTable Scan on structure_thesaurus st (cost=0.00..0.20 rows=10 width=8) (actual time=0.000..0.045 rows=140 loops=3)

12. 2.095 2.095 ↑ 1.0 1 419

Index Only Scan using structure_pkey on structure s_2 (cost=0.28..0.47 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=419)

  • Index Cond: (id = st.channel_id)
  • Heap Fetches: 419
13. 2.933 2.933 ↑ 7.0 1 419

Index Scan using thesaurus_line_parent_id_idx on thesaurus_line tl_1 (cost=0.43..0.87 rows=7 width=22) (actual time=0.005..0.007 rows=1 loops=419)

  • Index Cond: (parent_id = st.id)
14.          

CTE mapped_with_catalog

15. 0.053 8.618 ↑ 161.9 20 1

Recursive Union (cost=23.72..324.16 rows=3,239 width=52) (actual time=8.213..8.618 rows=20 loops=1)

16. 0.048 8.263 ↓ 1.1 10 1

Hash Join (cost=23.72..26.74 rows=9 width=52) (actual time=8.208..8.263 rows=10 loops=1)

  • Hash Cond: (cm.channel_thesaurus_line_id = tl_2.id)
17. 0.052 0.052 ↑ 1.0 20 1

Index Only Scan using catalog_category_mapping_ui on category_mapping cm (cost=0.28..1.37 rows=20 width=8) (actual time=0.020..0.052 rows=20 loops=1)

  • Index Cond: (catalog_feed_id = 1284)
  • Heap Fetches: 18
18. 0.287 8.163 ↑ 1.7 419 1

Hash (cost=14.42..14.42 rows=721 width=44) (actual time=8.162..8.163 rows=419 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
19. 7.876 7.876 ↑ 1.7 419 1

CTE Scan on structure_thesaurus tl_2 (cost=0.00..14.42 rows=721 width=44) (actual time=0.045..7.876 rows=419 loops=1)

20. 0.098 0.302 ↑ 64.6 5 2

Hash Join (cost=2.92..23.26 rows=323 width=52) (actual time=0.086..0.151 rows=5 loops=2)

  • Hash Cond: (tl_3.id = mwc.parent_id)
21. 0.182 0.182 ↑ 1.7 418 2

CTE Scan on structure_thesaurus tl_3 (cost=0.00..14.42 rows=717 width=40) (actual time=0.002..0.091 rows=418 loops=2)

  • Filter: (parent_id IS NOT NULL)
  • Rows Removed by Filter: 1
22. 0.014 0.022 ↑ 9.0 10 2

Hash (cost=1.80..1.80 rows=90 width=16) (actual time=0.011..0.011 rows=10 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.008 0.008 ↑ 9.0 10 2

WorkTable Scan on mapped_with_catalog mwc (cost=0.00..1.80 rows=90 width=16) (actual time=0.002..0.004 rows=10 loops=2)

24.          

CTE linked_with_fields

25. 0.541 12.754 ↓ 10.2 890 1

Nested Loop (cost=0.55..2,334.61 rows=87 width=506) (actual time=9.937..12.754 rows=890 loops=1)

26. 9.709 10.341 ↑ 90.4 18 1

CTE Scan on mapped_with_catalog mwc_1 (cost=0.00..1,074.27 rows=1,628 width=52) (actual time=9.880..10.341 rows=18 loops=1)

  • Filter: ((catalog_category_id IS NULL) OR (alternatives: SubPlan 3 or hashed SubPlan 4))
  • Rows Removed by Filter: 2
27.          

SubPlan (forCTE Scan)

28. 0.000 0.000 ↓ 0.0 0

Index Only Scan using product_1960_catalog_category_id_idx on product_1960 p (cost=0.28..0.48 rows=6 width=0) (never executed)

  • Index Cond: (catalog_category_id = mwc_1.catalog_category_id)
  • Heap Fetches: 0
29. 0.632 0.632 ↑ 1.0 1,629 1

Index Only Scan using product_1960_catalog_category_id_idx on product_1960 p_1 (cost=0.28..27.11 rows=1,629 width=4) (actual time=0.011..0.632 rows=1,629 loops=1)

  • Heap Fetches: 0
30. 1.872 1.872 ↓ 49.0 49 18

Index Scan using structure_fields_new_channel_structure_id_conditions_idx on structure_fields sf_1 (cost=0.55..0.76 rows=1 width=454) (actual time=0.024..0.104 rows=49 loops=18)

  • Filter: (NOT hidden)
31. 0.469 45.718 ↓ 10.2 995 1

Merge Left Join (cost=43.78..45.71 rows=98 width=80) (actual time=44.908..45.718 rows=995 loops=1)

  • Merge Cond: (lwf.field_id = caa.channel_structure_field_id)
32. 0.226 45.198 ↓ 10.2 995 1

Unique (cost=41.92..42.41 rows=98 width=64) (actual time=44.858..45.198 rows=995 loops=1)

33. 4.864 44.972 ↓ 10.2 995 1

Sort (cost=41.92..42.17 rows=98 width=64) (actual time=44.858..44.972 rows=995 loops=1)

  • Sort Key: lwf.field_id
  • Sort Method: quicksort Memory: 128kB
34. 0.193 40.108 ↓ 10.2 995 1

Hash Right Join (cost=36.06..38.68 rows=98 width=64) (actual time=39.966..40.108 rows=995 loops=1)

  • Hash Cond: ((ov.field_id = lwf.field_id) AND (ov.channel_structure_id = lwf.channel_structure_id))
35. 0.022 0.022 ↓ 1.2 113 1

Seq Scan on structure_override ov (cost=0.00..1.38 rows=98 width=11) (actual time=0.007..0.022 rows=113 loops=1)

36. 0.319 39.893 ↓ 10.2 995 1

Hash (cost=34.59..34.59 rows=98 width=68) (actual time=39.893..39.893 rows=995 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 91kB
37. 1.412 39.574 ↓ 10.2 995 1

HashAggregate (cost=32.63..33.61 rows=98 width=68) (actual time=39.384..39.574 rows=995 loops=1)

  • Group Key: lwf.field_id, lwf.use, lwf.channel_structure_id
38. 0.304 38.162 ↓ 10.8 1,057 1

Append (cost=0.00..31.90 rows=98 width=68) (actual time=9.948..38.162 rows=1,057 loops=1)

39. 16.065 16.065 ↓ 10.2 890 1

CTE Scan on linked_with_fields lwf (cost=0.00..1.74 rows=87 width=68) (actual time=9.947..16.065 rows=890 loops=1)

  • Filter: (field_id IS NOT NULL)
40. 0.030 21.793 ↓ 15.2 167 1

Nested Loop (cost=0.98..29.18 rows=11 width=43) (actual time=3.033..21.793 rows=167 loops=1)

41. 0.001 0.011 ↑ 1.0 1 1

Nested Loop (cost=0.56..1.00 rows=1 width=8) (actual time=0.008..0.011 rows=1 loops=1)

42. 0.006 0.006 ↑ 1.0 1 1

Index Scan using structure_mapping_pkey on structure_mapping sm (cost=0.28..0.49 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=1)

  • Index Cond: (id = 1284)
43. 0.004 0.004 ↑ 1.0 1 1

Index Only Scan using structure_pkey on structure s (cost=0.28..0.50 rows=1 width=4) (actual time=0.002..0.004 rows=1 loops=1)

  • Index Cond: (id = sm.channel_structure_id)
  • Heap Fetches: 1
44. 21.752 21.752 ↓ 33.4 167 1

Index Scan using structure_fields_channel_structure_id_name_idx on structure_fields sf (cost=0.42..28.13 rows=5 width=43) (actual time=3.023..21.752 rows=167 loops=1)

  • Index Cond: (channel_structure_id = s.id)
  • Filter: ((NOT hidden) AND (field_id IS NOT NULL) AND ((conditions)::text = '{}'::text))
  • Rows Removed by Filter: 18876
45. 0.004 0.051 ↑ 1.2 6 1

Materialize (cost=1.86..1.98 rows=7 width=16) (actual time=0.045..0.051 rows=6 loops=1)

46. 0.006 0.047 ↑ 1.2 6 1

Unique (cost=1.86..1.90 rows=7 width=124) (actual time=0.042..0.047 rows=6 loops=1)

47. 0.021 0.041 ↑ 1.2 6 1

Sort (cost=1.86..1.88 rows=7 width=124) (actual time=0.040..0.041 rows=6 loops=1)

  • Sort Key: caa.channel_structure_field_id
  • Sort Method: quicksort Memory: 25kB
48. 0.006 0.020 ↑ 1.2 6 1

Nested Loop (cost=0.57..1.76 rows=7 width=124) (actual time=0.017..0.020 rows=6 loops=1)

49. 0.007 0.007 ↑ 2.0 1 1

Index Scan using rule_catalog_feed_id_idx on rule car (cost=0.28..0.52 rows=2 width=4) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (catalog_feed_id = 1284)
50. 0.007 0.007 ↓ 1.5 6 1

Index Scan using action_catalog_rule_id_idx on action caa (cost=0.29..0.58 rows=4 width=20) (actual time=0.005..0.007 rows=6 loops=1)

  • Index Cond: (catalog_rule_id = car.id)
Planning time : 5.958 ms
Execution time : 48.173 ms