explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W5iE

Settings
# exclusive inclusive rows x rows loops node
1. 0.652 141,494.895 ↑ 1.0 1 1

Aggregate (cost=6,016.73..6,016.74 rows=1 width=32) (actual time=141,494.895..141,494.895 rows=1 loops=1)

2.          

CTE structure_thesaurus

3. 0.935 12.875 ↓ 2.1 1,500 1

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

4. 0.004 0.159 ↑ 1.0 1 1

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

5. 0.007 0.103 ↑ 1.0 1 1

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

6. 0.055 0.055 ↑ 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.055..0.055 rows=1 loops=1)

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

  • Index Cond: (id = sm_1.channel_structure_id)
8. 0.052 0.052 ↑ 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.052..0.052 rows=1 loops=1)

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

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

10. 0.480 6.624 ↓ 50.0 500 3

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

11. 0.144 0.144 ↓ 50.0 500 3

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

12. 6.000 6.000 ↑ 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.004..0.004 rows=1 loops=1,500)

  • Index Cond: (id = st.channel_id)
  • Heap Fetches: 0
13. 4.500 4.500 ↑ 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.003 rows=1 loops=1,500)

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

CTE mapped_with_catalog

15. 1.880 22.095 ↑ 16.2 378 1

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

16. 0.766 18.619 ↓ 11.1 189 1

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

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

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

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

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

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

20. 0.872 1.596 ↑ 6.5 94 2

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

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

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

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

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

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

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

24.          

CTE linked_with_fields

25. 0.000 141,458.282 ↓ 0.0 0 1

Nested Loop (cost=0.43..4,714.04 rows=256 width=509) (actual time=141,458.282..141,458.282 rows=0 loops=1)

26. 25.046 141,458.282 ↓ 0.0 0 1

CTE Scan on mapped_with_catalog mwc_1 (cost=0.00..2,723.78 rows=3,069 width=52) (actual time=141,458.282..141,458.282 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. 141,433.236 141,433.236 ↓ 0.0 0 378

Seq Scan on product_13876 p (cost=0.00..37,366.51 rows=87,713 width=0) (actual time=374.162..374.162 rows=0 loops=378)

  • Filter: (catalog_category_id = mwc_1.catalog_category_id)
  • Rows Removed by Filter: 84184
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,323.51 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.130 141,494.243 ↑ 7.1 28 1

Merge Right Join (cost=615.97..638.57 rows=200 width=77) (actual time=141,493.947..141,494.243 rows=28 loops=1)

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

Unique (cost=433.63..438.27 rows=928 width=121) (actual time=12.304..12.462 rows=309 loops=1)

33. 1.354 12.377 ↑ 2.3 409 1

Sort (cost=433.63..435.95 rows=928 width=121) (actual time=12.302..12.377 rows=409 loops=1)

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

Nested Loop (cost=0.84..387.89 rows=928 width=121) (actual time=0.479..11.023 rows=553 loops=1)

35. 2.567 2.567 ↑ 1.3 184 1

Index Scan using catalog_rule_catalog_feed_id_idx on rule car (cost=0.42..17.80 rows=243 width=4) (actual time=0.430..2.567 rows=184 loops=1)

  • Index Cond: (catalog_feed_id = 14075)
36. 8.280 8.280 ↑ 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.028..0.045 rows=3 loops=184)

  • Index Cond: (catalog_rule_id = car.id)
37. 0.008 141,481.651 ↑ 7.1 28 1

Materialize (cost=182.34..186.20 rows=200 width=64) (actual time=141,481.636..141,481.651 rows=28 loops=1)

38. 0.009 141,481.643 ↑ 7.1 28 1

Unique (cost=182.34..183.70 rows=200 width=64) (actual time=141,481.630..141,481.643 rows=28 loops=1)

39. 0.076 141,481.634 ↑ 9.8 28 1

Sort (cost=182.34..183.02 rows=273 width=64) (actual time=141,481.629..141,481.634 rows=28 loops=1)

  • Sort Key: lwf.field_id
  • Sort Method: quicksort Memory: 27kB
40. 11.059 141,481.558 ↑ 9.8 28 1

Nested Loop Left Join (cost=53.25..171.29 rows=273 width=64) (actual time=141,470.426..141,481.558 rows=28 loops=1)

41. 0.038 141,470.415 ↑ 9.8 28 1

HashAggregate (cost=52.97..55.70 rows=273 width=68) (actual time=141,470.405..141,470.415 rows=28 loops=1)

  • Group Key: lwf.field_id, lwf.use, lwf.channel_structure_id
42. 0.007 141,470.377 ↑ 9.8 28 1

Append (cost=0.00..50.92 rows=273 width=68) (actual time=141,470.333..141,470.377 rows=28 loops=1)

43. 141,458.284 141,458.284 ↓ 0.0 0 1

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

  • Filter: (field_id IS NOT NULL)
44. 0.007 12.086 ↓ 1.6 28 1

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

45. 0.013 0.035 ↑ 1.0 1 1

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

46. 0.012 0.012 ↑ 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.011..0.012 rows=1 loops=1)

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

  • Index Cond: (id = sm.channel_structure_id)
  • Heap Fetches: 0
48. 12.044 12.044 ↓ 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=12.012..12.044 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.084 0.084 ↓ 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.003..0.003 rows=0 loops=28)

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