explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RKC7

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 28,740.987 ↑ 1.0 6 1

HashAggregate (cost=785,086.21..785,086.27 rows=6 width=17) (actual time=28,740.985..28,740.987 rows=6 loops=1)

  • Output: l0.settings_unum, p2.lobj_id
  • Group Key: l0.settings_unum, p2.lobj_id
  • Buffers: shared hit=29,205,830 read=147,129, temp read=18,644 written=15,820
  • I/O Timings: read=2,892.135
2. 152.434 28,740.959 ↑ 1.0 6 1

Hash Right Join (cost=785,023.06..785,086.18 rows=6 width=17) (actual time=27,896.245..28,740.959 rows=6 loops=1)

  • Output: l0.settings_unum, p2.lobj_id
  • Hash Cond: (ln1.childlobj_id = l0.lobj_id)
  • Buffers: shared hit=29,205,830 read=147,129, temp read=18,644 written=15,820
  • I/O Timings: read=2,892.135
3. 2,724.602 28,588.398 ↓ 952.0 2,529,534 1

HashAggregate (cost=784,987.97..785,014.54 rows=2,657 width=16) (actual time=27,652.506..28,588.398 rows=2,529,534 loops=1)

  • Output: ln1.childlobj_id, p2.lobj_id
  • Group Key: ln1.childlobj_id, p2.lobj_id
  • Buffers: shared hit=29,205,805 read=147,124, temp read=18,644 written=15,820
  • I/O Timings: read=2,892.087
4. 232.196 25,863.796 ↓ 952.0 2,529,536 1

Append (cost=218,057.99..784,974.68 rows=2,657 width=16) (actual time=2,119.221..25,863.796 rows=2,529,536 loops=1)

  • Buffers: shared hit=29,205,805 read=147,124, temp read=18,644 written=15,820
  • I/O Timings: read=2,892.087
5. 548.615 14,607.578 ↓ 980.1 2,529,536 1

Nested Loop (cost=218,057.99..384,547.47 rows=2,581 width=16) (actual time=2,119.220..14,607.578 rows=2,529,536 loops=1)

  • Output: ln1.childlobj_id, p2.lobj_id
  • Buffers: shared hit=18,935,991 read=43,749, temp read=18,644 written=15,820
  • I/O Timings: read=316.915
6. 44.187 8,999.877 ↓ 613.7 2,529,543 1

Nested Loop (cost=218,057.56..380,307.47 rows=4,122 width=16) (actual time=2,119.208..8,999.877 rows=2,529,543 loops=1)

  • Output: ln1.childlobj_id, ln2.parentlobj_id
  • Join Filter: (ln1.parentlobj_id = p1.lobj_id)
  • Buffers: shared hit=8,785,238 read=43,585, temp read=18,644 written=15,820
  • I/O Timings: read=315.806
7. 761.937 3,896.604 ↓ 388.8 2,529,543 1

Merge Join (cost=218,057.13..375,153.62 rows=6,506 width=32) (actual time=2,119.182..3,896.604 rows=2,529,543 loops=1)

  • Output: ln1.childlobj_id, ln1.parentlobj_id, ln2.childlobj_id, ln2.parentlobj_id
  • Merge Cond: (ln1.parentlobj_id = ln2.childlobj_id)
  • Buffers: shared hit=515,239 read=43,580, temp read=18,644 written=15,820
  • I/O Timings: read=315.758
8. 710.031 710.031 ↓ 15.8 2,519,324 1

Index Only Scan using pk_objectlink on dalimes.objectlink ln1 (cost=0.56..153,910.46 rows=159,060 width=16) (actual time=1.368..710.031 rows=2,519,324 loops=1)

  • Output: ln1.parentotpl_id, ln1.parentlobj_id, ln1.ts_deletion, ln1.childotpl_id, ln1.childlobj_id, ln1.olnk_id
  • Index Cond: ((ln1.parentotpl_id = 58) AND (ln1.ts_deletion = 0) AND (ln1.childotpl_id = 61))
  • Heap Fetches: 209,221
  • Buffers: shared hit=373,924 read=23,611
  • I/O Timings: read=189.996
9. 1,879.005 2,424.636 ↓ 4.6 2,529,607 1

Sort (cost=218,054.40..219,417.56 rows=545,263 width=16) (actual time=2,117.740..2,424.636 rows=2,529,607 loops=1)

  • Output: ln2.childlobj_id, ln2.parentlobj_id
  • Sort Key: ln2.childlobj_id
  • Sort Method: external sort Disk: 63,280kB
  • Buffers: shared hit=141,315 read=19,969, temp read=17,232 written=15,820
  • I/O Timings: read=125.762
10. 545.631 545.631 ↓ 4.0 2,157,610 1

Index Only Scan using pk_objectlink on dalimes.objectlink ln2 (cost=0.56..166,100.13 rows=545,263 width=16) (actual time=0.028..545.631 rows=2,157,610 loops=1)

  • Output: ln2.childlobj_id, ln2.parentlobj_id
  • Index Cond: ((ln2.parentotpl_id = 19) AND (ln2.ts_deletion = 0) AND (ln2.childotpl_id = 58))
  • Heap Fetches: 139,938
  • Buffers: shared hit=141,315 read=19,969
  • I/O Timings: read=125.762
11. 5,059.086 5,059.086 ↑ 1.0 1 2,529,543

Index Only Scan using ixu_o_pageorder_idlist on dalimes.o_pageorder p1 (cost=0.43..0.78 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2,529,543)

  • Output: p1.ts_deletion, p1.lobj_id
  • Index Cond: ((p1.ts_deletion = 0) AND (p1.lobj_id = ln2.childlobj_id))
  • Heap Fetches: 666,646
  • Buffers: shared hit=8,269,999 read=5
  • I/O Timings: read=0.048
12. 5,059.086 5,059.086 ↑ 1.0 1 2,529,543

Index Only Scan using ixu_o_pagelayout_idlist on dalimes.o_pagelayout p2 (cost=0.43..1.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2,529,543)

  • Output: p2.ts_deletion, p2.lobj_id
  • Index Cond: ((p2.ts_deletion = 0) AND (p2.lobj_id = ln2.parentlobj_id))
  • Heap Fetches: 2,542,127
  • Buffers: shared hit=10,150,753 read=164
  • I/O Timings: read=1.109
13. 0.000 11,024.022 ↓ 0.0 0 1

Nested Loop (cost=139,600.95..400,400.64 rows=76 width=16) (actual time=11,024.022..11,024.022 rows=0 loops=1)

  • Output: ln1_1.childlobj_id, p3.lobj_id
  • Buffers: shared hit=10,269,814 read=103,375
  • I/O Timings: read=2,575.172
14. 0.002 11,024.022 ↓ 0.0 0 1

Nested Loop (cost=139,600.52..400,275.15 rows=122 width=16) (actual time=11,024.022..11,024.022 rows=0 loops=1)

  • Output: ln1_1.childlobj_id, ln3.parentlobj_id
  • Buffers: shared hit=10,269,814 read=103,375
  • I/O Timings: read=2,575.172
15. 0.000 11,024.020 ↓ 0.0 0 1

Nested Loop (cost=139,599.96..396,867.13 rows=2,987 width=24) (actual time=11,024.020..11,024.020 rows=0 loops=1)

  • Output: ln1_1.childlobj_id, ln2_1.parentlobj_id, p2_1.lobj_id
  • Buffers: shared hit=10,269,814 read=103,375
  • I/O Timings: read=2,575.172
16. 462.773 11,024.020 ↓ 0.0 0 1

Nested Loop (cost=139,599.53..393,342.85 rows=4,707 width=16) (actual time=11,024.020..11,024.020 rows=0 loops=1)

  • Output: ln1_1.childlobj_id, ln2_1.parentlobj_id
  • Buffers: shared hit=10,269,814 read=103,375
  • I/O Timings: read=2,575.172
17. 1,264.418 3,003.875 ↓ 25.0 2,519,124 1

Hash Join (cost=139,598.96..278,192.27 rows=100,925 width=24) (actual time=1,075.744..3,003.875 rows=2,519,124 loops=1)

  • Output: ln1_1.childlobj_id, ln1_1.parentlobj_id, p1_1.lobj_id
  • Hash Cond: (ln1_1.parentlobj_id = p1_1.lobj_id)
  • Buffers: shared hit=258,832 read=17,791
  • I/O Timings: read=201.833
18. 668.551 668.551 ↓ 15.8 2,519,324 1

Index Only Scan using ix_objectlink_child_id on dalimes.objectlink ln1_1 (cost=0.56..136,789.32 rows=159,060 width=16) (actual time=0.019..668.551 rows=2,519,324 loops=1)

  • Output: ln1_1.childotpl_id, ln1_1.childlobj_id, ln1_1.ts_deletion, ln1_1.parentotpl_id, ln1_1.parentlobj_id
  • Index Cond: ((ln1_1.childotpl_id = 61) AND (ln1_1.ts_deletion = 0) AND (ln1_1.parentotpl_id = 58))
  • Heap Fetches: 209,221
  • Buffers: shared hit=175,451 read=17,791
  • I/O Timings: read=201.833
19. 496.037 1,070.906 ↑ 1.0 2,147,856 1

Hash (cost=111,497.20..111,497.20 rows=2,248,096 width=8) (actual time=1,070.906..1,070.906 rows=2,147,856 loops=1)

  • Output: p1_1.lobj_id
  • Buckets: 4,194,304 Batches: 1 Memory Usage: 116,669kB
  • Buffers: shared hit=83,381
20. 574.869 574.869 ↑ 1.0 2,147,856 1

Seq Scan on dalimes.o_pageorder p1_1 (cost=0.00..111,497.20 rows=2,248,096 width=8) (actual time=0.016..574.869 rows=2,147,856 loops=1)

  • Output: p1_1.lobj_id
  • Filter: (p1_1.ts_deletion = 0)
  • Rows Removed by Filter: 975
  • Buffers: shared hit=83,381
21. 7,557.372 7,557.372 ↓ 0.0 0 2,519,124

Index Only Scan using ix_objectlink_child_id on dalimes.objectlink ln2_1 (cost=0.56..1.13 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=2,519,124)

  • Output: ln2_1.childotpl_id, ln2_1.childlobj_id, ln2_1.ts_deletion, ln2_1.parentotpl_id, ln2_1.parentlobj_id
  • Index Cond: ((ln2_1.childotpl_id = 58) AND (ln2_1.childlobj_id = p1_1.lobj_id) AND (ln2_1.ts_deletion = 0) AND (ln2_1.parentotpl_id = 58))
  • Heap Fetches: 0
  • Buffers: shared hit=10,010,982 read=85,584
  • I/O Timings: read=2,373.339
22. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ixu_o_pageorder_idlist on dalimes.o_pageorder p2_1 (cost=0.43..0.74 rows=1 width=8) (never executed)

  • Output: p2_1.ts_deletion, p2_1.lobj_id
  • Index Cond: ((p2_1.ts_deletion = 0) AND (p2_1.lobj_id = ln2_1.parentlobj_id))
  • Heap Fetches: 0
23. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ix_objectlink_child_id on dalimes.objectlink ln3 (cost=0.56..1.13 rows=1 width=16) (never executed)

  • Output: ln3.childotpl_id, ln3.childlobj_id, ln3.ts_deletion, ln3.parentotpl_id, ln3.parentlobj_id
  • Index Cond: ((ln3.childotpl_id = 58) AND (ln3.childlobj_id = p2_1.lobj_id) AND (ln3.ts_deletion = 0) AND (ln3.parentotpl_id = 19))
  • Heap Fetches: 0
24. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ixu_o_pagelayout_idlist on dalimes.o_pagelayout p3 (cost=0.43..1.02 rows=1 width=8) (never executed)

  • Output: p3.ts_deletion, p3.lobj_id
  • Index Cond: ((p3.ts_deletion = 0) AND (p3.lobj_id = ln3.parentlobj_id))
  • Heap Fetches: 0
25. 0.005 0.127 ↑ 1.0 6 1

Hash (cost=35.02..35.02 rows=6 width=17) (actual time=0.127..0.127 rows=6 loops=1)

  • Output: l0.settings_unum, l0.lobj_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=25 read=5
  • I/O Timings: read=0.048
26. 0.122 0.122 ↑ 1.0 6 1

Index Only Scan using ix_o_filebag_unum on dalimes.o_filebag l0 (cost=0.56..35.02 rows=6 width=17) (actual time=0.060..0.122 rows=6 loops=1)

  • Output: l0.settings_unum, l0.lobj_id
  • Index Cond: ((l0.settings_unum = ANY ('{ES9179577,ES9179588,ES9179568,ES9179558,ES9179591,ES9179578}'::text[])) AND (l0.ts_deletion = 0))
  • Heap Fetches: 6
  • Buffers: shared hit=25 read=5
  • I/O Timings: read=0.048
Planning time : 6.407 ms
Execution time : 28,753.269 ms