explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RNTE

Settings
# exclusive inclusive rows x rows loops node
1. 0.064 44.215 ↑ 1.0 1 1

Aggregate (cost=6,147.10..6,147.11 rows=1 width=32) (actual time=44.215..44.215 rows=1 loops=1)

2.          

CTE structure_thesaurus

3. 0.528 6.088 ↓ 2.1 1,500 1

Recursive Union (cost=0.99..179.19 rows=721 width=27) (actual time=0.111..6.088 rows=1,500 loops=1)

4. 0.002 0.106 ↑ 1.0 1 1

Nested Loop (cost=0.99..1.65 rows=1 width=27) (actual time=0.106..0.106 rows=1 loops=1)

5. 0.002 0.055 ↑ 1.0 1 1

Nested Loop (cost=0.57..1.01 rows=1 width=8) (actual time=0.055..0.055 rows=1 loops=1)

6. 0.039 0.039 ↑ 1.0 1 1

Index Scan using structure_mapping_pkey on structure_mapping sm_1 (cost=0.29..0.51 rows=1 width=4) (actual time=0.039..0.039 rows=1 loops=1)

  • Index Cond: (id = 14075)
7. 0.014 0.014 ↑ 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.014..0.014 rows=1 loops=1)

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

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

  • Index Cond: (id = s_1.channel_thesaurus_line_id)
9. 1.005 5.454 ↓ 6.9 500 3

Nested Loop (cost=0.71..16.31 rows=72 width=27) (actual time=1.127..1.818 rows=500 loops=3)

10. 0.000 1.449 ↓ 50.0 500 3

Nested Loop (cost=0.28..4.17 rows=10 width=8) (actual time=0.014..0.483 rows=500 loops=3)

11. 0.072 0.072 ↓ 50.0 500 3

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

12. 1.500 1.500 ↑ 1.0 1 1,500

Index Only Scan using structure_pkey on structure s_2 (cost=0.28..0.39 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,500)

  • Index Cond: (id = st.channel_id)
  • Heap Fetches: 0
13. 3.000 3.000 ↑ 7.0 1 1,500

Index Scan using thesaurus_line_parent_id_idx on thesaurus_line tl_1 (cost=0.43..1.14 rows=7 width=23) (actual time=0.002..0.002 rows=1 loops=1,500)

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

CTE mapped_with_catalog

15. 0.171 7.504 ↑ 16.2 378 1

Recursive Union (cost=23.99..480.92 rows=6,107 width=52) (actual time=6.641..7.504 rows=378 loops=1)

16. 0.054 6.833 ↓ 11.1 189 1

Hash Join (cost=23.99..71.54 rows=17 width=52) (actual time=6.638..6.833 rows=189 loops=1)

  • Hash Cond: (cm.channel_thesaurus_line_id = tl_2.id)
17. 0.210 0.210 ↑ 2.3 189 1

Index Only Scan using catalog_category_mapping_ui on category_mapping cm (cost=0.56..8.34 rows=428 width=8) (actual time=0.052..0.210 rows=189 loops=1)

  • Index Cond: (catalog_feed_id = 14075)
  • Heap Fetches: 27
18. 0.173 6.569 ↓ 2.1 1,500 1

Hash (cost=14.42..14.42 rows=721 width=44) (actual time=6.569..6.569 rows=1,500 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 93kB
19. 6.396 6.396 ↓ 2.1 1,500 1

CTE Scan on structure_thesaurus tl_2 (cost=0.00..14.42 rows=721 width=44) (actual time=0.111..6.396 rows=1,500 loops=1)

20. 0.148 0.500 ↑ 6.5 94 2

Hash Join (cost=5.53..28.72 rows=609 width=52) (actual time=0.144..0.250 rows=94 loops=2)

  • Hash Cond: (tl_3.id = mwc.parent_id)
21. 0.274 0.274 ↓ 2.1 1,499 2

CTE Scan on structure_thesaurus tl_3 (cost=0.00..14.42 rows=717 width=40) (actual time=0.001..0.137 rows=1,499 loops=2)

  • Filter: (parent_id IS NOT NULL)
  • Rows Removed by Filter: 1
22. 0.044 0.078 ↓ 1.1 189 2

Hash (cost=3.40..3.40 rows=170 width=16) (actual time=0.039..0.039 rows=189 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
23. 0.034 0.034 ↓ 1.1 189 2

WorkTable Scan on mapped_with_catalog mwc (cost=0.00..3.40 rows=170 width=16) (actual time=0.001..0.017 rows=189 loops=2)

24.          

CTE linked_with_fields

25. 0.000 8.271 ↓ 0.0 0 1

Nested Loop (cost=0.43..4,839.16 rows=256 width=509) (actual time=8.271..8.271 rows=0 loops=1)

26. 7.515 8.271 ↓ 0.0 0 1

CTE Scan on mapped_with_catalog mwc_1 (cost=0.00..2,848.89 rows=3,069 width=52) (actual time=8.271..8.271 rows=0 loops=1)

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

SubPlan (forCTE Scan)

28. 0.756 0.756 ↓ 0.0 0 378

Index Only Scan using product_13876_catalog_category_id_idx on product_13876 p (cost=0.42..2,543.70 rows=87,713 width=0) (actual time=0.002..0.002 rows=0 loops=378)

  • Index Cond: (catalog_category_id = mwc_1.catalog_category_id)
  • Heap Fetches: 0
29. 0.000 0.000 ↓ 0.0 0

Index Only Scan using product_13876_catalog_category_id_idx on product_13876 p_1 (cost=0.42..2,324.41 rows=87,713 width=4) (never executed)

  • Heap Fetches: 0
30. 0.000 0.000 ↓ 0.0 0

Index Scan using structure_fields_t_channel_structure_id_conditions_idx on structure_fields sf_1 (cost=0.43..0.64 rows=1 width=457) (never executed)

  • Index Cond: ((channel_structure_id = mwc_1.channel_id) AND (conditions[array_length(conditions, 1)] = mwc_1.channel_code))
  • Filter: (NOT hidden)
31. 0.068 44.151 ↑ 7.1 28 1

Merge Right Join (cost=621.23..643.84 rows=200 width=77) (actual time=43.960..44.151 rows=28 loops=1)

  • Merge Cond: (caa.channel_structure_field_id = lwf.field_id)
32. 0.059 5.843 ↑ 3.0 309 1

Unique (cost=438.90..443.54 rows=928 width=121) (actual time=5.726..5.843 rows=309 loops=1)

33. 0.512 5.784 ↑ 2.3 409 1

Sort (cost=438.90..441.22 rows=928 width=121) (actual time=5.726..5.784 rows=409 loops=1)

  • Sort Key: caa.channel_structure_field_id
  • Sort Method: quicksort Memory: 48kB
34. 0.148 5.272 ↑ 1.7 553 1

Nested Loop (cost=0.84..393.16 rows=928 width=121) (actual time=0.098..5.272 rows=553 loops=1)

35. 1.628 1.628 ↑ 1.3 184 1

Index Scan using catalog_rule_catalog_feed_id_idx on rule car (cost=0.42..23.07 rows=243 width=4) (actual time=0.062..1.628 rows=184 loops=1)

  • Index Cond: (catalog_feed_id = 14075)
36. 3.496 3.496 ↑ 6.3 3 184

Index Scan using catalog_action_catalog_rule_id_idx on action caa (cost=0.42..1.33 rows=19 width=17) (actual time=0.010..0.019 rows=3 loops=184)

  • Index Cond: (catalog_rule_id = car.id)
37. 0.003 38.240 ↑ 7.1 28 1

Materialize (cost=182.34..186.20 rows=200 width=64) (actual time=38.231..38.240 rows=28 loops=1)

38. 0.006 38.237 ↑ 7.1 28 1

Unique (cost=182.34..183.70 rows=200 width=64) (actual time=38.229..38.237 rows=28 loops=1)

39. 0.046 38.231 ↑ 9.8 28 1

Sort (cost=182.34..183.02 rows=273 width=64) (actual time=38.228..38.231 rows=28 loops=1)

  • Sort Key: lwf.field_id
  • Sort Method: quicksort Memory: 27kB
40. 0.094 38.185 ↑ 9.8 28 1

Nested Loop Left Join (cost=53.25..171.29 rows=273 width=64) (actual time=38.004..38.185 rows=28 loops=1)

41. 0.027 37.923 ↑ 9.8 28 1

HashAggregate (cost=52.97..55.70 rows=273 width=68) (actual time=37.918..37.923 rows=28 loops=1)

  • Group Key: lwf.field_id, lwf.use, lwf.channel_structure_id
42. 0.004 37.896 ↑ 9.8 28 1

Append (cost=0.00..50.92 rows=273 width=68) (actual time=37.857..37.896 rows=28 loops=1)

43. 8.272 8.272 ↓ 0.0 0 1

CTE Scan on linked_with_fields lwf (cost=0.00..5.12 rows=255 width=68) (actual time=8.272..8.272 rows=0 loops=1)

  • Filter: (field_id IS NOT NULL)
44. 0.006 29.620 ↓ 1.6 28 1

Nested Loop (cost=0.99..43.07 rows=18 width=40) (actual time=29.583..29.620 rows=28 loops=1)

45. 0.002 0.015 ↑ 1.0 1 1

Nested Loop (cost=0.57..1.01 rows=1 width=8) (actual time=0.012..0.015 rows=1 loops=1)

46. 0.008 0.008 ↑ 1.0 1 1

Index Scan using structure_mapping_pkey on structure_mapping sm (cost=0.29..0.51 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (id = 14075)
47. 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.003..0.005 rows=1 loops=1)

  • Index Cond: (id = sm.channel_structure_id)
  • Heap Fetches: 0
48. 29.599 29.599 ↓ 3.5 28 1

Index Scan using structure_fields_t_channel_structure_id_conditions_idx on structure_fields sf (cost=0.43..41.98 rows=8 width=40) (actual time=29.569..29.599 rows=28 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: 6945
49. 0.168 0.168 ↓ 0.0 0 28

Index Scan using structure_override_item on structure_override ov (cost=0.29..0.40 rows=1 width=10) (actual time=0.006..0.006 rows=0 loops=28)

  • Index Cond: ((channel_structure_id = lwf.channel_structure_id) AND (field_id = lwf.field_id))