explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9fVL

Settings
# exclusive inclusive rows x rows loops node
1. 88.522 5,609.210 ↓ 6.6 128,271 1

Hash Left Join (cost=12,440.35..16,099.14 rows=19,375 width=831) (actual time=4,971.470..5,609.210 rows=128,271 loops=1)

  • Output: the_items.items_id, the_items.revs_id, the_items.type, the_items.name, the_items.labelmatch, the_items.pitems_id, the_items.deny, the_items.rso, the_items.tagsexp, the_items.snapshotsexp, items_src.description, items_src.owner, items_src.grp, items_src.readaccess, items_src.writeaccess, items_src.icon, items_src.alias, items_src.trigger, items_src.vendorkey, items_src.packagetype, items_src.rcpgm, items_src.reference, items_src.user1, items_src.user2, items_src.user3, items_src.prop1, items_src.prop2, items_src.prop3, items_src.prop4, items_src.prop5, items_src.prop6, items_src.prop7, items_src.prop8, items_src.prop9, items_src.writable, items_src.keywordsub, the_revs.branch_id, the_revs.ancestor_id, the_revs.basename, the_revs.version, the_revs.revision, the_revs.checkinby, the_revs.checkintime, the_revs.checkinlog, the_revs.cattrs, the_revs.branch, term.mrevs_id, term.terminated, term.latestrev, (unm.items_id IS NULL), lcks.lckowner, lcks.lckinwrk, lcks.checkoutby, lcks.checkouttime, lcks.checkoutpath, lcks.checkoutlog, r.project, r.rtype, r.ritems_id, r.ralias, the_items.rso, d.def_rso, r.rpath
  • Hash Cond: (the_items.revs_id = the_revs.revs_id)
  • Buffers: shared hit=9,854,473 read=16,758
2.          

CTE the_items

3. 3,908.245 3,908.245 ↓ 128.3 128,271 1

Function Scan on public.allrevs t (cost=0.25..10.25 rows=1,000 width=217) (actual time=3,889.771..3,908.245 rows=128,271 loops=1)

  • Output: t.items_id, t.revs_id, t.type, t.name, t.labelmatch, t.pitems_id, t.deny, t.rso, t.tagsexp, t.snapshotsexp
  • Function Call: allrevs('prashant'::text, '1596002365'::bigint, '2'::bigint, 'main'::text, './lib_asdfly1176'::text, 0, '{693382}'::bigint[], '{}'::bigint[])
  • Buffers: shared hit=7,293,012 read=12,793
4.          

CTE the_revs

5. 20.301 379.672 ↓ 128.3 128,271 1

Unique (cost=3,768.85..3,773.85 rows=1,000 width=100) (actual time=345.261..379.672 rows=128,271 loops=1)

  • Output: r_1.id, r_1.items_id, r_1.branch_id, r_1.ancestor_id, r_1.basename, r_1.version, r_1.revision, r_1.checkinby, (trunc(date_part('epoch'::text, r_1.checkintime))), r_1.checkinlog, r_1.cattrs, l.name
  • Buffers: shared hit=511,544 read=1,541
6. 54.134 359.371 ↓ 128.3 128,271 1

Sort (cost=3,768.85..3,771.35 rows=1,000 width=100) (actual time=345.260..359.371 rows=128,271 loops=1)

  • Output: r_1.id, r_1.items_id, r_1.branch_id, r_1.ancestor_id, r_1.basename, r_1.version, r_1.revision, r_1.checkinby, (trunc(date_part('epoch'::text, r_1.checkintime))), r_1.checkinlog, r_1.cattrs, l.name
  • Sort Key: r_1.id
  • Sort Method: quicksort Memory: 21,111kB
  • Buffers: shared hit=511,544 read=1,541
7. 42.043 305.237 ↓ 128.3 128,271 1

Nested Loop (cost=0.42..3,719.03 rows=1,000 width=100) (actual time=0.022..305.237 rows=128,271 loops=1)

  • Output: r_1.id, r_1.items_id, r_1.branch_id, r_1.ancestor_id, r_1.basename, r_1.version, r_1.revision, r_1.checkinby, trunc(date_part('epoch'::text, r_1.checkintime)), r_1.checkinlog, r_1.cattrs, l.name
  • Inner Unique: true
  • Join Filter: (r_1.branch_id = l.id)
  • Buffers: shared hit=511,544 read=1,541
8. 78.751 263.194 ↓ 128.3 128,271 1

Nested Loop (cost=0.42..3,688.00 rows=1,000 width=68) (actual time=0.010..263.194 rows=128,271 loops=1)

  • Output: r_1.id, r_1.items_id, r_1.branch_id, r_1.ancestor_id, r_1.basename, r_1.version, r_1.revision, r_1.checkinby, r_1.checkintime, r_1.checkinlog, r_1.cattrs
  • Inner Unique: true
  • Buffers: shared hit=511,543 read=1,541
9. 56.172 56.172 ↓ 128.3 128,271 1

CTE Scan on the_items i (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.000..56.172 rows=128,271 loops=1)

  • Output: i.items_id, i.revs_id, i.type, i.name, i.labelmatch, i.pitems_id, i.deny, i.rso, i.tagsexp, i.snapshotsexp
10. 128.271 128.271 ↑ 1.0 1 128,271

Index Scan using revs_pkey on public.revs r_1 (cost=0.42..3.67 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=128,271)

  • 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 = i.revs_id)
  • Buffers: shared hit=511,543 read=1,541
11. 0.000 0.000 ↑ 2.0 1 128,271

Materialize (cost=0.00..1.03 rows=2 width=40) (actual time=0.000..0.000 rows=1 loops=128,271)

  • Output: l.name, l.id
  • Buffers: shared hit=1
12. 0.006 0.006 ↑ 2.0 1 1

Seq Scan on public.labels l (cost=0.00..1.02 rows=2 width=40) (actual time=0.005..0.006 rows=1 loops=1)

  • Output: l.name, l.id
  • Buffers: shared hit=1
13.          

CTE the_terminate

14. 43.251 795.503 ↓ 25,654.2 128,271 1

Nested Loop (cost=0.84..2,827.42 rows=5 width=21) (actual time=345.285..795.503 rows=128,271 loops=1)

  • Output: m.items_id, m.mrevs_id, (m.terminated OR COALESCE(b.terminated, false)), mr.revision
  • Inner Unique: true
  • Buffers: shared hit=1,537,478 read=1,776
15. 37.450 623.981 ↓ 25,654.2 128,271 1

Nested Loop Left Join (cost=0.42..2,824.59 rows=5 width=18) (actual time=345.280..623.981 rows=128,271 loops=1)

  • Output: m.items_id, m.mrevs_id, m.terminated, b.terminated
  • Join Filter: (b.labels_id = m.branch_id)
  • Buffers: shared hit=1,024,394 read=1,776
16. 61.237 586.531 ↓ 25,654.2 128,271 1

Nested Loop (cost=0.42..2,823.50 rows=5 width=25) (actual time=345.273..586.531 rows=128,271 loops=1)

  • Output: m.items_id, m.mrevs_id, m.terminated, m.branch_id
  • Buffers: shared hit=1,024,393 read=1,776
17. 397.023 397.023 ↓ 128.3 128,271 1

CTE Scan on the_revs r_2 (cost=0.00..20.00 rows=1,000 width=16) (actual time=345.262..397.023 rows=128,271 loops=1)

  • Output: r_2.revs_id, r_2.items_id, r_2.branch_id, r_2.ancestor_id, r_2.basename, r_2.version, r_2.revision, r_2.checkinby, r_2.checkintime, r_2.checkinlog, r_2.cattrs, r_2.branch
  • Buffers: shared hit=511,544 read=1,541
18. 128.271 128.271 ↑ 1.0 1 128,271

Index Scan using maxrev_items_id_branch_id_index on public.maxrev m (cost=0.42..2.79 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=128,271)

  • Output: m.id, m.branch_id, m.items_id, m.mrevs_id, m.terminated
  • Index Cond: ((m.items_id = r_2.items_id) AND (m.branch_id = r_2.branch_id))
  • Buffers: shared hit=512,849 read=235
19. 0.000 0.000 ↑ 1.0 1 128,271

Materialize (cost=0.00..1.01 rows=1 width=9) (actual time=0.000..0.000 rows=1 loops=128,271)

  • Output: b.terminated, b.labels_id
  • Buffers: shared hit=1
20. 0.005 0.005 ↑ 1.0 1 1

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

  • Output: b.terminated, b.labels_id
  • Buffers: shared hit=1
21. 128.271 128.271 ↑ 1.0 1 128,271

Index Scan using revs_pkey on public.revs mr (cost=0.42..0.57 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=128,271)

  • Output: mr.id, mr.items_id, mr.branch_id, mr.ancestor_id, mr.changeset_id, mr.version, mr.revision, mr.lck_cnt, mr.checkinby, mr.checkintime, mr.basename, mr.checkinlog, mr.checkoutby, mr.checkouttime, mr.checkoutlog, mr.merge_from_rev, mr.cattrs
  • Index Cond: (mr.id = m.mrevs_id)
  • Buffers: shared hit=513,084
22.          

CTE the_unmerged

23. 0.000 190.200 ↓ 0.0 0 1

Group (cost=2,859.96..2,864.98 rows=500 width=8) (actual time=190.199..190.200 rows=0 loops=1)

  • Output: m_1.items_id
  • Group Key: m_1.items_id
  • Buffers: shared hit=513,085
24. 0.001 190.200 ↓ 0.0 0 1

Merge Anti Join (cost=2,859.96..2,863.73 rows=500 width=8) (actual time=190.199..190.200 rows=0 loops=1)

  • Output: m_1.items_id
  • Merge Cond: ((m_1.items_id = mr_1.items_id) AND (m_1.mrevs_id = mr_1.merge_from_rev))
  • Buffers: shared hit=513,085
25. 0.002 190.199 ↓ 0.0 0 1

Sort (cost=2,856.94..2,858.19 rows=500 width=16) (actual time=190.198..190.199 rows=0 loops=1)

  • Output: m_1.items_id, m_1.mrevs_id
  • Sort Key: m_1.items_id, m_1.mrevs_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=513,085
26. 5.232 190.197 ↓ 0.0 0 1

Nested Loop (cost=0.42..2,834.53 rows=500 width=16) (actual time=190.196..190.197 rows=0 loops=1)

  • Output: m_1.items_id, m_1.mrevs_id
  • Join Filter: (m_1.branch_id = l_1.id)
  • Rows Removed by Join Filter: 128,271
  • Buffers: shared hit=513,085
27. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on public.labels l_1 (cost=0.00..1.02 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=1)

  • 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.name)::text <> 'main'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1
28. 45.184 184.960 ↓ 128.3 128,271 1

Nested Loop (cost=0.42..2,821.00 rows=1,000 width=24) (actual time=0.006..184.960 rows=128,271 loops=1)

  • Output: m_1.items_id, m_1.mrevs_id, m_1.branch_id
  • Buffers: shared hit=513,084
29. 11.505 11.505 ↓ 128.3 128,271 1

CTE Scan on the_items i_1 (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.000..11.505 rows=128,271 loops=1)

  • Output: i_1.items_id, i_1.revs_id, i_1.type, i_1.name, i_1.labelmatch, i_1.pitems_id, i_1.deny, i_1.rso, i_1.tagsexp, i_1.snapshotsexp
30. 128.271 128.271 ↑ 1.0 1 128,271

Index Scan using maxrev_items_id_branch_id_index on public.maxrev m_1 (cost=0.42..2.79 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=128,271)

  • Output: m_1.id, m_1.branch_id, m_1.items_id, m_1.mrevs_id, m_1.terminated
  • Index Cond: (m_1.items_id = i_1.items_id)
  • Filter: (NOT m_1.terminated)
  • Buffers: shared hit=513,084
31. 0.000 0.000 ↓ 0.0 0

Sort (cost=3.02..3.03 rows=1 width=16) (never executed)

  • Output: mr_1.items_id, mr_1.merge_from_rev
  • Sort Key: mr_1.items_id, mr_1.merge_from_rev
32. 0.000 0.000 ↓ 0.0 0

Index Only Scan using revs_merge_from_rev_index on public.revs mr_1 (cost=0.12..3.01 rows=1 width=16) (never executed)

  • Output: mr_1.items_id, mr_1.merge_from_rev
  • Heap Fetches: 0
33.          

CTE the_locks

34. 0.002 0.025 ↓ 0.0 0 1

HashAggregate (cost=2,864.66..2,868.66 rows=200 width=138) (actual time=0.025..0.025 rows=0 loops=1)

  • Output: r_3.revs_id, bool_or((((nr.checkoutby)::text = 'prashant'::text))), bool_or(((l_2.wid = 1,596,002,365))), string_agg((nr.checkoutby)::text, ','::text), string_agg(((trunc(date_part('epoch'::text, nr.checkouttime)))::text), ','::text), string_agg((l_2.checkoutpath)::text, ','::text), string_agg(nr.checkoutlog, ','::text)
  • Group Key: r_3.revs_id
  • Buffers: shared hit=3
35. 0.017 0.023 ↓ 0.0 0 1

Sort (cost=2,773.16..2,780.79 rows=3,050 width=96) (actual time=0.023..0.023 rows=0 loops=1)

  • Output: r_3.revs_id, (((nr.checkoutby)::text = 'prashant'::text)), ((l_2.wid = 1,596,002,365)), l_2.checkoutpath, nr.checkoutby, ((trunc(date_part('epoch'::text, nr.checkouttime)))::text), nr.checkoutlog, (CASE l_2.wid WHEN 1596002365 THEN 0 ELSE 1 END), l_2.date
  • Sort Key: (CASE l_2.wid WHEN 1596002365 THEN 0 ELSE 1 END), l_2.date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3
36. 0.003 0.006 ↓ 0.0 0 1

Hash Join (cost=32.92..2,596.65 rows=3,050 width=96) (actual time=0.003..0.006 rows=0 loops=1)

  • Output: r_3.revs_id, ((nr.checkoutby)::text = 'prashant'::text), (l_2.wid = 1,596,002,365), l_2.checkoutpath, nr.checkoutby, (trunc(date_part('epoch'::text, nr.checkouttime)))::text, nr.checkoutlog, CASE l_2.wid WHEN 1596002365 THEN 0 ELSE 1 END, l_2.date
  • Hash Cond: (l_2.revs_id = r_3.revs_id)
37. 0.000 0.003 ↓ 0.0 0 1

Nested Loop (cost=0.42..2,402.50 rows=610 width=74) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: l_2.wid, l_2.checkoutpath, l_2.date, l_2.revs_id, nr.checkoutby, nr.checkouttime, nr.checkoutlog
  • Inner Unique: true
38. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on public.lock l_2 (cost=0.00..16.10 rows=610 width=64) (actual time=0.002..0.003 rows=0 loops=1)

  • Output: l_2.id, l_2.revs_id, l_2.next_revs_id, l_2.owner, l_2.date, l_2.wid, l_2.checkoutpath
39. 0.000 0.000 ↓ 0.0 0

Index Scan using revs_pkey on public.revs nr (cost=0.42..3.91 rows=1 width=26) (never executed)

  • Output: nr.id, nr.items_id, nr.branch_id, nr.ancestor_id, nr.changeset_id, nr.version, nr.revision, nr.lck_cnt, nr.checkinby, nr.checkintime, nr.basename, nr.checkinlog, nr.checkoutby, nr.checkouttime, nr.checkoutlog, nr.merge_from_rev, nr.cattrs
  • Index Cond: (nr.id = l_2.next_revs_id)
40. 0.000 0.000 ↓ 0.0 0

Hash (cost=20.00..20.00 rows=1,000 width=8) (never executed)

  • Output: r_3.revs_id
41. 0.000 0.000 ↓ 0.0 0

CTE Scan on the_revs r_3 (cost=0.00..20.00 rows=1,000 width=8) (never executed)

  • Output: r_3.revs_id
42. 34.661 5,476.770 ↓ 33.1 128,271 1

Hash Left Join (cost=62.68..3,033.66 rows=3,875 width=586) (actual time=4,927.535..5,476.770 rows=128,271 loops=1)

  • Output: the_items.items_id, the_items.revs_id, the_items.type, the_items.name, the_items.labelmatch, the_items.pitems_id, the_items.deny, the_items.rso, the_items.tagsexp, the_items.snapshotsexp, items_src.description, items_src.owner, items_src.grp, items_src.readaccess, items_src.writeaccess, items_src.icon, items_src.alias, items_src.trigger, items_src.vendorkey, items_src.packagetype, items_src.rcpgm, items_src.reference, items_src.user1, items_src.user2, items_src.user3, items_src.prop1, items_src.prop2, items_src.prop3, items_src.prop4, items_src.prop5, items_src.prop6, items_src.prop7, items_src.prop8, items_src.prop9, items_src.writable, items_src.keywordsub, term.mrevs_id, term.terminated, term.latestrev, unm.items_id, lcks.lckowner, lcks.lckinwrk, lcks.checkoutby, lcks.checkouttime, lcks.checkoutpath, lcks.checkoutlog, r.project, r.rtype, r.ritems_id, r.ralias, r.rpath, d.def_rso
  • Hash Cond: (the_items.items_id = unm.items_id)
  • Buffers: shared hit=9,854,473 read=16,758
43. 33.959 5,251.908 ↓ 82.8 128,271 1

Hash Left Join (cost=46.43..2,877.91 rows=1,550 width=578) (actual time=4,737.327..5,251.908 rows=128,271 loops=1)

  • Output: the_items.items_id, the_items.revs_id, the_items.type, the_items.name, the_items.labelmatch, the_items.pitems_id, the_items.deny, the_items.rso, the_items.tagsexp, the_items.snapshotsexp, items_src.description, items_src.owner, items_src.grp, items_src.readaccess, items_src.writeaccess, items_src.icon, items_src.alias, items_src.trigger, items_src.vendorkey, items_src.packagetype, items_src.rcpgm, items_src.reference, items_src.user1, items_src.user2, items_src.user3, items_src.prop1, items_src.prop2, items_src.prop3, items_src.prop4, items_src.prop5, items_src.prop6, items_src.prop7, items_src.prop8, items_src.prop9, items_src.writable, items_src.keywordsub, term.mrevs_id, term.terminated, term.latestrev, lcks.lckowner, lcks.lckinwrk, lcks.checkoutby, lcks.checkouttime, lcks.checkoutpath, lcks.checkoutlog, r.project, r.rtype, r.ritems_id, r.ralias, r.rpath, d.def_rso
  • Hash Cond: ((r.project)::text = (d.name)::text)
  • Buffers: shared hit=9,341,388 read=16,758
44. 34.510 5,217.946 ↓ 128.3 128,271 1

Hash Left Join (cost=29.45..2,804.18 rows=1,000 width=546) (actual time=4,737.320..5,217.946 rows=128,271 loops=1)

  • Output: the_items.items_id, the_items.revs_id, the_items.type, the_items.name, the_items.labelmatch, the_items.pitems_id, the_items.deny, the_items.rso, the_items.tagsexp, the_items.snapshotsexp, items_src.description, items_src.owner, items_src.grp, items_src.readaccess, items_src.writeaccess, items_src.icon, items_src.alias, items_src.trigger, items_src.vendorkey, items_src.packagetype, items_src.rcpgm, items_src.reference, items_src.user1, items_src.user2, items_src.user3, items_src.prop1, items_src.prop2, items_src.prop3, items_src.prop4, items_src.prop5, items_src.prop6, items_src.prop7, items_src.prop8, items_src.prop9, items_src.writable, items_src.keywordsub, term.mrevs_id, term.terminated, term.latestrev, lcks.lckowner, lcks.lckinwrk, lcks.checkoutby, lcks.checkouttime, lcks.checkoutpath, lcks.checkoutlog, r.project, r.rtype, r.ritems_id, r.ralias, r.rpath
  • Inner Unique: true
  • Hash Cond: (the_items.items_id = r.id)
  • Buffers: shared hit=9,341,388 read=16,758
45. 33.995 5,183.432 ↓ 128.3 128,271 1

Hash Left Join (cost=7.08..2,779.16 rows=1,000 width=434) (actual time=4,737.310..5,183.432 rows=128,271 loops=1)

  • Output: the_items.items_id, the_items.revs_id, the_items.type, the_items.name, the_items.labelmatch, the_items.pitems_id, the_items.deny, the_items.rso, the_items.tagsexp, the_items.snapshotsexp, items_src.description, items_src.owner, items_src.grp, items_src.readaccess, items_src.writeaccess, items_src.icon, items_src.alias, items_src.trigger, items_src.vendorkey, items_src.packagetype, items_src.rcpgm, items_src.reference, items_src.user1, items_src.user2, items_src.user3, items_src.prop1, items_src.prop2, items_src.prop3, items_src.prop4, items_src.prop5, items_src.prop6, items_src.prop7, items_src.prop8, items_src.prop9, items_src.writable, items_src.keywordsub, term.mrevs_id, term.terminated, term.latestrev, lcks.lckowner, lcks.lckinwrk, lcks.checkoutby, lcks.checkouttime, lcks.checkoutpath, lcks.checkoutlog
  • Hash Cond: (the_items.revs_id = lcks.revs_id)
  • Buffers: shared hit=9,341,388 read=16,758
46. 70.644 5,149.410 ↓ 128.3 128,271 1

Hash Left Join (cost=0.58..2,735.16 rows=1,000 width=304) (actual time=4,737.276..5,149.410 rows=128,271 loops=1)

  • Output: the_items.items_id, the_items.revs_id, the_items.type, the_items.name, the_items.labelmatch, the_items.pitems_id, the_items.deny, the_items.rso, the_items.tagsexp, the_items.snapshotsexp, items_src.description, items_src.owner, items_src.grp, items_src.readaccess, items_src.writeaccess, items_src.icon, items_src.alias, items_src.trigger, items_src.vendorkey, items_src.packagetype, items_src.rcpgm, items_src.reference, items_src.user1, items_src.user2, items_src.user3, items_src.prop1, items_src.prop2, items_src.prop3, items_src.prop4, items_src.prop5, items_src.prop6, items_src.prop7, items_src.prop8, items_src.prop9, items_src.writable, items_src.keywordsub, term.mrevs_id, term.terminated, term.latestrev
  • Hash Cond: (the_items.items_id = term.items_id)
  • Buffers: shared hit=9,341,385 read=16,758
47. 65.967 4,231.303 ↓ 128.3 128,271 1

Nested Loop Left Join (cost=0.42..2,731.00 rows=1,000 width=291) (actual time=3,889.805..4,231.303 rows=128,271 loops=1)

  • Output: the_items.items_id, the_items.revs_id, the_items.type, the_items.name, the_items.labelmatch, the_items.pitems_id, the_items.deny, the_items.rso, the_items.tagsexp, the_items.snapshotsexp, items_src.description, items_src.owner, items_src.grp, items_src.readaccess, items_src.writeaccess, items_src.icon, items_src.alias, items_src.trigger, items_src.vendorkey, items_src.packagetype, items_src.rcpgm, items_src.reference, items_src.user1, items_src.user2, items_src.user3, items_src.prop1, items_src.prop2, items_src.prop3, items_src.prop4, items_src.prop5, items_src.prop6, items_src.prop7, items_src.prop8, items_src.prop9, items_src.writable, items_src.keywordsub
  • Inner Unique: true
  • Buffers: shared hit=7,803,907 read=14,982
48. 3,908.794 3,908.794 ↓ 128.3 128,271 1

CTE Scan on the_items (cost=0.00..20.00 rows=1,000 width=217) (actual time=3,889.779..3,908.794 rows=128,271 loops=1)

  • Output: the_items.items_id, the_items.revs_id, the_items.type, the_items.name, the_items.labelmatch, the_items.pitems_id, the_items.deny, the_items.rso, the_items.tagsexp, the_items.snapshotsexp
  • Buffers: shared hit=7,293,012 read=12,793
49. 256.542 256.542 ↑ 1.0 1 128,271

Index Scan using items_src_pkey on public.items_src (cost=0.42..2.71 rows=1 width=82) (actual time=0.002..0.002 rows=1 loops=128,271)

  • Output: items_src.id, items_src.alias, items_src.owner, items_src.grp, items_src.readaccess, items_src.writeaccess, items_src.icon, items_src.subtype, items_src.trigger, items_src.reference, items_src.vendorkey, items_src.packagetype, items_src.rcpgm, items_src.user1, items_src.user2, items_src.user3, items_src.prop1, items_src.prop2, items_src.prop3, items_src.prop4, items_src.prop5, items_src.prop6, items_src.prop7, items_src.prop8, items_src.prop9, items_src.writable, items_src.description, items_src.keywordsub
  • Index Cond: (items_src.id = the_items.items_id)
  • Buffers: shared hit=510,895 read=2,189
50. 16.149 847.463 ↓ 25,654.2 128,271 1

Hash (cost=0.10..0.10 rows=5 width=21) (actual time=847.461..847.463 rows=128,271 loops=1)

  • Output: term.mrevs_id, term.terminated, term.latestrev, term.items_id
  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 8,039kB
  • Buffers: shared hit=1,537,478 read=1,776
51. 831.314 831.314 ↓ 25,654.2 128,271 1

CTE Scan on the_terminate term (cost=0.00..0.10 rows=5 width=21) (actual time=345.287..831.314 rows=128,271 loops=1)

  • Output: term.mrevs_id, term.terminated, term.latestrev, term.items_id
  • Buffers: shared hit=1,537,478 read=1,776
52. 0.001 0.027 ↓ 0.0 0 1

Hash (cost=4.00..4.00 rows=200 width=138) (actual time=0.025..0.027 rows=0 loops=1)

  • Output: lcks.lckowner, lcks.lckinwrk, lcks.checkoutby, lcks.checkouttime, lcks.checkoutpath, lcks.checkoutlog, lcks.revs_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=3
53. 0.026 0.026 ↓ 0.0 0 1

CTE Scan on the_locks lcks (cost=0.00..4.00 rows=200 width=138) (actual time=0.025..0.026 rows=0 loops=1)

  • Output: lcks.lckowner, lcks.lckinwrk, lcks.checkoutby, lcks.checkouttime, lcks.checkoutpath, lcks.checkoutlog, lcks.revs_id
  • Buffers: shared hit=3
54. 0.002 0.004 ↓ 0.0 0 1

Hash (cost=15.50..15.50 rows=550 width=120) (actual time=0.002..0.004 rows=0 loops=1)

  • Output: r.project, r.rtype, r.ritems_id, r.ralias, r.rpath, r.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
55. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on public.items_ref r (cost=0.00..15.50 rows=550 width=120) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: r.project, r.rtype, r.ritems_id, r.ralias, r.rpath, r.id
56. 0.001 0.003 ↓ 0.0 0 1

Hash (cost=13.10..13.10 rows=310 width=64) (actual time=0.001..0.003 rows=0 loops=1)

  • Output: d.def_rso, d.name
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
57. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on public.refprojects d (cost=0.00..13.10 rows=310 width=64) (actual time=0.001..0.002 rows=0 loops=1)

  • Output: d.def_rso, d.name
58. 0.000 190.201 ↓ 0.0 0 1

Hash (cost=10.00..10.00 rows=500 width=8) (actual time=190.200..190.201 rows=0 loops=1)

  • Output: unm.items_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=513,085
59. 190.202 190.202 ↓ 0.0 0 1

CTE Scan on the_unmerged unm (cost=0.00..10.00 rows=500 width=8) (actual time=190.200..190.202 rows=0 loops=1)

  • Output: unm.items_id
  • Buffers: shared hit=513,085
60. 25.644 43.918 ↓ 128.3 128,271 1

Hash (cost=20.00..20.00 rows=1,000 width=228) (actual time=43.918..43.918 rows=128,271 loops=1)

  • Output: the_revs.branch_id, the_revs.ancestor_id, the_revs.basename, the_revs.version, the_revs.revision, the_revs.checkinby, the_revs.checkintime, the_revs.checkinlog, the_revs.cattrs, the_revs.branch, the_revs.revs_id
  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 14,052kB
61. 18.274 18.274 ↓ 128.3 128,271 1

CTE Scan on the_revs (cost=0.00..20.00 rows=1,000 width=228) (actual time=0.002..18.274 rows=128,271 loops=1)

  • Output: the_revs.branch_id, the_revs.ancestor_id, the_revs.basename, the_revs.version, the_revs.revision, the_revs.checkinby, the_revs.checkintime, the_revs.checkinlog, the_revs.cattrs, the_revs.branch, the_revs.revs_id