explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EUgC : RW mi cool

Settings
# exclusive inclusive rows x rows loops node
1. 0.276 18,484.822 ↑ 1.0 1 1

Aggregate (cost=4,577.52..4,577.53 rows=1 width=32) (actual time=18,484.821..18,484.822 rows=1 loops=1)

2.          

CTE structure_thesaurus

3. 0.698 7.138 ↓ 2.1 1,500 1

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

4. 0.004 0.134 ↑ 1.0 1 1

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

5. 0.005 0.088 ↑ 1.0 1 1

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

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

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

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

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

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

10. 0.396 1.983 ↓ 50.0 500 3

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

11. 0.087 0.087 ↓ 50.0 500 3

WorkTable Scan on structure_thesaurus st (cost=0.00..0.20 rows=10 width=8) (actual time=0.000..0.029 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. 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.762 12.331 ↑ 16.2 378 1

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

16. 0.733 9.265 ↓ 11.1 189 1

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

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

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

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

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

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

20. 0.852 1.304 ↑ 6.5 94 2

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

  • Hash Cond: (tl_3.id = mwc.parent_id)
21. 0.336 0.336 ↓ 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.168 rows=1,499 loops=2)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
23. 0.060 0.060 ↓ 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.030 rows=189 loops=2)

24.          

CTE linked_with_fields

25. 0.002 18,455.863 ↓ 0.0 0 1

Nested Loop (cost=0.43..3,269.57 rows=256 width=509) (actual time=18,455.863..18,455.863 rows=0 loops=1)

26. 15.131 18,455.861 ↓ 0.0 0 1

CTE Scan on mapped_with_catalog mwc_1 (cost=0.00..1,279.30 rows=3,069 width=52) (actual time=18,455.861..18,455.861 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. 18,440.730 18,440.730 ↓ 0.0 0 378

Seq Scan on product_13876 p (cost=0.00..15,951.30 rows=84,184 width=0) (actual time=48.785..48.785 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

Seq Scan on product_13876 p_1 (cost=0.00..15,740.84 rows=84,184 width=4) (never executed)

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.077 18,484.546 ↑ 7.1 28 1

Merge Right Join (cost=621.23..643.84 rows=200 width=77) (actual time=18,484.383..18,484.546 rows=28 loops=1)

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

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

33. 0.966 7.330 ↑ 2.3 409 1

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

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

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

35. 1.719 1.719 ↑ 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.045..1.719 rows=184 loops=1)

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

  • Index Cond: (catalog_rule_id = car.id)
37. 0.010 18,477.090 ↑ 7.1 28 1

Materialize (cost=182.34..186.20 rows=200 width=64) (actual time=18,477.076..18,477.090 rows=28 loops=1)

38. 0.008 18,477.080 ↑ 7.1 28 1

Unique (cost=182.34..183.70 rows=200 width=64) (actual time=18,477.070..18,477.080 rows=28 loops=1)

39. 0.050 18,477.072 ↑ 9.8 28 1

Sort (cost=182.34..183.02 rows=273 width=64) (actual time=18,477.069..18,477.072 rows=28 loops=1)

  • Sort Key: lwf.field_id
  • Sort Method: quicksort Memory: 27kB
40. 11.671 18,477.022 ↑ 9.8 28 1

Nested Loop Left Join (cost=53.25..171.29 rows=273 width=64) (actual time=18,465.300..18,477.022 rows=28 loops=1)

41. 0.027 18,465.267 ↑ 9.8 28 1

HashAggregate (cost=52.97..55.70 rows=273 width=68) (actual time=18,465.262..18,465.267 rows=28 loops=1)

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

Append (cost=0.00..50.92 rows=273 width=68) (actual time=18,465.213..18,465.240 rows=28 loops=1)

43. 18,455.864 18,455.864 ↓ 0.0 0 1

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

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

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

45. 0.007 0.037 ↑ 1.0 1 1

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

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

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

  • Index Cond: (id = sm.channel_structure_id)
  • Heap Fetches: 0
48. 9.329 9.329 ↓ 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=9.308..9.329 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))