explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vZu

Settings
# exclusive inclusive rows x rows loops node
1. 153.396 153.396 ↓ 438.0 438 1

CTE Scan on translated_prop_value (cost=53,830.78..53,830.80 rows=1 width=40) (actual time=153.297..153.396 rows=438 loops=1)

2.          

CTE features

3. 0.168 1.334 ↓ 9.3 926 1

Nested Loop (cost=2.18..791.51 rows=100 width=55) (actual time=0.180..1.334 rows=926 loops=1)

4. 0.182 0.240 ↓ 9.3 926 1

HashAggregate (cost=1.76..2.76 rows=100 width=4) (actual time=0.173..0.240 rows=926 loops=1)

  • Group Key: unnest('{29268,29272,29274,29276,29284,29288,29290,29292,29293,29294,29295,29296,29297,29298,29300,29306,29314,29319,29327,29332,29334,29336,29338,29340,29342,29345,29348,29352,29355,29361,29363,29366,29368,29370,29372,29376,2937 (...)
5. 0.058 0.058 ↓ 9.3 926 1

Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.006..0.058 rows=926 loops=1)

6. 0.926 0.926 ↑ 1.0 1 926

Index Scan using feature_pkey on feature f (cost=0.42..7.88 rows=1 width=55) (actual time=0.001..0.001 rows=1 loops=926)

  • Index Cond: (id = (unnest('{29268,29272,29274,29276,29284,29288,29290,29292,29293,29294,29295,29296,29297,29298,29300,29306,29314,29319,29327,29332,29334,29336,29338,29340,29342,29345,29348,29352,29355,29361,29363,29366,29368,29370,29372,29 (...)
7.          

CTE data_class_rule

8. 0.000 0.000 ↓ 0.0 0

Recursive Union (cost=3.25..524.70 rows=234 width=20) (never executed)

9. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=3.25..9.25 rows=154 width=16) (never executed)

  • Hash Cond: (nr.data_class_id = f_1.data_class_id)
10. 0.000 0.000 ↓ 0.0 0

Seq Scan on name_rule nr (cost=0.00..3.79 rows=179 width=16) (never executed)

11. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.00..2.00 rows=100 width=4) (never executed)

12. 0.000 0.000 ↓ 0.0 0

CTE Scan on features f_1 (cost=0.00..2.00 rows=100 width=4) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=31.05..51.08 rows=8 width=20) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=30.90..49.31 rows=8 width=8) (never executed)

  • Hash Cond: (dc.id = dcr.data_class_id)
15. 0.000 0.000 ↓ 0.0 0

Seq Scan on data_class dc (cost=0.00..15.51 rows=751 width=8) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Hash (cost=30.80..30.80 rows=8 width=8) (never executed)

17. 0.000 0.000 ↓ 0.0 0

WorkTable Scan on data_class_rule dcr (cost=0.00..30.80 rows=8 width=8) (never executed)

  • Filter: (priority IS NULL)
18. 0.000 0.000 ↓ 0.0 0

Index Scan using name_rule_data_class_id_priority_key on name_rule nr_1 (cost=0.14..0.20 rows=2 width=16) (never executed)

  • Index Cond: (data_class_id = dc.base_id)
19.          

CTE values

20. 6.896 119.962 ↑ 1.4 27,377 1

Nested Loop (cost=12.58..43,657.31 rows=39,126 width=63) (actual time=66.082..119.962 rows=27,377 loops=1)

21. 1.860 89.011 ↑ 1.5 24,055 1

Nested Loop (cost=12.15..22,830.11 rows=36,737 width=16) (actual time=66.075..89.011 rows=24,055 loops=1)

22. 1.266 67.825 ↓ 12.9 6,442 1

Hash Join (cost=3.50..22.25 rows=500 width=8) (actual time=66.057..67.825 rows=6,442 loops=1)

  • Hash Cond: (mpv.feature_id = f_2.id)
23. 64.858 64.858 ↓ 6.4 6,442 1

Function Scan on d_get_features_snapshot_values mpv (cost=0.26..10.26 rows=1,000 width=8) (actual time=64.348..64.858 rows=6,442 loops=1)

24. 0.096 1.701 ↓ 9.3 926 1

Hash (cost=2.00..2.00 rows=100 width=4) (actual time=1.701..1.701 rows=926 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
25. 1.605 1.605 ↓ 9.3 926 1

CTE Scan on features f_2 (cost=0.00..2.00 rows=100 width=4) (actual time=0.181..1.605 rows=926 loops=1)

26. 6.442 19.326 ↑ 18.2 4 6,442

Bitmap Heap Scan on value v (cost=8.65..44.89 rows=73 width=8) (actual time=0.002..0.003 rows=4 loops=6,442)

  • Recheck Cond: ((id = mpv.id) OR (top = mpv.id))
  • Heap Blocks: exact=6579
27. 0.000 12.884 ↓ 0.0 0 6,442

BitmapOr (cost=8.65..8.65 rows=73 width=0) (actual time=0.002..0.002 rows=0 loops=6,442)

28. 6.442 6.442 ↑ 1.0 1 6,442

Bitmap Index Scan on value_pkey (cost=0.00..4.04 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=6,442)

  • Index Cond: (id = mpv.id)
29. 6.442 6.442 ↑ 24.0 3 6,442

Bitmap Index Scan on value_top_idx (cost=0.00..4.57 rows=72 width=0) (actual time=0.001..0.001 rows=3 loops=6,442)

  • Index Cond: (top = mpv.id)
30. 24.055 24.055 ↑ 1.0 1 24,055

Index Scan using simple_value_value_id_idx on simple_value sv (cost=0.43..0.56 rows=1 width=51) (actual time=0.001..0.001 rows=1 loops=24,055)

  • Index Cond: (value_id = v.id)
31.          

CTE annotation_values

32. 132.222 132.222 ↓ 2.6 507 1

CTE Scan on values v_1 (cost=0.00..880.33 rows=196 width=88) (actual time=66.128..132.222 rows=507 loops=1)

  • Filter: (short_xpath = 'annotation'::ltree)
  • Rows Removed by Filter: 26870
33.          

CTE translated_prop_value

34. 0.142 153.325 ↓ 438.0 438 1

HashAggregate (cost=7,976.92..7,976.93 rows=1 width=8) (actual time=153.294..153.325 rows=438 loops=1)

  • Group Key: prop_lang.feature_id, prop_lang.id, 'some'::text
35. 0.515 153.183 ↓ 438.0 438 1

Nested Loop (cost=2,318.39..7,976.91 rows=1 width=8) (actual time=134.669..153.183 rows=438 loops=1)

36. 0.860 150.034 ↓ 2,634.0 2,634 1

Hash Join (cost=2,317.96..7,976.34 rows=1 width=12) (actual time=134.662..150.034 rows=2,634 loops=1)

  • Hash Cond: (prop_lang.value = (lang.let2)::text)
37. 0.989 149.168 ↓ 177.2 5,316 1

Nested Loop (cost=2,316.93..7,975.14 rows=30 width=17) (actual time=134.649..149.168 rows=5,316 loops=1)

38. 0.935 147.289 ↓ 890.0 890 1

Nested Loop (cost=2,316.50..7,972.70 rows=1 width=29) (actual time=134.645..147.289 rows=890 loops=1)

39. 1.079 141.038 ↓ 177.2 5,316 1

Nested Loop (cost=2,316.07..7,955.62 rows=30 width=33) (actual time=134.641..141.038 rows=5,316 loops=1)

40. 0.538 139.069 ↓ 890.0 890 1

Nested Loop (cost=2,315.64..7,953.18 rows=1 width=25) (actual time=134.637..139.069 rows=890 loops=1)

41. 0.380 137.641 ↓ 890.0 890 1

Nested Loop (cost=2,315.22..7,952.61 rows=1 width=37) (actual time=134.628..137.641 rows=890 loops=1)

  • Join Filter: (prop_lang.value_id = v2.id)
42. 0.494 136.371 ↓ 63.6 890 1

Hash Join (cost=2,314.79..7,945.72 rows=14 width=29) (actual time=134.618..136.371 rows=890 loops=1)

  • Hash Cond: (prop_lang.value_id = v1.id)
43. 1.380 1.997 ↓ 2.1 3,451 1

Bitmap Heap Scan on simple_value prop_lang (cost=117.43..5,741.92 rows=1,680 width=17) (actual time=0.697..1.997 rows=3,451 loops=1)

  • Recheck Cond: (short_xpath = 'annotation.translatedNote.note.0lang'::ltree)
  • Heap Blocks: exact=1102
44. 0.617 0.617 ↓ 2.1 3,451 1

Bitmap Index Scan on simple_value_short_xpath_idx (cost=0.00..117.01 rows=1,680 width=0) (actual time=0.617..0.617 rows=3,451 loops=1)

  • Index Cond: (short_xpath = 'annotation.translatedNote.note.0lang'::ltree)
45. 0.478 133.880 ↑ 5.1 2,794 1

Hash (cost=2,019.79..2,019.79 rows=14,205 width=12) (actual time=133.880..133.880 rows=2,794 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 249kB
46. 0.589 133.402 ↑ 5.1 2,794 1

Nested Loop (cost=0.43..2,019.79 rows=14,205 width=12) (actual time=66.132..133.402 rows=2,794 loops=1)

47. 132.306 132.306 ↓ 2.6 507 1

CTE Scan on annotation_values av (cost=0.00..3.92 rows=196 width=4) (actual time=66.129..132.306 rows=507 loops=1)

48. 0.507 0.507 ↑ 12.0 6 507

Index Scan using value_top_idx on value v1 (cost=0.43..9.57 rows=72 width=8) (actual time=0.001..0.001 rows=6 loops=507)

  • Index Cond: (top = av.value_id)
49. 0.890 0.890 ↑ 1.0 1 890

Index Scan using value_pkey on value v2 (cost=0.43..0.48 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=890)

  • Index Cond: (id = v1.id)
  • Filter: (v1.top = top)
50. 0.890 0.890 ↑ 1.0 1 890

Index Scan using simple_value_value_id_idx on simple_value prop_translation (cost=0.43..0.56 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=890)

  • Index Cond: (value_id = v2.id)
  • Filter: (short_xpath = 'annotation.translatedNote.note'::ltree)
  • Rows Removed by Filter: 1
51. 0.890 0.890 ↑ 12.0 6 890

Index Scan using value_top_idx on value v3 (cost=0.43..1.72 rows=72 width=8) (actual time=0.001..0.001 rows=6 loops=890)

  • Index Cond: (top = v1.top)
52. 5.316 5.316 ↓ 0.0 0 5,316

Index Scan using simple_value_value_id_idx on simple_value prop_name (cost=0.43..0.56 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=5,316)

  • Index Cond: (value_id = v3.id)
  • Filter: (short_xpath = 'annotation.propertyName'::ltree)
  • Rows Removed by Filter: 1
53. 0.890 0.890 ↑ 12.0 6 890

Index Scan using value_top_idx on value v4 (cost=0.43..1.72 rows=72 width=8) (actual time=0.001..0.001 rows=6 loops=890)

  • Index Cond: (top = v1.top)
54. 0.002 0.006 ↑ 1.0 1 1

Hash (cost=1.02..1.02 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
55. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on language lang (cost=0.00..1.02 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=1)

  • Filter: (id = 1)
  • Rows Removed by Filter: 1
56. 2.634 2.634 ↓ 0.0 0 2,634

Index Scan using simple_value_value_id_idx on simple_value prop_purpose (cost=0.43..0.56 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=2,634)

  • Index Cond: (value_id = v4.id)
  • Filter: ((short_xpath = 'annotation.purpose'::ltree) AND (value = 'OTHER:TRANSLATION'::text))
  • Rows Removed by Filter: 1