explain.depesz.com

PostgreSQL's explain analyze made readable

Result: azsA

Settings
# exclusive inclusive rows x rows loops node
1. 1.526 37.759 ↑ 1.0 1 1

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

2.          

CTE structure_thesaurus

3. 0.646 3.747 ↑ 1.7 419 1

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

4. 0.002 0.044 ↑ 1.0 1 1

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

5. 0.004 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.009 0.009 ↑ 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.008..0.009 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.130 3.057 ↓ 1.9 139 3

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

10. 0.356 1.251 ↓ 14.0 140 3

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

11. 0.057 0.057 ↓ 14.0 140 3

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

12. 0.838 0.838 ↑ 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.002..0.002 rows=1 loops=419)

  • Index Cond: (id = st.channel_id)
  • Heap Fetches: 419
13. 1.676 1.676 ↑ 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.002..0.004 rows=1 loops=419)

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

CTE mapped_with_catalog

15. 0.025 4.704 ↑ 161.9 20 1

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

16. 0.036 4.447 ↓ 1.1 10 1

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

  • Hash Cond: (cm.channel_thesaurus_line_id = tl_2.id)
17. 0.031 0.031 ↑ 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.031 rows=20 loops=1)

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

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

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

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

20. 0.088 0.232 ↑ 64.6 5 2

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

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

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

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

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

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

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

24.          

CTE linked_with_fields

25. 0.970 6.280 ↓ 10.2 890 1

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

26. 5.081 5.310 ↑ 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=5.011..5.310 rows=18 loops=1)

  • Filter: ((catalog_category_id IS NULL) OR (alternatives: SubPlan 3 or hashed SubPlan 4))
  • Rows Removed by Filter: 2
  • Filter: (NOT hidden)
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.229 0.229 ↑ 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.004..0.229 rows=1,629 loops=1)

  • Heap Fetches: 0 Index Cond: ((channel_structure_id = mwc_1.channel_id) AND (conditions[array_length(conditions, 1)] = mwc_1.channel_code))
30. 0.473 36.233 ↓ 10.2 995 1

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

  • Merge Cond: (lwf.field_id = caa.channel_structure_field_id)
31. 0.175 35.712 ↓ 10.2 995 1

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

32. 4.797 35.537 ↓ 10.2 995 1

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

  • Sort Key: lwf.field_id
  • Sort Method: quicksort Memory: 128kB
33. 0.198 30.740 ↓ 10.2 995 1

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

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

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

35. 0.354 30.521 ↓ 10.2 995 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 91kB
36. 0.740 30.167 ↓ 10.2 995 1

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

  • Group Key: lwf.field_id, lwf.use, lwf.channel_structure_id
37. 0.116 29.427 ↓ 10.8 1,057 1

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

38. 7.573 7.573 ↓ 10.2 890 1

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

  • Filter: (field_id IS NOT NULL)
39. 0.030 21.738 ↓ 15.2 167 1

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

40. 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)

41. 0.005 0.005 ↑ 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.005 rows=1 loops=1)

  • Index Cond: (id = 1284)
42. 0.005 0.005 ↑ 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.005 rows=1 loops=1)

  • Index Cond: (id = sm.channel_structure_id)
  • Heap Fetches: 1
43. 21.697 21.697 ↓ 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.036..21.697 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
44. 0.000 0.048 ↑ 1.2 6 1

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

45. 0.002 0.044 ↑ 1.2 6 1

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

46. 0.022 0.042 ↑ 1.2 6 1

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

  • Sort Key: caa.channel_structure_field_id
  • Sort Method: quicksort Memory: 25kB
47. 0.007 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)

48. 0.008 0.008 ↑ 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.008..0.008 rows=1 loops=1)

  • Index Cond: (catalog_feed_id = 1284)
49. 0.005 0.005 ↓ 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.005 rows=6 loops=1)

  • Index Cond: (catalog_rule_id = car.id)
50. 0.738 0.738 ↓ 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.010..0.041 rows=49 loops=18)

Planning time : 5.998 ms
Execution time : 38.686 ms