explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lnp0

Settings
# exclusive inclusive rows x rows loops node
1. 18.244 3,820.010 ↓ 1,603.4 128,271 1

Hash Left Join (cost=6,593,271.96..6,593,273.87 rows=80 width=193) (actual time=2,771.904..3,820.010 rows=128,271 loops=1)

  • Output: r.items_id, r.pitems_id, r.revs_id, r.type, r.name, r.lname, r.deny, r.rso, l.tagsexp, l.snapshotsexp
  • Hash Cond: (r.items_id = l.items_id)
  • Buffers: shared hit=7,291,661 read=12,790
2.          

CTE cur_items

3. 0.001 0.061 ↑ 103.0 1 1

Unique (cost=509.33..509.84 rows=103 width=30) (actual time=0.060..0.061 rows=1 loops=1)

  • Output: r_1.id, r_1.id, r_1.items_id, i.type, (2)
  • Buffers: shared hit=10
4. 0.009 0.060 ↑ 103.0 1 1

Sort (cost=509.33..509.58 rows=103 width=30) (actual time=0.060..0.060 rows=1 loops=1)

  • Output: r_1.id, r_1.id, r_1.items_id, i.type, (2)
  • Sort Key: r_1.items_id, (2)
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=10
5. 0.000 0.051 ↑ 103.0 1 1

Append (cost=1.61..505.88 rows=103 width=30) (actual time=0.047..0.051 rows=1 loops=1)

  • Buffers: shared hit=10
6. 0.002 0.047 ↑ 100.0 1 1

Nested Loop (cost=1.61..483.13 rows=100 width=30) (actual time=0.046..0.047 rows=1 loops=1)

  • Output: r_1.id, r_1.id, r_1.items_id, i.type, 2
  • Inner Unique: true
  • Buffers: shared hit=8
7. 0.002 0.040 ↑ 100.0 1 1

Nested Loop (cost=1.19..439.02 rows=100 width=16) (actual time=0.040..0.040 rows=1 loops=1)

  • Output: r_1.id, r_1.items_id
  • Inner Unique: true
  • Buffers: shared hit=4
8. 0.003 0.008 ↑ 100.0 1 1

HashAggregate (cost=0.77..2.02 rows=100 width=8) (actual time=0.008..0.008 rows=1 loops=1)

  • Output: (sos_unnest($7))
  • Group Key: sos_unnest($7)
9. 0.004 0.005 ↑ 100.0 1 1

ProjectSet (cost=0.00..0.52 rows=100 width=8) (actual time=0.004..0.005 rows=1 loops=1)

  • Output: sos_unnest($7)
10. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

11. 0.030 0.030 ↑ 1.0 1 1

Index Scan using revs_pkey on public.revs r_1 (cost=0.42..4.36 rows=1 width=16) (actual time=0.030..0.030 rows=1 loops=1)

  • Output: r_1.id, r_1.items_id, r_1.branch_id, r_1.ancestor_id, r_1.changeset_id, r_1.version, r_1.revision, r_1.lck_cnt, r_1.checkinby, r_1.checkintime, r_1.basename, r_1.checkinlog, r_1.checkoutby, r_1.checkouttime, r_1.checkoutlog, r_1.merge_from_rev, r_1.cattrs
  • Index Cond: (r_1.id = (sos_unnest($7)))
  • Buffers: shared hit=4
12. 0.005 0.005 ↑ 1.0 1 1

Index Scan using items_pkey on public.items i (cost=0.42..0.44 rows=1 width=10) (actual time=0.005..0.005 rows=1 loops=1)

  • Output: i.id, i.type, i.populate, i.date
  • Index Cond: (i.id = r_1.items_id)
  • Buffers: shared hit=4
13. 0.000 0.004 ↓ 0.0 0 1

Nested Loop (cost=3.01..21.21 rows=3 width=30) (actual time=0.004..0.004 rows=0 loops=1)

  • Output: revs.id, lock.next_revs_id, revs.items_id, items.type, 1
  • Inner Unique: true
  • Buffers: shared hit=2
14. 0.001 0.004 ↓ 0.0 0 1

Nested Loop (cost=2.60..19.89 rows=3 width=24) (actual time=0.004..0.004 rows=0 loops=1)

  • Output: lock.next_revs_id, revs.id, revs.items_id
  • Inner Unique: true
  • Buffers: shared hit=2
15. 0.000 0.003 ↓ 0.0 0 1

Bitmap Heap Scan on public.lock (cost=2.17..6.57 rows=3 width=16) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: lock.id, lock.revs_id, lock.next_revs_id, lock.owner, lock.date, lock.wid, lock.checkoutpath
  • Recheck Cond: (lock.wid = $2)
  • Buffers: shared hit=2
16. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on lock_revs_wid_index (cost=0.00..2.17 rows=3 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (lock.wid = $2)
  • Buffers: shared hit=2
17. 0.000 0.000 ↓ 0.0 0

Index Scan using revs_pkey on public.revs (cost=0.42..4.44 rows=1 width=16) (never executed)

  • Output: revs.id, revs.items_id, revs.branch_id, revs.ancestor_id, revs.changeset_id, revs.version, revs.revision, revs.lck_cnt, revs.checkinby, revs.checkintime, revs.basename, revs.checkinlog, revs.checkoutby, revs.checkouttime, revs.checkoutlog, revs.merge_from_rev, revs.cattrs
  • Index Cond: (revs.id = lock.revs_id)
18. 0.000 0.000 ↓ 0.0 0

Index Scan using items_pkey on public.items (cost=0.42..0.44 rows=1 width=10) (never executed)

  • Output: items.id, items.type, items.populate, items.date
  • Index Cond: (items.id = revs.items_id)
19.          

CTE dirs_below

20. 22.331 1,722.499 ↑ 1.0 62,663 1

HashAggregate (cost=5,697.06..6,324.45 rows=62,739 width=16) (actual time=1,712.375..1,722.499 rows=62,663 loops=1)

  • Output: i_3.id, i_3.type
  • Group Key: i_3.id, i_3.type
  • Buffers: shared hit=4,474,519 read=11,468
21.          

CTE d1

22. 15.127 1,581.199 ↓ 415.0 62,662 1

Recursive Union (cost=26.39..1,212.98 rows=151 width=10) (actual time=292.410..1,581.199 rows=62,662 loops=1)

  • Buffers: shared hit=4,223,855 read=11,468
23. 9.993 306.090 ↓ 30,719.0 30,719 1

Unique (cost=26.39..26.40 rows=1 width=10) (actual time=292.408..306.090 rows=30,719 loops=1)

  • Output: i_1.id, i_1.type
  • Buffers: shared hit=759,846 read=2,417
24. 25.951 296.097 ↓ 95,122.0 95,122 1

Sort (cost=26.39..26.40 rows=1 width=10) (actual time=292.407..296.097 rows=95,122 loops=1)

  • Output: i_1.id, i_1.type
  • Sort Key: i_1.id, i_1.type
  • Sort Method: quicksort Memory: 7,531kB
  • Buffers: shared hit=759,846 read=2,417
25. 27.488 270.146 ↓ 95,122.0 95,122 1

Nested Loop (cost=1.68..26.38 rows=1 width=10) (actual time=0.036..270.146 rows=95,122 loops=1)

  • Output: i_1.id, i_1.type
  • Inner Unique: true
  • Buffers: shared hit=759,846 read=2,417
26. 24.247 147.536 ↓ 31,707.3 95,122 1

Nested Loop (cost=1.26..25.01 rows=3 width=8) (actual time=0.027..147.536 rows=95,122 loops=1)

  • Output: dn.items_id
  • Inner Unique: true
  • Buffers: shared hit=379,936 read=1,839
27. 6.021 28.167 ↓ 31,707.3 95,122 1

Nested Loop (cost=0.84..23.68 rows=3 width=8) (actual time=0.019..28.167 rows=95,122 loops=1)

  • Output: di.name_id
  • Buffers: shared hit=82 read=1,205
28. 0.114 0.114 ↓ 8.0 24 1

Index Scan using revs_items_id_index on public.revs r_2 (cost=0.42..7.55 rows=3 width=8) (actual time=0.008..0.114 rows=24 loops=1)

  • Output: r_2.id, r_2.items_id, r_2.branch_id, r_2.ancestor_id, r_2.changeset_id, r_2.version, r_2.revision, r_2.lck_cnt, r_2.checkinby, r_2.checkintime, r_2.basename, r_2.checkinlog, r_2.checkoutby, r_2.checkouttime, r_2.checkoutlog, r_2.merge_from_rev, r_2.cattrs
  • Index Cond: (r_2.items_id = $3)
  • Buffers: shared hit=5 read=14
29. 22.032 22.032 ↓ 1,981.5 3,963 24

Index Only Scan using dirs_items_prevs_id_name_id_index on public.dirs_items di (cost=0.42..5.36 rows=2 width=16) (actual time=0.008..0.918 rows=3,963 loops=24)

  • Output: di.prevs_id, di.name_id
  • Index Cond: (di.prevs_id = r_2.id)
  • Heap Fetches: 95,122
  • Buffers: shared hit=77 read=1,191
30. 95.122 95.122 ↑ 1.0 1 95,122

Index Scan using dirs_items_name_pkey on public.dirs_items_name dn (cost=0.42..0.44 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=95,122)

  • Output: dn.id, dn.items_id, dn.name, dn.rso
  • Index Cond: (dn.id = di.name_id)
  • Buffers: shared hit=379,854 read=634
31. 95.122 95.122 ↑ 1.0 1 95,122

Index Scan using items_pkey on public.items i_1 (cost=0.42..0.46 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=95,122)

  • Output: i_1.id, i_1.type, i_1.populate, i_1.date
  • Index Cond: (i_1.id = dn.items_id)
  • Filter: ((i_1.type = 'D'::bpchar) OR (i_1.type = 'R'::bpchar))
  • Buffers: shared hit=379,910 read=578
32. 10.431 1,259.982 ↓ 236.6 3,549 9

Unique (cost=118.24..118.36 rows=15 width=10) (actual time=138.488..139.998 rows=3,549 loops=9)

  • Output: i_2.id, i_2.type
  • Buffers: shared hit=3,464,009 read=9,051
33. 25.488 1,249.551 ↓ 709.9 10,648 9

Sort (cost=118.24..118.28 rows=15 width=10) (actual time=138.437..138.839 rows=10,648 loops=9)

  • Output: i_2.id, i_2.type
  • Sort Key: i_2.id, i_2.type
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3,464,009 read=9,051
34. 85.806 1,224.063 ↓ 709.9 10,648 9

Nested Loop (cost=1.68..117.95 rows=15 width=10) (actual time=29.624..136.007 rows=10,648 loops=9)

  • Output: i_2.id, i_2.type
  • Inner Unique: true
  • Buffers: shared hit=3,464,009 read=9,051
35. 54.918 845.604 ↓ 1,083.9 32,517 9

Nested Loop (cost=1.26..104.20 rows=30 width=8) (actual time=0.009..93.956 rows=32,517 loops=9)

  • Output: dn_1.items_id
  • Inner Unique: true
  • Buffers: shared hit=2,293,984 read=8,464
36. 135.430 498.033 ↓ 1,083.9 32,517 9

Nested Loop (cost=0.84..90.95 rows=30 width=8) (actual time=0.006..55.337 rows=32,517 loops=9)

  • Output: di_1.name_id
  • Buffers: shared hit=1,124,021 read=7,815
37. 41.644 174.609 ↓ 696.3 20,888 9

Nested Loop (cost=0.42..76.01 rows=30 width=8) (actual time=0.003..19.401 rows=20,888 loops=9)

  • Output: r_3.id
  • Buffers: shared hit=370,923 read=5,050
38. 7.641 7.641 ↓ 696.2 6,962 9

WorkTable Scan on d1 n (cost=0.00..0.20 rows=10 width=8) (actual time=0.000..0.849 rows=6,962 loops=9)

  • Output: n.id, n.type
39. 125.324 125.324 ↑ 1.0 3 62,662

Index Scan using revs_items_id_index on public.revs r_3 (cost=0.42..7.55 rows=3 width=16) (actual time=0.001..0.002 rows=3 loops=62,662)

  • Output: r_3.id, r_3.items_id, r_3.branch_id, r_3.ancestor_id, r_3.changeset_id, r_3.version, r_3.revision, r_3.lck_cnt, r_3.checkinby, r_3.checkintime, r_3.basename, r_3.checkinlog, r_3.checkoutby, r_3.checkouttime, r_3.checkoutlog, r_3.merge_from_rev, r_3.cattrs
  • Index Cond: (r_3.items_id = n.id)
  • Buffers: shared hit=370,923 read=5,050
40. 187.994 187.994 ↑ 1.0 2 187,994

Index Only Scan using dirs_items_prevs_id_name_id_index on public.dirs_items di_1 (cost=0.42..0.48 rows=2 width=16) (actual time=0.001..0.001 rows=2 loops=187,994)

  • Output: di_1.prevs_id, di_1.name_id
  • Index Cond: (di_1.prevs_id = r_3.id)
  • Heap Fetches: 292,653
  • Buffers: shared hit=753,098 read=2,765
41. 292.653 292.653 ↑ 1.0 1 292,653

Index Scan using dirs_items_name_pkey on public.dirs_items_name dn_1 (cost=0.42..0.44 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=292,653)

  • Output: dn_1.id, dn_1.items_id, dn_1.name, dn_1.rso
  • Index Cond: (dn_1.id = di_1.name_id)
  • Buffers: shared hit=1,169,963 read=649
42. 292.653 292.653 ↓ 0.0 0 292,653

Index Scan using items_pkey on public.items i_2 (cost=0.42..0.46 rows=1 width=10) (actual time=0.001..0.001 rows=0 loops=292,653)

  • Output: i_2.id, i_2.type, i_2.populate, i_2.date
  • Index Cond: (i_2.id = dn_1.items_id)
  • Filter: ((i_2.type = 'D'::bpchar) OR (i_2.type = 'R'::bpchar))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1,170,025 read=587
43. 2.601 1,700.168 ↑ 1.0 62,663 1

Append (cost=856.58..4,170.38 rows=62,739 width=16) (actual time=1,686.858..1,700.168 rows=62,663 loops=1)

  • Buffers: shared hit=4,474,519 read=11,468
44. 0.002 0.011 ↓ 0.0 0 1

Result (cost=856.58..2,614.54 rows=62,664 width=10) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: i_3.id, i_3.type
  • One-Time Filter: ($14 = 'R'::bpchar)
  • Buffers: shared hit=4
45.          

Initplan (for Result)

46. 0.000 0.009 ↑ 1.0 1 1

Limit (cost=0.42..4.44 rows=1 width=2) (actual time=0.009..0.009 rows=1 loops=1)

  • Output: t.type
  • Buffers: shared hit=4
47. 0.009 0.009 ↑ 1.0 1 1

Index Scan using items_pkey on public.items t (cost=0.42..4.44 rows=1 width=2) (actual time=0.009..0.009 rows=1 loops=1)

  • Output: t.type
  • Index Cond: (t.id = $3)
  • Buffers: shared hit=4
48. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on public.items i_3 (cost=856.58..2,614.54 rows=62,664 width=10) (never executed)

  • Output: i_3.id, i_3.type, i_3.populate, i_3.date
  • Recheck Cond: ((i_3.type = 'D'::bpchar) OR (i_3.type = 'R'::bpchar))
49. 0.000 0.000 ↓ 0.0 0

BitmapOr (cost=852.15..852.15 rows=62,664 width=0) (never executed)

50. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on items_type_index (cost=0.00..818.39 rows=62,663 width=0) (never executed)

  • Index Cond: (i_3.type = 'D'::bpchar)
51. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on items_type_index (cost=0.00..2.42 rows=1 width=0) (never executed)

  • Index Cond: (i_3.type = 'R'::bpchar)
52. 8.336 1,697.534 ↓ 846.8 62,662 1

Unique (cost=605.33..605.88 rows=74 width=16) (actual time=1,686.846..1,697.534 rows=62,662 loops=1)

  • Output: d1.id, d1.type
  • Buffers: shared hit=4,474,507 read=11,468
53.          

Initplan (for Unique)

54. 0.001 0.011 ↑ 1.0 1 1

Limit (cost=0.42..4.44 rows=1 width=2) (actual time=0.011..0.011 rows=1 loops=1)

  • Output: t_1.type
  • Buffers: shared hit=4
55. 0.010 0.010 ↑ 1.0 1 1

Index Scan using items_pkey on public.items t_1 (cost=0.42..4.44 rows=1 width=2) (actual time=0.010..0.010 rows=1 loops=1)

  • Output: t_1.type
  • Index Cond: (t_1.id = $3)
  • Buffers: shared hit=4
56. 16.016 1,689.187 ↓ 846.8 62,662 1

Sort (cost=600.89..601.08 rows=74 width=16) (actual time=1,686.845..1,689.187 rows=62,662 loops=1)

  • Output: d1.id, d1.type
  • Sort Key: d1.id, d1.type
  • Sort Method: quicksort Memory: 4,474kB
  • Buffers: shared hit=4,474,507 read=11,468
57. 4.419 1,673.171 ↓ 846.8 62,662 1

Result (cost=0.42..598.59 rows=74 width=16) (actual time=292.434..1,673.171 rows=62,662 loops=1)

  • Output: d1.id, d1.type
  • One-Time Filter: ($15 <> 'R'::bpchar)
  • Buffers: shared hit=4,474,507 read=11,468
58. 17.228 1,668.752 ↓ 846.8 62,662 1

Nested Loop (cost=0.42..598.59 rows=74 width=16) (actual time=292.422..1,668.752 rows=62,662 loops=1)

  • Output: d1.id, d1.type
  • Inner Unique: true
  • Buffers: shared hit=4,474,503 read=11,468
59. 1,588.862 1,588.862 ↓ 415.0 62,662 1

CTE Scan on d1 (cost=0.00..3.02 rows=151 width=16) (actual time=292.411..1,588.862 rows=62,662 loops=1)

  • Output: d1.id, d1.type
  • Buffers: shared hit=4,223,855 read=11,468
60. 62.662 62.662 ↑ 1.0 1 62,662

Index Scan using items_pkey on public.items i_4 (cost=0.42..3.93 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=62,662)

  • Output: i_4.id, i_4.type, i_4.populate, i_4.date
  • Index Cond: (i_4.id = d1.id)
  • Filter: (i_4.type = ANY ('{D,R}'::bpchar[]))
  • Buffers: shared hit=250,648
61. 0.006 0.022 ↑ 1.0 1 1

Result (cost=4.85..8.87 rows=1 width=10) (actual time=0.021..0.022 rows=1 loops=1)

  • Output: i_5.id, i_5.type
  • One-Time Filter: ($16 <> 'R'::bpchar)
  • Buffers: shared hit=8
62.          

Initplan (for Result)

63. 0.002 0.013 ↑ 1.0 1 1

Limit (cost=0.42..4.44 rows=1 width=2) (actual time=0.013..0.013 rows=1 loops=1)

  • Output: t_2.type
  • Buffers: shared hit=4
64. 0.011 0.011 ↑ 1.0 1 1

Index Scan using items_pkey on public.items t_2 (cost=0.42..4.44 rows=1 width=2) (actual time=0.011..0.011 rows=1 loops=1)

  • Output: t_2.type
  • Index Cond: (t_2.id = $3)
  • Buffers: shared hit=4
65. 0.003 0.003 ↑ 1.0 1 1

Index Scan using items_pkey on public.items i_5 (cost=4.85..8.87 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=1)

  • Output: i_5.id, i_5.type, i_5.populate, i_5.date
  • Index Cond: (i_5.id = $3)
  • Buffers: shared hit=4
66.          

CTE dir_match

67. 13.154 1,867.620 ↓ 313.3 62,663 1

Hash Right Join (cost=6,506,539.09..6,506,541.43 rows=200 width=56) (actual time=1,854.482..1,867.620 rows=62,663 loops=1)

  • Output: COALESCE(c.revs_id, m.revs_id), COALESCE(c.nrevs_id, m.revs_id), m.items_id, m.lname
  • Inner Unique: true
  • Hash Cond: (c.items_id = m.items_id)
  • Buffers: shared hit=4,474,608 read=12,535
68. 0.065 0.065 ↑ 103.0 1 1

CTE Scan on cur_items c (cost=0.00..2.06 rows=103 width=24) (actual time=0.063..0.065 rows=1 loops=1)

  • Output: c.revs_id, c.nrevs_id, c.items_id, c.type
  • Buffers: shared hit=10
69. 7.306 1,854.401 ↓ 313.3 62,663 1

Hash (cost=6,506,536.59..6,506,536.59 rows=200 width=48) (actual time=1,854.401..1,854.401 rows=62,663 loops=1)

  • Output: m.revs_id, m.items_id, m.lname
  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3,756kB
  • Buffers: shared hit=4,474,598 read=12,535
70. 4.596 1,847.095 ↓ 313.3 62,663 1

Subquery Scan on m (cost=6,506,218.59..6,506,536.59 rows=200 width=48) (actual time=1,830.640..1,847.095 rows=62,663 loops=1)

  • Output: m.revs_id, m.items_id, m.lname
  • Buffers: shared hit=4,474,598 read=12,535
71. 6.542 1,842.499 ↓ 313.3 62,663 1

Unique (cost=6,506,218.59..6,506,534.59 rows=200 width=56) (actual time=1,830.639..1,842.499 rows=62,663 loops=1)

  • Output: m_1.items_id, m_1.mrevs_id, r_4.lname, r_4.priority
  • Buffers: shared hit=4,474,598 read=12,535
72. 21.560 1,835.957 ↑ 1.0 62,663 1

Sort (cost=6,506,218.59..6,506,376.59 rows=63,200 width=56) (actual time=1,830.638..1,835.957 rows=62,663 loops=1)

  • Output: m_1.items_id, m_1.mrevs_id, r_4.lname, r_4.priority
  • Sort Key: m_1.items_id, r_4.priority
  • Sort Method: quicksort Memory: 6,432kB
  • Buffers: shared hit=4,474,598 read=12,535
73. 4.125 1,814.397 ↑ 1.0 62,663 1

Nested Loop (cost=4,470.18..6,501,179.14 rows=63,200 width=56) (actual time=1,754.400..1,814.397 rows=62,663 loops=1)

  • Output: m_1.items_id, m_1.mrevs_id, r_4.lname, r_4.priority
  • Buffers: shared hit=4,474,598 read=12,535
74. 0.004 0.830 ↑ 200.0 1 1

HashAggregate (cost=22.75..24.75 rows=200 width=82) (actual time=0.829..0.830 rows=1 loops=1)

  • Output: r_4.ltype, r_4.labels_id, r_4.priority, r_4.lname, r_4.itype
  • Group Key: r_4.ltype, r_4.labels_id, r_4.priority, r_4.lname, r_4.itype
  • Buffers: shared hit=73
75. 0.826 0.826 ↑ 1,000.0 1 1

Function Scan on public.rsoids r_4 (cost=0.25..10.25 rows=1,000 width=82) (actual time=0.826..0.826 rows=1 loops=1)

  • Output: r_4.labels_id, r_4.lname, r_4.ltype, r_4.priority, r_4.itype
  • Function Call: rsoids($4, 'D'::bpchar)
  • Buffers: shared hit=73
76. 2.743 1,809.442 ↓ 198.3 62,663 1

Append (cost=4,447.43..32,502.60 rows=316 width=16) (actual time=1,753.567..1,809.442 rows=62,663 loops=1)

  • Buffers: shared hit=4,474,525 read=12,535
77. 3.662 1,806.663 ↓ 399.1 62,663 1

Result (cost=4,447.43..5,943.37 rows=157 width=16) (actual time=1,753.567..1,806.663 rows=62,663 loops=1)

  • Output: m_1.items_id, m_1.mrevs_id
  • One-Time Filter: (($6 = 0) AND (r_4.ltype = 0))
  • Buffers: shared hit=4,474,522 read=12,535
78. 9.882 1,803.001 ↓ 399.1 62,663 1

Hash Join (cost=4,447.43..5,943.37 rows=157 width=16) (actual time=1,753.565..1,803.001 rows=62,663 loops=1)

  • Output: m_1.items_id, m_1.mrevs_id
  • Hash Cond: (i_6.type = t_3.itype)
  • Buffers: shared hit=4,474,522 read=12,535
79. 18.436 1,793.114 ↓ 199.6 62,663 1

Hash Join (cost=4,445.18..5,938.37 rows=314 width=24) (actual time=1,753.552..1,793.114 rows=62,663 loops=1)

  • Output: i_6.type, m_1.items_id, m_1.mrevs_id
  • Hash Cond: (i_6.id = m_1.items_id)
  • Buffers: shared hit=4,474,522 read=12,535
80. 1,733.513 1,733.513 ↑ 1.0 62,663 1

CTE Scan on dirs_below i_6 (cost=0.00..1,254.78 rows=62,739 width=16) (actual time=1,712.376..1,733.513 rows=62,663 loops=1)

  • Output: i_6.id, i_6.type
  • Buffers: shared hit=4,474,519 read=11,468
81. 13.767 41.165 ↓ 200.1 128,272 1

Hash (cost=4,437.16..4,437.16 rows=641 width=16) (actual time=41.165..41.165 rows=128,272 loops=1)

  • Output: m_1.items_id, m_1.mrevs_id
  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 7,037kB
  • Buffers: shared hit=3 read=1,067
82. 12.693 27.398 ↓ 200.1 128,272 1

Hash Left Join (cost=1.02..4,437.16 rows=641 width=16) (actual time=0.021..27.398 rows=128,272 loops=1)

  • Output: m_1.items_id, m_1.mrevs_id
  • Hash Cond: (m_1.branch_id = b.labels_id)
  • Join Filter: (b.terminated OR m_1.terminated)
  • Filter: (b.id IS NULL)
  • Buffers: shared hit=3 read=1,067
83. 14.700 14.700 ↑ 1.0 128,272 1

Seq Scan on public.maxrev m_1 (cost=0.00..2,672.40 rows=128,272 width=25) (actual time=0.009..14.700 rows=128,272 loops=1)

  • Output: m_1.id, m_1.branch_id, m_1.items_id, m_1.mrevs_id, m_1.terminated
  • Filter: (m_1.branch_id = r_4.labels_id)
  • Buffers: shared hit=2 read=1,067
84. 0.001 0.005 ↓ 0.0 0 1

Hash (cost=1.01..1.01 rows=1 width=17) (actual time=0.005..0.005 rows=0 loops=1)

  • Output: b.labels_id, b.terminated, b.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1
85. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on public.branch_attrs b (cost=0.00..1.01 rows=1 width=17) (actual time=0.004..0.004 rows=0 loops=1)

  • Output: b.labels_id, b.terminated, b.id
  • Filter: (b.ign_if_term AND (b.labels_id = r_4.labels_id))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1
86. 0.001 0.005 ↑ 50.0 2 1

Hash (cost=1.00..1.00 rows=100 width=32) (actual time=0.005..0.005 rows=2 loops=1)

  • Output: t_3.itype
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
87. 0.004 0.004 ↑ 50.0 2 1

Function Scan on public.sos_unnest t_3 (cost=0.00..1.00 rows=100 width=32) (actual time=0.003..0.004 rows=2 loops=1)

  • Output: t_3.itype
  • Function Call: sos_unnest(r_4.itype)
88. 0.001 0.033 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=25,031.39..25,033.74 rows=157 width=16) (actual time=0.033..0.033 rows=0 loops=1)

  • Output: "*SELECT* 2".items_id, "*SELECT* 2".revs_id
  • Buffers: shared hit=3
89. 0.000 0.032 ↓ 0.0 0 1

Unique (cost=25,031.39..25,032.17 rows=157 width=28) (actual time=0.032..0.032 rows=0 loops=1)

  • Output: r_5.items_id, r_5.id, (r_4.priority), r_5.revision
  • Buffers: shared hit=3
90. 0.030 0.032 ↓ 0.0 0 1

Sort (cost=25,031.39..25,031.78 rows=157 width=28) (actual time=0.032..0.032 rows=0 loops=1)

  • Output: r_5.items_id, r_5.id, (r_4.priority), r_5.revision
  • Sort Key: r_5.items_id, r_5.revision DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3
91. 0.002 0.002 ↓ 0.0 0 1

Result (cost=23,216.03..25,025.66 rows=157 width=28) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: r_5.items_id, r_5.id, r_4.priority, r_5.revision
  • One-Time Filter: (($6 <> 0) AND (r_4.ltype = 0))
92. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=23,216.03..25,025.66 rows=157 width=20) (never executed)

  • Output: r_5.items_id, r_5.id, r_5.revision
  • Hash Cond: (i_7.type = t_4.itype)
93. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=23,213.77..25,020.65 rows=314 width=28) (never executed)

  • Output: r_5.items_id, r_5.id, r_5.revision, i_7.type
  • Hash Cond: (i_7.id = r_5.items_id)
94. 0.000 0.000 ↓ 0.0 0

CTE Scan on dirs_below i_7 (cost=0.00..1,254.78 rows=62,739 width=16) (never executed)

  • Output: i_7.id, i_7.type
95. 0.000 0.000 ↓ 0.0 0

Hash (cost=23,205.76..23,205.76 rows=641 width=20) (never executed)

  • Output: r_5.items_id, r_5.id, r_5.revision
96. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=4,918.17..23,205.76 rows=641 width=20) (never executed)

  • Output: r_5.items_id, r_5.id, r_5.revision
  • Hash Cond: ((r_5.branch_id = b_1.labels_id) AND (r_5.items_id = m_2.items_id))
  • Filter: (b_1.id IS NULL)
97. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.revs r_5 (cost=0.00..16,684.08 rows=128,281 width=28) (never executed)

  • Output: r_5.id, r_5.items_id, r_5.branch_id, r_5.ancestor_id, r_5.changeset_id, r_5.version, r_5.revision, r_5.lck_cnt, r_5.checkinby, r_5.checkintime, r_5.basename, r_5.checkinlog, r_5.checkoutby, r_5.checkouttime, r_5.checkoutlog, r_5.merge_from_rev, r_5.cattrs
  • Filter: ((r_5.revision > '-1'::integer) AND (r_5.branch_id = r_4.labels_id) AND (date_trunc('second'::text, r_5.checkintime) <= to_timestamp(($6)::double precision)))
98. 0.000 0.000 ↓ 0.0 0

Hash (cost=3,956.13..3,956.13 rows=64,136 width=24) (never executed)

  • Output: b_1.labels_id, b_1.id, m_2.items_id
99. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..3,956.13 rows=64,136 width=24) (never executed)

  • Output: b_1.labels_id, b_1.id, m_2.items_id
  • Join Filter: (m_2.terminated OR b_1.terminated)
100. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.branch_attrs b_1 (cost=0.00..1.01 rows=1 width=17) (never executed)

  • Output: b_1.id, b_1.labels_id, b_1.snap_labels_id, b_1.parent_lid, b_1.ign_if_term, b_1.terminated, b_1.proj_bra
  • Filter: (b_1.ign_if_term AND (b_1.labels_id = r_4.labels_id))
101. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.maxrev m_2 (cost=0.00..2,672.40 rows=128,272 width=17) (never executed)

  • Output: m_2.id, m_2.branch_id, m_2.items_id, m_2.mrevs_id, m_2.terminated
  • Filter: (m_2.branch_id = r_4.labels_id)
102. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.00..1.00 rows=100 width=32) (never executed)

  • Output: t_4.itype
103. 0.000 0.000 ↓ 0.0 0

Function Scan on public.sos_unnest t_4 (cost=0.00..1.00 rows=100 width=32) (never executed)

  • Output: t_4.itype
  • Function Call: sos_unnest(r_4.itype)
104. 0.001 0.003 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=1,521.47..1,522.34 rows=2 width=16) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: "*SELECT* 3".items_id, "*SELECT* 3".revs_id
105. 0.000 0.002 ↓ 0.0 0 1

Unique (cost=1,521.47..1,522.32 rows=2 width=32) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: a.items_id, a.revs_id, a.labels_id, a.id
106. 0.002 0.002 ↓ 0.0 0 1

Sort (cost=1,521.47..1,521.90 rows=169 width=32) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: a.items_id, a.revs_id, a.labels_id, a.id
  • Sort Key: a.items_id, a.id DESC
  • Sort Method: quicksort Memory: 25kB
107. 0.000 0.000 ↓ 0.0 0 1

Result (cost=19.08..1,515.22 rows=169 width=32) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: a.items_id, a.revs_id, a.labels_id, a.id
  • One-Time Filter: (r_4.ltype > 0)
108. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=19.08..1,515.22 rows=169 width=32) (never executed)

  • Output: a.items_id, a.revs_id, a.labels_id, a.id
  • Hash Cond: (i_8.type = t_5.itype)
109. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=16.83..1,510.02 rows=337 width=40) (never executed)

  • Output: i_8.type, a.items_id, a.revs_id, a.labels_id, a.id
  • Hash Cond: (i_8.id = a.items_id)
110. 0.000 0.000 ↓ 0.0 0

CTE Scan on dirs_below i_8 (cost=0.00..1,254.78 rows=62,739 width=16) (never executed)

  • Output: i_8.id, i_8.type
111. 0.000 0.000 ↓ 0.0 0

Hash (cost=16.82..16.82 rows=1 width=32) (never executed)

  • Output: a.items_id, a.revs_id, a.labels_id, a.id
112. 0.000 0.000 ↓ 0.0 0

Nested Loop Anti Join (cost=3.36..16.82 rows=1 width=32) (never executed)

  • Output: a.items_id, a.revs_id, a.labels_id, a.id
113. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on public.revlabels a (cost=2.18..7.75 rows=2 width=32) (never executed)

  • Output: a.id, a.labels_id, a.items_id, a.revs_id, a.owner, a.date
  • Recheck Cond: (a.labels_id = r_4.labels_id)
  • Filter: CASE WHEN ($6 <> 0) THEN (date_trunc('second'::text, a.date) <= to_timestamp(($6)::double precision)) ELSE true END
114. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on revlabels_labels_id_items_id_index (cost=0.00..2.18 rows=4 width=0) (never executed)

  • Index Cond: (a.labels_id = r_4.labels_id)
115. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on public.revlabels_bo d (cost=1.18..5.64 rows=2 width=8) (never executed)

  • Output: d.id, d.revlabels_id, d.labels_id, d.items_id, d.revs_id, d.owner, d.date
  • Recheck Cond: (d.revlabels_id = a.id)
  • Filter: CASE WHEN ($6 <> 0) THEN (date_trunc('second'::text, d.date) <= to_timestamp(($6)::double precision)) ELSE true END
116. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on revlabels_bo_revlabels_id_index (cost=0.00..1.18 rows=4 width=0) (never executed)

  • Index Cond: (d.revlabels_id = a.id)
117. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.00..1.00 rows=100 width=32) (never executed)

  • Output: t_5.itype
118. 0.000 0.000 ↓ 0.0 0

Function Scan on public.sos_unnest t_5 (cost=0.00..1.00 rows=100 width=32) (never executed)

  • Output: t_5.itype
  • Function Call: sos_unnest(r_4.itype)
119.          

CTE the_dirs

120. 0.000 2,513.798 ↓ 292.8 62,662 1

Nested Loop (cost=1.26..1,416.59 rows=214 width=61) (actual time=1,854.502..2,513.798 rows=62,662 loops=1)

  • Output: i_9.id, m_3.items_id, i_9.type, dn_2.rso, dn_2.name
  • Inner Unique: true
  • Buffers: shared hit=5,752,041 read=12,535
121. 0.000 2,285.333 ↓ 292.2 128,270 1

Nested Loop (cost=0.84..1,215.40 rows=439 width=59) (actual time=1,854.498..2,285.333 rows=128,270 loops=1)

  • Output: m_3.items_id, dn_2.rso, dn_2.name, dn_2.items_id
  • Inner Unique: true
  • Buffers: shared hit=5,238,961 read=12,535
122. 46.103 2,055.819 ↓ 292.2 128,270 1

Nested Loop (cost=0.42..1,021.51 rows=439 width=16) (actual time=1,854.492..2,055.819 rows=128,270 loops=1)

  • Output: m_3.items_id, di_2.name_id
  • Buffers: shared hit=4,725,881 read=12,535
123. 1,884.390 1,884.390 ↓ 313.3 62,663 1

CTE Scan on dir_match m_3 (cost=0.00..4.00 rows=200 width=16) (actual time=1,854.482..1,884.390 rows=62,663 loops=1)

  • Output: m_3.revs_id, m_3.nrevs_id, m_3.items_id, m_3.lname
  • Buffers: shared hit=4,474,608 read=12,535
124. 125.326 125.326 ↑ 1.0 2 62,663

Index Only Scan using dirs_items_prevs_id_name_id_index on public.dirs_items di_2 (cost=0.42..5.07 rows=2 width=16) (actual time=0.002..0.002 rows=2 loops=62,663)

  • Output: di_2.prevs_id, di_2.name_id
  • Index Cond: (di_2.prevs_id = m_3.nrevs_id)
  • Heap Fetches: 128,270
  • Buffers: shared hit=251,273
125. 256.540 256.540 ↑ 1.0 1 128,270

Index Scan using dirs_items_name_pkey on public.dirs_items_name dn_2 (cost=0.42..0.44 rows=1 width=59) (actual time=0.002..0.002 rows=1 loops=128,270)

  • Output: dn_2.id, dn_2.items_id, dn_2.name, dn_2.rso
  • Index Cond: (dn_2.id = di_2.name_id)
  • Buffers: shared hit=513,080
126. 256.540 256.540 ↓ 0.0 0 128,270

Index Scan using items_pkey on public.items i_9 (cost=0.42..0.46 rows=1 width=10) (actual time=0.002..0.002 rows=0 loops=128,270)

  • Output: i_9.id, i_9.type, i_9.populate, i_9.date
  • Index Cond: (i_9.id = dn_2.items_id)
  • Filter: ((i_9.type = 'D'::bpchar) OR (i_9.type = 'R'::bpchar))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=513,080
127.          

CTE dir_haveperms

128. 0.001 0.022 ↓ 0.0 0 1

Append (cost=1.17..13.01 rows=2 width=37) (actual time=0.022..0.022 rows=0 loops=1)

  • Buffers: shared hit=2
129. 0.002 0.016 ↓ 0.0 0 1

Result (cost=1.17..4.06 rows=1 width=37) (actual time=0.016..0.016 rows=0 loops=1)

  • Output: s.id, s.readaccess, s.owner, s.grp
  • One-Time Filter: ($35 = 0)
  • Buffers: shared hit=1
130.          

Initplan (for Result)

131. 0.005 0.014 ↑ 1.0 1 1

Aggregate (cost=1.04..1.05 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1)

  • Output: count(user_group_roles.rolename)
  • Buffers: shared hit=1
132. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on public.user_group_roles (cost=0.00..1.03 rows=1 width=32) (actual time=0.008..0.009 rows=1 loops=1)

  • Output: user_group_roles.id, user_group_roles.username, user_group_roles.groupname, user_group_roles.rolename
  • Filter: (((user_group_roles.username)::text = $1) AND ((user_group_roles.rolename)::text = 'ADMIN'::text) AND ((user_group_roles.groupname)::text = 'all'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1
133. 0.000 0.000 ↓ 0.0 0

Index Only Scan using items_src_readaccess_noworld_index on public.items_src s (cost=1.17..4.06 rows=1 width=37) (never executed)

  • Output: s.id, s.readaccess, s.owner, s.grp
  • Index Cond: (s.id = $3)
  • Heap Fetches: 0
134. 0.000 0.005 ↓ 0.0 0 1

Result (cost=4.08..8.92 rows=1 width=37) (actual time=0.005..0.005 rows=0 loops=1)

  • Output: m_4.items_id, s_1.readaccess, s_1.owner, s_1.grp
  • One-Time Filter: ($36 = 0)
  • Buffers: shared hit=1
135.          

Initplan (for Result)

136. 0.003 0.005 ↑ 1.0 1 1

Aggregate (cost=1.04..1.05 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)

  • Output: count(user_group_roles_1.rolename)
  • Buffers: shared hit=1
137. 0.002 0.002 ↑ 1.0 1 1

Seq Scan on public.user_group_roles user_group_roles_1 (cost=0.00..1.03 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)

  • Output: user_group_roles_1.id, user_group_roles_1.username, user_group_roles_1.groupname, user_group_roles_1.rolename
  • Filter: (((user_group_roles_1.username)::text = $1) AND ((user_group_roles_1.rolename)::text = 'ADMIN'::text) AND ((user_group_roles_1.groupname)::text = 'all'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1
138. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=4.08..8.92 rows=1 width=37) (never executed)

  • Output: m_4.items_id, s_1.readaccess, s_1.owner, s_1.grp
  • Inner Unique: true
  • Hash Cond: (m_4.items_id = s_1.id)
139. 0.000 0.000 ↓ 0.0 0

CTE Scan on the_dirs m_4 (cost=0.00..4.28 rows=214 width=8) (never executed)

  • Output: m_4.items_id, m_4.pitems_id, m_4.type, m_4.rso, m_4.name
140. 0.000 0.000 ↓ 0.0 0

Hash (cost=3.01..3.01 rows=1 width=37) (never executed)

  • Output: s_1.readaccess, s_1.owner, s_1.grp, s_1.id
141. 0.000 0.000 ↓ 0.0 0

Index Only Scan using items_src_readaccess_noworld_index on public.items_src s_1 (cost=0.12..3.01 rows=1 width=37) (never executed)

  • Output: s_1.readaccess, s_1.owner, s_1.grp, s_1.id
  • Heap Fetches: 0
142.          

CTE dir_obj_denied

143. 0.001 0.023 ↓ 0.0 0 1

Append (cost=0.00..6.88 rows=3 width=8) (actual time=0.023..0.023 rows=0 loops=1)

  • Buffers: shared hit=2
144. 0.022 0.022 ↓ 0.0 0 1

CTE Scan on dir_haveperms m_5 (cost=0.00..0.05 rows=1 width=8) (actual time=0.022..0.022 rows=0 loops=1)

  • Output: m_5.items_id
  • Filter: (((m_5.owner)::text <> $1) AND ((m_5.readaccess)::text = 'user'::text))
  • Buffers: shared hit=2
145. 0.000 0.000 ↓ 0.0 0 1

Nested Loop Anti Join (cost=2.78..2.87 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: m_6.items_id
  • Join Filter: ((g.groupname)::text = (m_6.grp)::text)
146. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on dir_haveperms m_6 (cost=0.00..0.06 rows=1 width=40) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: m_6.items_id, m_6.readaccess, m_6.owner, m_6.grp
  • Filter: (((m_6.grp)::text <> 'all_my_groups'::text) AND ((m_6.owner)::text <> $1) AND ((m_6.readaccess)::text = 'group'::text))
147. 0.000 0.000 ↓ 0.0 0

Unique (cost=2.78..2.79 rows=1 width=32) (never executed)

  • Output: g.groupname
148. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.78..2.79 rows=1 width=32) (never executed)

  • Output: g.groupname
  • Sort Key: g.groupname
149. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..2.77 rows=1 width=32) (never executed)

  • Output: g.groupname
  • Join Filter: ((g.rolename)::text = (r_6.rolename)::text)
150. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.user_group_roles g (cost=0.00..1.02 rows=1 width=64) (never executed)

  • Output: g.id, g.username, g.groupname, g.rolename
  • Filter: ((g.username)::text = $1)
151. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.roles r_6 (cost=0.00..1.71 rows=3 width=6) (never executed)

  • Output: r_6.id, r_6.rolename, r_6.commands
  • Filter: (r_6.commands = 'populate'::text)
152. 0.000 0.000 ↓ 0.0 0 1

Nested Loop Anti Join (cost=3.83..3.91 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: m_7.items_id
  • Join Filter: ((g_1.username)::text = (m_7.owner)::text)
153. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on dir_haveperms m_7 (cost=0.00..0.06 rows=1 width=40) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: m_7.items_id, m_7.readaccess, m_7.owner, m_7.grp
  • Filter: (((m_7.owner)::text <> $1) AND ((m_7.readaccess)::text = 'group'::text) AND ((m_7.grp)::text = 'all_my_groups'::text))
154. 0.000 0.000 ↓ 0.0 0

Unique (cost=3.83..3.83 rows=1 width=32) (never executed)

  • Output: g_1.username
155. 0.000 0.000 ↓ 0.0 0

Sort (cost=3.83..3.83 rows=1 width=32) (never executed)

  • Output: g_1.username
  • Sort Key: g_1.username
156. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..3.82 rows=1 width=32) (never executed)

  • Output: g_1.username
  • Join Filter: ((mg.rolename)::text = (r_7.rolename)::text)
157. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..2.07 rows=1 width=64) (never executed)

  • Output: g_1.username, mg.rolename
  • Join Filter: ((g_1.groupname)::text = (mg.groupname)::text)
158. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.user_group_roles g_1 (cost=0.00..1.02 rows=1 width=64) (never executed)

  • Output: g_1.id, g_1.username, g_1.groupname, g_1.rolename
  • Filter: ((g_1.username)::text <> $1)
159. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.user_group_roles mg (cost=0.00..1.03 rows=1 width=64) (never executed)

  • Output: mg.id, mg.username, mg.groupname, mg.rolename
  • Filter: (((mg.groupname)::text <> 'all_my_groups'::text) AND ((mg.username)::text = $1))
160. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.roles r_7 (cost=0.00..1.71 rows=3 width=6) (never executed)

  • Output: r_7.id, r_7.rolename, r_7.commands
  • Filter: (r_7.commands = 'populate'::text)
161.          

CTE the_ignored

162. 0.000 0.003 ↓ 0.0 0 1

HashAggregate (cost=0.77..2.02 rows=100 width=8) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: (sos_unnest($8))
  • Group Key: sos_unnest($8)
163. 0.003 0.003 ↓ 0.0 0 1

ProjectSet (cost=0.00..0.52 rows=100 width=8) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: sos_unnest($8)
164. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)

165.          

CTE the_dirs_deny

166. 11.298 2,555.692 ↓ 292.8 62,662 1

Hash Left Join (cost=3.35..11.94 rows=214 width=90) (actual time=1,854.514..2,555.692 rows=62,662 loops=1)

  • Output: m_8.items_id, m_8.pitems_id, m_8.type, (COALESCE(d_1.items_id, '0'::bigint) <> 0), ((COALESCE(d_1.items_id, '0'::bigint) <> 0) OR (COALESCE(n_1.items_id, '0'::bigint) <> 0)), m_8.rso, m_8.name
  • Hash Cond: (m_8.items_id = n_1.items_id)
  • Buffers: shared hit=5,752,041 read=12,535
167. 10.545 2,544.394 ↓ 292.8 62,662 1

Hash Left Join (cost=0.10..5.21 rows=214 width=96) (actual time=1,854.510..2,544.394 rows=62,662 loops=1)

  • Output: m_8.items_id, m_8.pitems_id, m_8.type, m_8.rso, m_8.name, d_1.items_id
  • Hash Cond: (m_8.items_id = d_1.items_id)
  • Buffers: shared hit=5,752,041 read=12,535
168. 2,533.848 2,533.848 ↓ 292.8 62,662 1

CTE Scan on the_dirs m_8 (cost=0.00..4.28 rows=214 width=88) (actual time=1,854.503..2,533.848 rows=62,662 loops=1)

  • Output: m_8.items_id, m_8.pitems_id, m_8.type, m_8.rso, m_8.name
  • Buffers: shared hit=5,752,041 read=12,535
169. 0.001 0.001 ↓ 0.0 0 1

Hash (cost=0.06..0.06 rows=3 width=8) (actual time=0.000..0.001 rows=0 loops=1)

  • Output: d_1.items_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
170. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on dir_obj_denied d_1 (cost=0.00..0.06 rows=3 width=8) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: d_1.items_id
171. 0.000 0.000 ↓ 0.0 0 1

Hash (cost=2.00..2.00 rows=100 width=8) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: n_1.items_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
172. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on the_ignored n_1 (cost=0.00..2.00 rows=100 width=8) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: n_1.items_id
173.          

CTE dir_tree

174. 2,747.268 2,747.268 ↓ 1,228.7 62,663 1

CTE Scan on d2 (cost=62.01..63.03 rows=51 width=90) (actual time=0.046..2,747.268 rows=62,663 loops=1)

  • Output: d2.items_id, d2.pitems_id, d2.type, d2.deny, d2.ignore, d2.rso, d2.name
  • Buffers: shared hit=5,752,047 read=12,535
175.          

CTE d2

176. 30.150 2,727.654 ↓ 1,228.7 62,663 1

Recursive Union (cost=0.42..62.01 rows=51 width=90) (actual time=0.046..2,727.654 rows=62,663 loops=1)

  • Buffers: shared hit=5,752,047 read=12,535
177. 0.002 0.044 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.42..6.79 rows=1 width=84) (actual time=0.043..0.044 rows=1 loops=1)

  • Output: i_10.id, '0'::bigint, i_10.type, (COALESCE(p.items_id, '0'::bigint) <> 0), ((COALESCE(p.items_id, '0'::bigint) <> 0) OR (COALESCE(n_2.items_id, '0'::bigint) <> 0)), NULL::character varying, $5
  • Join Filter: (n_2.items_id = i_10.id)
  • Buffers: shared hit=6
178. 0.002 0.038 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.42..4.52 rows=1 width=18) (actual time=0.038..0.038 rows=1 loops=1)

  • Output: i_10.id, i_10.type, p.items_id
  • Join Filter: (p.items_id = i_10.id)
  • Buffers: shared hit=6
179. 0.013 0.013 ↑ 1.0 1 1

Index Scan using items_pkey on public.items i_10 (cost=0.42..4.44 rows=1 width=10) (actual time=0.013..0.013 rows=1 loops=1)

  • Output: i_10.id, i_10.type, i_10.populate, i_10.date
  • Index Cond: (i_10.id = $3)
  • Buffers: shared hit=4
180. 0.023 0.023 ↓ 0.0 0 1

CTE Scan on dir_obj_denied p (cost=0.00..0.07 rows=1 width=8) (actual time=0.023..0.023 rows=0 loops=1)

  • Output: p.items_id
  • Filter: (p.items_id = $3)
  • Buffers: shared hit=2
181. 0.004 0.004 ↓ 0.0 0 1

CTE Scan on the_ignored n_2 (cost=0.00..2.25 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)

  • Output: n_2.items_id
  • Filter: (n_2.items_id = $3)
182. 53.170 2,697.460 ↓ 1,253.2 6,266 10

Hash Join (cost=0.26..5.42 rows=5 width=90) (actual time=192.124..269.746 rows=6,266 loops=10)

  • Output: i_11.items_id, r_8.items_id, i_11.type, i_11.deny, i_11.ignore, i_11.rso, ((r_8.name || '/'::text) || (i_11.name)::text)
  • Hash Cond: (i_11.pitems_id = r_8.items_id)
  • Buffers: shared hit=5,752,041 read=12,535
183. 2,628.410 2,628.410 ↓ 292.8 62,662 10

CTE Scan on the_dirs_deny i_11 (cost=0.00..4.28 rows=214 width=90) (actual time=185.452..262.841 rows=62,662 loops=10)

  • Output: i_11.items_id, i_11.pitems_id, i_11.type, i_11.deny, i_11.ignore, i_11.rso, i_11.name
  • Buffers: shared hit=5,752,041 read=12,535
184. 6.920 15.880 ↓ 1,253.2 6,266 10

Hash (cost=0.20..0.20 rows=5 width=40) (actual time=1.588..1.588 rows=6,266 loops=10)

  • Output: r_8.items_id, r_8.name
  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
185. 8.960 8.960 ↓ 1,253.2 6,266 10

WorkTable Scan on d2 r_8 (cost=0.00..0.20 rows=5 width=40) (actual time=0.001..0.896 rows=6,266 loops=10)

  • Output: r_8.items_id, r_8.name
  • Filter: (NOT r_8.ignore)
186.          

CTE the_files

187. 89.786 635.143 ↓ 2,262.3 65,608 1

Nested Loop (cost=2.60..190.07 rows=29 width=82) (actual time=102.500..635.143 rows=65,608 loops=1)

  • Output: i_12.id, t_6.items_id, i_12.type, dn_3.rso, ((t_6.name || '/'::text) || (dn_3.name)::text)
  • Inner Unique: true
  • Buffers: shared hit=1,277,433
188. 84.715 417.087 ↓ 2,250.4 128,270 1

Nested Loop (cost=2.19..163.81 rows=57 width=91) (actual time=17.881..417.087 rows=128,270 loops=1)

  • Output: t_6.items_id, t_6.name, dn_3.rso, dn_3.name, dn_3.items_id
  • Inner Unique: true
  • Buffers: shared hit=764,353
189. 32.115 204.102 ↓ 2,250.4 128,270 1

Nested Loop (cost=1.77..138.63 rows=57 width=48) (actual time=17.874..204.102 rows=128,270 loops=1)

  • Output: t_6.items_id, t_6.name, di_3.name_id
  • Buffers: shared hit=251,273
190. 19.628 46.661 ↓ 2,410.1 62,663 1

Hash Join (cost=1.34..6.35 rows=26 width=48) (actual time=17.852..46.661 rows=62,663 loops=1)

  • Output: t_6.items_id, t_6.name, m_9.nrevs_id
  • Hash Cond: (m_9.items_id = t_6.items_id)
191. 9.192 9.192 ↓ 313.3 62,663 1

CTE Scan on dir_match m_9 (cost=0.00..4.00 rows=200 width=16) (actual time=0.001..9.192 rows=62,663 loops=1)

  • Output: m_9.revs_id, m_9.nrevs_id, m_9.items_id, m_9.lname
192. 7.506 17.841 ↓ 2,410.1 62,663 1

Hash (cost=1.02..1.02 rows=26 width=40) (actual time=17.841..17.841 rows=62,663 loops=1)

  • Output: t_6.items_id, t_6.name
  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 5,235kB
193. 10.335 10.335 ↓ 2,410.1 62,663 1

CTE Scan on dir_tree t_6 (cost=0.00..1.02 rows=26 width=40) (actual time=0.002..10.335 rows=62,663 loops=1)

  • Output: t_6.items_id, t_6.name
  • Filter: (NOT t_6.ignore)
194. 125.326 125.326 ↑ 1.0 2 62,663

Index Only Scan using dirs_items_prevs_id_name_id_index on public.dirs_items di_3 (cost=0.42..5.07 rows=2 width=16) (actual time=0.002..0.002 rows=2 loops=62,663)

  • Output: di_3.prevs_id, di_3.name_id
  • Index Cond: (di_3.prevs_id = m_9.nrevs_id)
  • Heap Fetches: 128,270
  • Buffers: shared hit=251,273
195. 128.270 128.270 ↑ 1.0 1 128,270

Index Scan using dirs_items_name_pkey on public.dirs_items_name dn_3 (cost=0.42..0.44 rows=1 width=59) (actual time=0.001..0.001 rows=1 loops=128,270)

  • Output: dn_3.id, dn_3.items_id, dn_3.name, dn_3.rso
  • Index Cond: (dn_3.id = di_3.name_id)
  • Buffers: shared hit=513,080
196. 128.270 128.270 ↑ 1.0 1 128,270

Index Scan using items_pkey on public.items i_12 (cost=0.42..0.46 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=128,270)

  • Output: i_12.id, i_12.type, i_12.populate, i_12.date
  • Index Cond: (i_12.id = dn_3.items_id)
  • Filter: ((i_12.type <> 'D'::bpchar) AND (i_12.type <> 'R'::bpchar))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=513,080
197.          

CTE file_haveperms

198. 0.002 0.016 ↓ 0.0 0 1

Result (cost=4.08..4.73 rows=1 width=37) (actual time=0.016..0.016 rows=0 loops=1)

  • Output: m_10.items_id, s_2.readaccess, s_2.owner, s_2.grp
  • One-Time Filter: ($48 = 0)
  • Buffers: shared hit=1
199.          

Initplan (for Result)

200. 0.004 0.014 ↑ 1.0 1 1

Aggregate (cost=1.04..1.05 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1)

  • Output: count(user_group_roles_2.rolename)
  • Buffers: shared hit=1
201. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on public.user_group_roles user_group_roles_2 (cost=0.00..1.03 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=1)

  • Output: user_group_roles_2.id, user_group_roles_2.username, user_group_roles_2.groupname, user_group_roles_2.rolename
  • Filter: (((user_group_roles_2.username)::text = $1) AND ((user_group_roles_2.rolename)::text = 'ADMIN'::text) AND ((user_group_roles_2.groupname)::text = 'all'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1
202. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=4.08..4.73 rows=1 width=37) (never executed)

  • Output: m_10.items_id, s_2.readaccess, s_2.owner, s_2.grp
  • Inner Unique: true
  • Hash Cond: (m_10.items_id = s_2.id)
203. 0.000 0.000 ↓ 0.0 0

CTE Scan on the_files m_10 (cost=0.00..0.58 rows=29 width=8) (never executed)

  • Output: m_10.items_id, m_10.pitems_id, m_10.type, m_10.rso, m_10.name
204. 0.000 0.000 ↓ 0.0 0

Hash (cost=3.01..3.01 rows=1 width=37) (never executed)

  • Output: s_2.readaccess, s_2.owner, s_2.grp, s_2.id
205. 0.000 0.000 ↓ 0.0 0

Index Only Scan using items_src_readaccess_noworld_index on public.items_src s_2 (cost=0.12..3.01 rows=1 width=37) (never executed)

  • Output: s_2.readaccess, s_2.owner, s_2.grp, s_2.id
  • Heap Fetches: 0
206.          

CTE file_obj_denied

207. 0.001 0.018 ↓ 0.0 0 1

Append (cost=0.00..6.80 rows=3 width=8) (actual time=0.018..0.018 rows=0 loops=1)

  • Buffers: shared hit=1
208. 0.016 0.016 ↓ 0.0 0 1

CTE Scan on file_haveperms m_11 (cost=0.00..0.03 rows=1 width=8) (actual time=0.016..0.016 rows=0 loops=1)

  • Output: m_11.items_id
  • Filter: (((m_11.owner)::text <> $1) AND ((m_11.readaccess)::text = 'user'::text))
  • Buffers: shared hit=1
209. 0.001 0.001 ↓ 0.0 0 1

Nested Loop Anti Join (cost=2.78..2.84 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: m_12.items_id
  • Join Filter: ((g_2.groupname)::text = (m_12.grp)::text)
210. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on file_haveperms m_12 (cost=0.00..0.03 rows=1 width=40) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: m_12.items_id, m_12.readaccess, m_12.owner, m_12.grp
  • Filter: (((m_12.grp)::text <> 'all_my_groups'::text) AND ((m_12.owner)::text <> $1) AND ((m_12.readaccess)::text = 'group'::text))
211. 0.000 0.000 ↓ 0.0 0

Unique (cost=2.78..2.79 rows=1 width=32) (never executed)

  • Output: g_2.groupname
212. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.78..2.79 rows=1 width=32) (never executed)

  • Output: g_2.groupname
  • Sort Key: g_2.groupname
213. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..2.77 rows=1 width=32) (never executed)

  • Output: g_2.groupname
  • Join Filter: ((g_2.rolename)::text = (r_9.rolename)::text)
214. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.user_group_roles g_2 (cost=0.00..1.02 rows=1 width=64) (never executed)

  • Output: g_2.id, g_2.username, g_2.groupname, g_2.rolename
  • Filter: ((g_2.username)::text = $1)
215. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.roles r_9 (cost=0.00..1.71 rows=3 width=6) (never executed)

  • Output: r_9.id, r_9.rolename, r_9.commands
  • Filter: (r_9.commands = 'populate'::text)
216. 0.000 0.000 ↓ 0.0 0 1

Nested Loop Anti Join (cost=3.83..3.88 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: m_13.items_id
  • Join Filter: ((g_3.username)::text = (m_13.owner)::text)
217. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on file_haveperms m_13 (cost=0.00..0.03 rows=1 width=40) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: m_13.items_id, m_13.readaccess, m_13.owner, m_13.grp
  • Filter: (((m_13.owner)::text <> $1) AND ((m_13.readaccess)::text = 'group'::text) AND ((m_13.grp)::text = 'all_my_groups'::text))
218. 0.000 0.000 ↓ 0.0 0

Unique (cost=3.83..3.83 rows=1 width=32) (never executed)

  • Output: g_3.username
219. 0.000 0.000 ↓ 0.0 0

Sort (cost=3.83..3.83 rows=1 width=32) (never executed)

  • Output: g_3.username
  • Sort Key: g_3.username
220. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..3.82 rows=1 width=32) (never executed)

  • Output: g_3.username
  • Join Filter: ((mg_1.rolename)::text = (r_10.rolename)::text)
221. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..2.07 rows=1 width=64) (never executed)

  • Output: g_3.username, mg_1.rolename
  • Join Filter: ((g_3.groupname)::text = (mg_1.groupname)::text)
222. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.user_group_roles g_3 (cost=0.00..1.02 rows=1 width=64) (never executed)

  • Output: g_3.id, g_3.username, g_3.groupname, g_3.rolename
  • Filter: ((g_3.username)::text <> $1)
223. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.user_group_roles mg_1 (cost=0.00..1.03 rows=1 width=64) (never executed)

  • Output: mg_1.id, mg_1.username, mg_1.groupname, mg_1.rolename
  • Filter: (((mg_1.groupname)::text <> 'all_my_groups'::text) AND ((mg_1.username)::text = $1))
224. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.roles r_10 (cost=0.00..1.71 rows=3 width=6) (never executed)

  • Output: r_10.id, r_10.rolename, r_10.commands
  • Filter: (r_10.commands = 'populate'::text)
225.          

CTE file_match

226. 8.092 806.479 ↓ 21,869.3 65,608 1

Hash Right Join (cost=78,157.39..78,159.74 rows=3 width=56) (actual time=798.399..806.479 rows=65,608 loops=1)

  • Output: COALESCE(c_1.revs_id, m_14.revs_id), COALESCE(c_1.nrevs_id, m_14.revs_id), m_14.items_id, m_14.lname
  • Inner Unique: true
  • Hash Cond: (c_1.items_id = m_14.items_id)
  • Buffers: shared hit=1,293,531 read=255
227. 0.002 0.002 ↑ 103.0 1 1

CTE Scan on cur_items c_1 (cost=0.00..2.06 rows=103 width=24) (actual time=0.002..0.002 rows=1 loops=1)

  • Output: c_1.revs_id, c_1.nrevs_id, c_1.items_id, c_1.type
228. 7.247 798.385 ↓ 21,869.3 65,608 1

Hash (cost=78,157.36..78,157.36 rows=3 width=48) (actual time=798.385..798.385 rows=65,608 loops=1)

  • Output: m_14.revs_id, m_14.items_id, m_14.lname
  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 4,420kB
  • Buffers: shared hit=1,293,531 read=255
229. 4.936 791.138 ↓ 21,869.3 65,608 1

Subquery Scan on m_14 (cost=78,154.33..78,157.36 rows=3 width=48) (actual time=775.135..791.138 rows=65,608 loops=1)

  • Output: m_14.revs_id, m_14.items_id, m_14.lname
  • Buffers: shared hit=1,293,531 read=255
230. 6.753 786.202 ↓ 21,869.3 65,608 1

Unique (cost=78,154.33..78,157.33 rows=3 width=56) (actual time=775.133..786.202 rows=65,608 loops=1)

  • Output: m_15.items_id, m_15.mrevs_id, r_11.lname, r_11.priority
  • Buffers: shared hit=1,293,531 read=255
231. 25.463 779.449 ↓ 109.3 65,608 1

Sort (cost=78,154.33..78,155.83 rows=600 width=56) (actual time=775.132..779.449 rows=65,608 loops=1)

  • Output: m_15.items_id, m_15.mrevs_id, r_11.lname, r_11.priority
  • Sort Key: m_15.items_id, r_11.priority
  • Sort Method: quicksort Memory: 8,198kB
  • Buffers: shared hit=1,293,531 read=255
232. 5.979 753.986 ↓ 109.3 65,608 1

Nested Loop (cost=23.18..78,126.64 rows=600 width=56) (actual time=0.513..753.986 rows=65,608 loops=1)

  • Output: m_15.items_id, m_15.mrevs_id, r_11.lname, r_11.priority
  • Buffers: shared hit=1,293,531 read=255
233. 0.005 0.470 ↑ 200.0 1 1

HashAggregate (cost=22.75..24.75 rows=200 width=82) (actual time=0.469..0.470 rows=1 loops=1)

  • Output: r_11.ltype, r_11.labels_id, r_11.priority, r_11.lname, r_11.itype
  • Group Key: r_11.ltype, r_11.labels_id, r_11.priority, r_11.lname, r_11.itype
  • Buffers: shared hit=1
234. 0.465 0.465 ↑ 1,000.0 1 1

Function Scan on public.rsoids r_11 (cost=0.25..10.25 rows=1,000 width=82) (actual time=0.464..0.465 rows=1 loops=1)

  • Output: r_11.labels_id, r_11.lname, r_11.ltype, r_11.priority, r_11.itype
  • Function Call: rsoids($4, 'F'::bpchar)
  • Buffers: shared hit=1
235. 5.046 747.537 ↓ 21,869.3 65,608 1

Append (cost=0.42..390.47 rows=3 width=16) (actual time=0.041..747.537 rows=65,608 loops=1)

  • Buffers: shared hit=1,293,530 read=255
236. 6.517 742.479 ↓ 65,608.0 65,608 1

Result (cost=0.42..133.27 rows=1 width=16) (actual time=0.041..742.479 rows=65,608 loops=1)

  • Output: m_15.items_id, m_15.mrevs_id
  • One-Time Filter: (($6 = 0) AND (r_11.ltype = 0))
  • Buffers: shared hit=1,293,530 read=255
237. 37.515 735.962 ↓ 65,608.0 65,608 1

Nested Loop (cost=0.42..133.27 rows=1 width=16) (actual time=0.040..735.962 rows=65,608 loops=1)

  • Output: m_15.items_id, m_15.mrevs_id
  • Join Filter: (i_13.type = t_7.itype)
  • Rows Removed by Join Filter: 131,216
  • Buffers: shared hit=1,293,530 read=255
238. 13.929 698.447 ↓ 65,608.0 65,608 1

Nested Loop Left Join (cost=0.42..131.01 rows=1 width=24) (actual time=0.034..698.447 rows=65,608 loops=1)

  • Output: i_13.type, m_15.items_id, m_15.mrevs_id
  • Join Filter: ((b_2.terminated OR m_15.terminated) AND (b_2.labels_id = m_15.branch_id))
  • Filter: (b_2.id IS NULL)
  • Buffers: shared hit=1,293,530 read=255
239. 0.000 684.518 ↓ 2,262.3 65,608 1

Nested Loop (cost=0.42..129.56 rows=29 width=33) (actual time=0.029..684.518 rows=65,608 loops=1)

  • Output: i_13.type, m_15.items_id, m_15.mrevs_id, m_15.branch_id, m_15.terminated
  • Buffers: shared hit=1,293,529 read=255
240. 556.269 556.269 ↓ 2,262.3 65,608 1

CTE Scan on the_files i_13 (cost=0.00..0.58 rows=29 width=16) (actual time=0.000..556.269 rows=65,608 loops=1)

  • Output: i_13.items_id, i_13.pitems_id, i_13.type, i_13.rso, i_13.name
  • Buffers: shared hit=1,031,352
241. 131.216 131.216 ↑ 1.0 1 65,608

Index Scan using maxrev_items_id_branch_id_index on public.maxrev m_15 (cost=0.42..4.44 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=65,608)

  • Output: m_15.id, m_15.branch_id, m_15.items_id, m_15.mrevs_id, m_15.terminated
  • Index Cond: ((m_15.items_id = i_13.items_id) AND (m_15.branch_id = r_11.labels_id))
  • Buffers: shared hit=262,177 read=255
242. 0.000 0.000 ↓ 0.0 0 65,608

Materialize (cost=0.00..1.02 rows=1 width=17) (actual time=0.000..0.000 rows=0 loops=65,608)

  • Output: b_2.labels_id, b_2.terminated, b_2.id
  • Buffers: shared hit=1
243. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on public.branch_attrs b_2 (cost=0.00..1.01 rows=1 width=17) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: b_2.labels_id, b_2.terminated, b_2.id
  • Filter: (b_2.ign_if_term AND (b_2.labels_id = r_11.labels_id))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1
244. 0.000 0.000 ↑ 33.3 3 65,608

Function Scan on public.sos_unnest t_7 (cost=0.00..1.00 rows=100 width=32) (actual time=0.000..0.000 rows=3 loops=65,608)

  • Output: t_7.itype
  • Function Call: sos_unnest(r_11.itype)
245. 0.001 0.009 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=241.60..241.62 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: "*SELECT* 2_1".items_id, "*SELECT* 2_1".revs_id
246. 0.000 0.008 ↓ 0.0 0 1

Unique (cost=241.60..241.61 rows=1 width=28) (actual time=0.008..0.008 rows=0 loops=1)

  • Output: r_12.items_id, r_12.id, (r_11.priority), r_12.revision
247. 0.006 0.008 ↓ 0.0 0 1

Sort (cost=241.60..241.61 rows=1 width=28) (actual time=0.008..0.008 rows=0 loops=1)

  • Output: r_12.items_id, r_12.id, (r_11.priority), r_12.revision
  • Sort Key: r_12.items_id, r_12.revision DESC
  • Sort Method: quicksort Memory: 25kB
248. 0.002 0.002 ↓ 0.0 0 1

Result (cost=0.97..241.59 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: r_12.items_id, r_12.id, r_11.priority, r_12.revision
  • One-Time Filter: (($6 <> 0) AND (r_11.ltype = 0))
249. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.97..241.59 rows=1 width=20) (never executed)

  • Output: r_12.items_id, r_12.id, r_12.revision
  • Join Filter: (i_14.type = t_8.itype)
250. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.96..239.33 rows=1 width=28) (never executed)

  • Output: r_12.items_id, r_12.id, r_12.revision, i_14.type
  • Filter: (b_3.id IS NULL)
251. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..221.15 rows=29 width=36) (never executed)

  • Output: i_14.type, r_12.items_id, r_12.id, r_12.revision, r_12.branch_id
252. 0.000 0.000 ↓ 0.0 0

CTE Scan on the_files i_14 (cost=0.00..0.58 rows=29 width=16) (never executed)

  • Output: i_14.items_id, i_14.pitems_id, i_14.type, i_14.rso, i_14.name
253. 0.000 0.000 ↓ 0.0 0

Index Scan using revs_items_id_index on public.revs r_12 (cost=0.42..7.60 rows=1 width=28) (never executed)

  • Output: r_12.id, r_12.items_id, r_12.branch_id, r_12.ancestor_id, r_12.changeset_id, r_12.version, r_12.revision, r_12.lck_cnt, r_12.checkinby, r_12.checkintime, r_12.basename, r_12.checkinlog, r_12.checkoutby, r_12.checkouttime, r_12.checkoutlog, r_12.merge_from_rev, r_12.cattrs
  • Index Cond: (r_12.items_id = i_14.items_id)
  • Filter: ((r_12.revision > '-1'::integer) AND (r_12.branch_id = r_11.labels_id) AND (date_trunc('second'::text, r_12.checkintime) <= to_timestamp(($6)::double precision)))
254. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.54..0.62 rows=1 width=24) (never executed)

  • Output: b_3.labels_id, b_3.id, m_16.items_id
  • Join Filter: (m_16.terminated OR b_3.terminated)
255. 0.000 0.000 ↓ 0.0 0

Index Scan using branch_attrs_pkey on public.branch_attrs b_3 (cost=0.12..0.15 rows=1 width=17) (never executed)

  • Output: b_3.id, b_3.labels_id, b_3.snap_labels_id, b_3.parent_lid, b_3.ign_if_term, b_3.terminated, b_3.proj_bra
  • Index Cond: ((b_3.labels_id = r_12.branch_id) AND (b_3.labels_id = r_11.labels_id))
  • Filter: b_3.ign_if_term
256. 0.000 0.000 ↓ 0.0 0

Index Scan using maxrev_items_id_branch_id_index on public.maxrev m_16 (cost=0.42..0.46 rows=1 width=17) (never executed)

  • Output: m_16.id, m_16.branch_id, m_16.items_id, m_16.mrevs_id, m_16.terminated
  • Index Cond: ((m_16.items_id = r_12.items_id) AND (m_16.branch_id = r_11.labels_id))
257. 0.000 0.000 ↓ 0.0 0

Function Scan on public.sos_unnest t_8 (cost=0.00..1.00 rows=100 width=32) (never executed)

  • Output: t_8.itype
  • Function Call: sos_unnest(r_11.itype)
258. 0.000 0.003 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3_1 (cost=15.54..15.56 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: "*SELECT* 3_1".items_id, "*SELECT* 3_1".revs_id
259. 0.000 0.003 ↓ 0.0 0 1

Unique (cost=15.54..15.55 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: a_1.items_id, a_1.revs_id, a_1.labels_id, a_1.id
260. 0.003 0.003 ↓ 0.0 0 1

Sort (cost=15.54..15.55 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: a_1.items_id, a_1.revs_id, a_1.labels_id, a_1.id
  • Sort Key: a_1.items_id, a_1.id DESC
  • Sort Method: quicksort Memory: 25kB
261. 0.000 0.000 ↓ 0.0 0 1

Result (cost=9.67..15.53 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: a_1.items_id, a_1.revs_id, a_1.labels_id, a_1.id
  • One-Time Filter: (r_11.ltype > 0)
262. 0.000 0.000 ↓ 0.0 0

Nested Loop Anti Join (cost=9.67..15.53 rows=1 width=32) (never executed)

  • Output: a_1.items_id, a_1.revs_id, a_1.labels_id, a_1.id
263. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.49..9.87 rows=1 width=32) (never executed)

  • Output: a_1.items_id, a_1.revs_id, a_1.labels_id, a_1.id
  • Hash Cond: (t_9.itype = i_15.type)
264. 0.000 0.000 ↓ 0.0 0

Function Scan on public.sos_unnest t_9 (cost=0.00..1.00 rows=100 width=32) (never executed)

  • Output: t_9.itype
  • Function Call: sos_unnest(r_11.itype)
265. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.47..8.47 rows=1 width=40) (never executed)

  • Output: i_15.type, a_1.items_id, a_1.revs_id, a_1.labels_id, a_1.id
266. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=7.78..8.47 rows=1 width=40) (never executed)

  • Output: i_15.type, a_1.items_id, a_1.revs_id, a_1.labels_id, a_1.id
  • Hash Cond: (i_15.items_id = a_1.items_id)
267. 0.000 0.000 ↓ 0.0 0

CTE Scan on the_files i_15 (cost=0.00..0.58 rows=29 width=16) (never executed)

  • Output: i_15.items_id, i_15.pitems_id, i_15.type, i_15.rso, i_15.name
268. 0.000 0.000 ↓ 0.0 0

Hash (cost=7.75..7.75 rows=2 width=32) (never executed)

  • Output: a_1.items_id, a_1.revs_id, a_1.labels_id, a_1.id
269. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on public.revlabels a_1 (cost=2.18..7.75 rows=2 width=32) (never executed)

  • Output: a_1.items_id, a_1.revs_id, a_1.labels_id, a_1.id
  • Recheck Cond: (a_1.labels_id = r_11.labels_id)
  • Filter: CASE WHEN ($6 <> 0) THEN (date_trunc('second'::text, a_1.date) <= to_timestamp(($6)::double precision)) ELSE true END
270. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on revlabels_labels_id_items_id_index (cost=0.00..2.18 rows=4 width=0) (never executed)

  • Index Cond: (a_1.labels_id = r_11.labels_id)
271. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on public.revlabels_bo d_2 (cost=1.18..5.64 rows=2 width=8) (never executed)

  • Output: d_2.id, d_2.revlabels_id, d_2.labels_id, d_2.items_id, d_2.revs_id, d_2.owner, d_2.date
  • Recheck Cond: (d_2.revlabels_id = a_1.id)
  • Filter: CASE WHEN ($6 <> 0) THEN (date_trunc('second'::text, d_2.date) <= to_timestamp(($6)::double precision)) ELSE true END
272. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on revlabels_bo_revlabels_id_index (cost=0.00..1.18 rows=4 width=0) (never executed)

  • Index Cond: (d_2.revlabels_id = a_1.id)
273.          

CTE all_items

274. 5.570 3,762.295 ↓ 1,603.4 128,271 1

Append (cost=1.66..9.24 rows=80 width=129) (actual time=2,771.875..3,762.295 rows=128,271 loops=1)

  • Buffers: shared hit=7,291,660 read=12,790
275. 18.092 2,794.280 ↓ 1,228.7 62,663 1

Hash Right Join (cost=1.66..6.92 rows=51 width=129) (actual time=2,771.875..2,794.280 rows=62,663 loops=1)

  • Output: t_10.items_id, t_10.pitems_id, m_17.revs_id, t_10.type, m_17.lname, t_10.deny, t_10.rso, t_10.name
  • Hash Cond: (m_17.items_id = t_10.items_id)
  • Buffers: shared hit=5,752,047 read=12,535
276. 4.326 4.326 ↓ 313.3 62,663 1

CTE Scan on dir_match m_17 (cost=0.00..4.00 rows=200 width=48) (actual time=0.001..4.326 rows=62,663 loops=1)

  • Output: m_17.revs_id, m_17.nrevs_id, m_17.items_id, m_17.lname
277. 9.830 2,771.862 ↓ 1,228.7 62,663 1

Hash (cost=1.02..1.02 rows=51 width=89) (actual time=2,771.861..2,771.862 rows=62,663 loops=1)

  • Output: t_10.items_id, t_10.pitems_id, t_10.type, t_10.deny, t_10.rso, t_10.name
  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 5,908kB
  • Buffers: shared hit=5,752,047 read=12,535
278. 2,762.032 2,762.032 ↓ 1,228.7 62,663 1

CTE Scan on dir_tree t_10 (cost=0.00..1.02 rows=51 width=89) (actual time=0.048..2,762.032 rows=62,663 loops=1)

  • Output: t_10.items_id, t_10.pitems_id, t_10.type, t_10.deny, t_10.rso, t_10.name
  • Buffers: shared hit=5,752,047 read=12,535
279. 6.948 962.445 ↓ 2,262.3 65,608 1

Hash Left Join (cost=0.20..1.12 rows=29 width=129) (actual time=931.593..962.445 rows=65,608 loops=1)

  • Output: f.items_id, f.pitems_id, m_18.revs_id, f.type, m_18.lname, (COALESCE(p_1.items_id, '0'::bigint) <> 0), f.rso, f.name
  • Hash Cond: (f.items_id = p_1.items_id)
  • Buffers: shared hit=1,539,613 read=255
280. 19.313 955.478 ↓ 2,262.3 65,608 1

Hash Left Join (cost=0.10..0.82 rows=29 width=128) (actual time=931.565..955.478 rows=65,608 loops=1)

  • Output: f.items_id, f.pitems_id, f.type, f.rso, f.name, m_18.revs_id, m_18.lname
  • Hash Cond: (f.items_id = m_18.items_id)
  • Buffers: shared hit=1,539,612 read=255
281. 107.111 107.111 ↓ 2,262.3 65,608 1

CTE Scan on the_files f (cost=0.00..0.58 rows=29 width=88) (actual time=102.502..107.111 rows=65,608 loops=1)

  • Output: f.items_id, f.pitems_id, f.type, f.rso, f.name
  • Buffers: shared hit=246,081
282. 7.727 829.054 ↓ 21,869.3 65,608 1

Hash (cost=0.06..0.06 rows=3 width=48) (actual time=829.054..829.054 rows=65,608 loops=1)

  • Output: m_18.revs_id, m_18.lname, m_18.items_id
  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 4,612kB
  • Buffers: shared hit=1,293,531 read=255
283. 821.327 821.327 ↓ 21,869.3 65,608 1

CTE Scan on file_match m_18 (cost=0.00..0.06 rows=3 width=48) (actual time=798.401..821.327 rows=65,608 loops=1)

  • Output: m_18.revs_id, m_18.lname, m_18.items_id
  • Buffers: shared hit=1,293,531 read=255
284. 0.000 0.019 ↓ 0.0 0 1

Hash (cost=0.06..0.06 rows=3 width=8) (actual time=0.019..0.019 rows=0 loops=1)

  • Output: p_1.items_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1
285. 0.019 0.019 ↓ 0.0 0 1

CTE Scan on file_obj_denied p_1 (cost=0.00..0.06 rows=3 width=8) (actual time=0.019..0.019 rows=0 loops=1)

  • Output: p_1.items_id
  • Buffers: shared hit=1
286.          

CTE the_labels

287. 0.001 0.020 ↓ 0.0 0 1

GroupAggregate (cost=12.11..12.16 rows=1 width=72) (actual time=0.020..0.020 rows=0 loops=1)

  • Output: a_2.items_id, string_agg((l_1.name)::text, ','::text) FILTER (WHERE (l_1.type > 1)), string_agg((l_1.name)::text, ','::text) FILTER (WHERE (l_1.type = 1))
  • Group Key: a_2.items_id
  • Buffers: shared hit=1
288. 0.000 0.019 ↓ 0.0 0 1

Unique (cost=12.11..12.12 rows=1 width=58) (actual time=0.019..0.019 rows=0 loops=1)

  • Output: a_2.items_id, l_1.type, l_1.name, a_2.labels_id, a_2.id
  • Buffers: shared hit=1
289.          

Initplan (for Unique)

290. 0.001 0.011 ↑ 1.0 1 1

Aggregate (cost=1.02..1.03 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1)

  • Output: count(c_2.id)
  • Buffers: shared hit=1
291. 0.010 0.010 ↓ 0.0 0 1

Seq Scan on public.labels c_2 (cost=0.00..1.02 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=1)

  • Output: c_2.id, c_2.name, c_2.type, c_2.description, c_2.active, c_2.createdby, c_2.createtime, c_2.updatedby, c_2.updatetime, c_2.export, c_2.tagancestor
  • Filter: c_2.export
  • Rows Removed by Filter: 2
  • Buffers: shared hit=1
292. 0.004 0.018 ↓ 0.0 0 1

Sort (cost=11.08..11.09 rows=1 width=58) (actual time=0.018..0.018 rows=0 loops=1)

  • Output: a_2.items_id, l_1.type, l_1.name, a_2.labels_id, a_2.id
  • Sort Key: a_2.items_id, a_2.labels_id, a_2.id DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1
293. 0.014 0.014 ↓ 0.0 0 1

Result (cost=8.97..11.07 rows=1 width=58) (actual time=0.014..0.014 rows=0 loops=1)

  • Output: a_2.items_id, l_1.type, l_1.name, a_2.labels_id, a_2.id
  • One-Time Filter: ($68 > 0)
  • Buffers: shared hit=1
294. 0.000 0.000 ↓ 0.0 0

Nested Loop Anti Join (cost=8.97..11.07 rows=1 width=58) (never executed)

  • Output: l_1.type, l_1.name, a_2.items_id, a_2.labels_id, a_2.id
295. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.82..10.73 rows=1 width=58) (never executed)

  • Output: l_1.type, l_1.name, a_2.items_id, a_2.labels_id, a_2.id
  • Hash Cond: (m_19.items_id = a_2.items_id)
296. 0.000 0.000 ↓ 0.0 0

CTE Scan on all_items m_19 (cost=0.00..1.60 rows=80 width=8) (never executed)

  • Output: m_19.items_id, m_19.pitems_id, m_19.revs_id, m_19.type, m_19.lname, m_19.deny, m_19.rso, m_19.name
297. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.79..8.79 rows=2 width=58) (never executed)

  • Output: l_1.type, l_1.name, a_2.items_id, a_2.labels_id, a_2.id
298. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.18..8.79 rows=2 width=58) (never executed)

  • Output: l_1.type, l_1.name, a_2.items_id, a_2.labels_id, a_2.id
299. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.labels l_1 (cost=0.00..1.02 rows=1 width=42) (never executed)

  • Output: l_1.id, l_1.name, l_1.type, l_1.description, l_1.active, l_1.createdby, l_1.createtime, l_1.updatedby, l_1.updatetime, l_1.export, l_1.tagancestor
  • Filter: (l_1.active AND l_1.export)
300. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on public.revlabels a_2 (cost=2.18..7.75 rows=2 width=24) (never executed)

  • Output: a_2.id, a_2.labels_id, a_2.items_id, a_2.revs_id, a_2.owner, a_2.date
  • Recheck Cond: (a_2.labels_id = l_1.id)
  • Filter: CASE WHEN ($6 <> 0) THEN (date_trunc('second'::text, a_2.date) <= to_timestamp(($6)::double precision)) ELSE true END
301. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on revlabels_labels_id_items_id_index (cost=0.00..2.18 rows=4 width=0) (never executed)

  • Index Cond: (a_2.labels_id = l_1.id)
302. 0.000 0.000 ↓ 0.0 0

Index Scan using revlabels_bo_revlabels_id_index on public.revlabels_bo d_3 (cost=0.15..0.32 rows=2 width=8) (never executed)

  • Output: d_3.id, d_3.revlabels_id, d_3.labels_id, d_3.items_id, d_3.revs_id, d_3.owner, d_3.date
  • Index Cond: (d_3.revlabels_id = a_2.id)
  • Filter: CASE WHEN ($6 <> 0) THEN (date_trunc('second'::text, d_3.date) <= to_timestamp(($6)::double precision)) ELSE true END
303. 3,801.746 3,801.746 ↓ 1,603.4 128,271 1

CTE Scan on all_items r (cost=0.00..1.60 rows=80 width=129) (actual time=2,771.877..3,801.746 rows=128,271 loops=1)

  • Output: r.items_id, r.pitems_id, r.revs_id, r.type, r.lname, r.deny, r.rso, r.name
  • Buffers: shared hit=7,291,660 read=12,790
304. 0.000 0.020 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=72) (actual time=0.020..0.020 rows=0 loops=1)

  • Output: l.tagsexp, l.snapshotsexp, l.items_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1
305. 0.020 0.020 ↓ 0.0 0 1

CTE Scan on the_labels l (cost=0.00..0.02 rows=1 width=72) (actual time=0.020..0.020 rows=0 loops=1)

  • Output: l.tagsexp, l.snapshotsexp, l.items_id
  • Buffers: shared hit=1