explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MZWB

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

CTE Scan on translated_prop_value (cost=49,529.94..49,529.96 rows=1 width=136) (actual time=22,509.578..22,510.686 rows=438 loops=1)

2.          

CTE features

3. 0.244 1.428 ↓ 9.3 926 1

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

4. 0.201 0.258 ↓ 9.3 926 1

HashAggregate (cost=1.76..2.76 rows=100 width=4) (actual time=0.176..0.258 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. 10.815 242.906 ↑ 1.4 27,377 1

Nested Loop (cost=12.58..43,657.31 rows=39,126 width=59) (actual time=184.298..242.906 rows=27,377 loops=1)

21. 2.617 208.036 ↑ 1.5 24,055 1

Nested Loop (cost=12.15..22,830.11 rows=36,737 width=12) (actual time=184.287..208.036 rows=24,055 loops=1)

22. 1.361 186.093 ↓ 12.9 6,442 1

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

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

24. 0.102 1.758 ↓ 9.3 926 1

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

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

CTE Scan on features f_2 (cost=0.00..2.00 rows=100 width=4) (actual time=0.190..1.656 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 translated_prop_value

32. 0.940 22,510.638 ↓ 438.0 438 1

Unique (cost=4,556.40..4,556.42 rows=1 width=168) (actual time=22,509.576..22,510.638 rows=438 loops=1)

33. 3.805 22,509.698 ↓ 3,942.0 3,942 1

Sort (cost=4,556.40..4,556.41 rows=1 width=168) (actual time=22,509.576..22,509.698 rows=3,942 loops=1)

  • Sort Key: v_1.feature_id, v_1.id, ((subltree(v_1.short_xpath, 0, index(v_1.short_xpath, 'annotation'::ltree)) || prop_name.value)), prop_lang.value, prop_translation.value
  • Sort Method: quicksort Memory: 651kB
34. 132.179 22,505.893 ↓ 3,942.0 3,942 1

Nested Loop (cost=978.16..4,556.39 rows=1 width=168) (actual time=255.365..22,505.893 rows=3,942 loops=1)

  • Join Filter: (v_1.id = prop_name.id)
  • Rows Removed by Join Filter: 1994652
35. 89.543 4,212.920 ↓ 3,942.0 3,942 1

Nested Loop (cost=978.16..3,675.56 rows=1 width=148) (actual time=255.354..4,212.920 rows=3,942 loops=1)

  • Join Filter: (prop_purpose.id = v_1.id)
  • Rows Removed by Join Filter: 1831716
36. 111.216 2,036.745 ↓ 438.0 438 1

Nested Loop (cost=978.16..2,794.74 rows=1 width=108) (actual time=255.347..2,036.745 rows=438 loops=1)

  • Join Filter: ((prop_translation.simple_value_id <> prop_lang.simple_value_id) AND (prop_purpose.id = prop_lang.id) AND (prop_translation.value_id = prop_lang.value_id))
  • Rows Removed by Join Filter: 1765722
37. 1.037 192.369 ↓ 440.0 440 1

Nested Loop (cost=0.00..881.94 rows=1 width=44) (actual time=184.373..192.369 rows=440 loops=1)

  • Join Filter: (prop_lang.value = (lang.let2)::text)
  • Rows Removed by Join Filter: 452
38. 0.010 0.010 ↑ 1.0 1 1

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

  • Filter: (id = 1)
  • Rows Removed by Filter: 1
39. 191.322 191.322 ↓ 22.9 892 1

CTE Scan on values prop_lang (cost=0.00..880.33 rows=39 width=44) (actual time=184.361..191.322 rows=892 loops=1)

  • Filter: (short_xpath ~ '*.note.0lang'::lquery)
  • Rows Removed by Filter: 26485
40. 925.642 1,733.160 ↓ 20.5 4,014 440

Hash Join (cost=978.16..1,909.37 rows=196 width=80) (actual time=0.164..3.939 rows=4,014 loops=440)

  • Hash Cond: (prop_translation.id = prop_purpose.id)
41. 736.560 736.560 ↑ 1.4 27,377 440

CTE Scan on values prop_translation (cost=0.00..782.52 rows=39,126 width=44) (actual time=0.000..1.674 rows=27,377 loops=440)

42. 0.080 70.958 ↓ 450.0 450 1

Hash (cost=978.15..978.15 rows=1 width=36) (actual time=70.958..70.958 rows=450 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
43. 70.878 70.878 ↓ 450.0 450 1

CTE Scan on values prop_purpose (cost=0.00..978.15 rows=1 width=36) (actual time=0.006..70.878 rows=450 loops=1)

  • Filter: ((short_xpath <@ 'annotation.purpose'::ltree) AND (value = 'OTHER:TRANSLATION'::text))
  • Rows Removed by Filter: 26927
44. 2,086.632 2,086.632 ↓ 107.5 4,191 438

CTE Scan on values v_1 (cost=0.00..880.33 rows=39 width=40) (actual time=0.003..4.764 rows=4,191 loops=438)

  • Filter: (short_xpath <@ 'annotation'::ltree)
  • Rows Removed by Filter: 23186
45. 18,160.794 18,160.794 ↓ 13.0 507 3,942

CTE Scan on values prop_name (cost=0.00..880.33 rows=39 width=36) (actual time=0.003..4.607 rows=507 loops=3,942)

  • Filter: (short_xpath <@ 'annotation.propertyName'::ltree)
  • Rows Removed by Filter: 26870