explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TjUQ

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

CTE Scan on translated_prop_value (cost=45,858.56..45,858.58 rows=1 width=40) (actual time=1,015.691..1,015.809 rows=438 loops=1)

2.          

CTE features

3. 0.171 1.353 ↓ 9.3 926 1

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

4. 0.199 0.256 ↓ 9.3 926 1

HashAggregate (cost=1.76..2.76 rows=100 width=4) (actual time=0.179..0.256 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.057 0.057 ↓ 9.3 926 1

Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.005..0.057 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. 7.311 127.372 ↑ 1.4 27,377 1

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

21. 2.010 96.006 ↑ 1.5 24,055 1

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

22. 1.275 74.670 ↓ 12.9 6,442 1

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

  • Hash Cond: (mpv.feature_id = f_2.id)
23. 71.719 71.719 ↓ 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=71.227..71.719 rows=6,442 loops=1)

24. 0.077 1.676 ↓ 9.3 926 1

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

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

CTE Scan on features f_2 (cost=0.00..2.00 rows=100 width=4) (actual time=0.187..1.599 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. 139.656 139.656 ↓ 107.5 4,191 1

CTE Scan on values v_1 (cost=0.00..880.33 rows=39 width=88) (actual time=72.997..139.656 rows=4,191 loops=1)

  • Filter: (short_xpath <@ 'annotation'::ltree)
  • Rows Removed by Filter: 23186
33.          

CTE translated_prop_value

34. 0.438 1,015.738 ↓ 438.0 438 1

HashAggregate (cost=4.70..4.71 rows=1 width=8) (actual time=1,015.689..1,015.738 rows=438 loops=1)

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

Nested Loop (cost=0.00..4.69 rows=1 width=8) (actual time=73.027..1,015.300 rows=438 loops=1)

  • Join Filter: (prop_translation.value_top_id = prop_purpose.value_top_id)
  • Rows Removed by Join Filter: 197112
36. 10.814 662.973 ↓ 439.0 439 1

Nested Loop (cost=0.00..3.70 rows=1 width=20) (actual time=73.024..662.973 rows=439 loops=1)

  • Join Filter: (prop_translation.value_top_id = prop_name.value_top_id)
  • Rows Removed by Join Filter: 222134
37. 18.751 385.247 ↓ 439.0 439 1

Nested Loop (cost=0.00..2.81 rows=1 width=16) (actual time=73.021..385.247 rows=439 loops=1)

  • Join Filter: ((prop_lang.value_top_id = prop_translation.value_top_id) AND (prop_lang.value_id = prop_translation.value_id))
  • Rows Removed by Join Filter: 390271
38. 0.389 74.122 ↓ 439.0 439 1

Nested Loop (cost=0.00..1.92 rows=1 width=16) (actual time=73.017..74.122 rows=439 loops=1)

  • Join Filter: ((lang.let2)::text = prop_lang.value)
  • Rows Removed by Join Filter: 451
39. 0.007 0.007 ↑ 1.0 1 1

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

  • Filter: (id = 1)
  • Rows Removed by Filter: 1
40. 73.726 73.726 ↓ 890.0 890 1

CTE Scan on annotation_values prop_lang (cost=0.00..0.88 rows=1 width=48) (actual time=73.009..73.726 rows=890 loops=1)

  • Filter: (short_xpath = 'annotation.translatedNote.note.0lang'::ltree)
  • Rows Removed by Filter: 3301
41. 292.374 292.374 ↓ 890.0 890 439

CTE Scan on annotation_values prop_translation (cost=0.00..0.88 rows=1 width=8) (actual time=0.001..0.666 rows=890 loops=439)

  • Filter: (short_xpath = 'annotation.translatedNote.note'::ltree)
  • Rows Removed by Filter: 3301
42. 266.912 266.912 ↓ 507.0 507 439

CTE Scan on annotation_values prop_name (cost=0.00..0.88 rows=1 width=4) (actual time=0.000..0.608 rows=507 loops=439)

  • Filter: (short_xpath = 'annotation.propertyName'::ltree)
  • Rows Removed by Filter: 3684
43. 342.859 342.859 ↓ 450.0 450 439

CTE Scan on annotation_values prop_purpose (cost=0.00..0.98 rows=1 width=4) (actual time=0.001..0.781 rows=450 loops=439)

  • Filter: ((short_xpath = 'annotation.purpose'::ltree) AND (value = 'OTHER:TRANSLATION'::text))
  • Rows Removed by Filter: 3741