explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mmY

Settings
# exclusive inclusive rows x rows loops node
1. 0.248 142,269.283 ↑ 1.0 200 1

Limit (cost=106,964.04..106,965.04 rows=200 width=4) (actual time=142,268.597..142,269.283 rows=200 loops=1)

  • Buffers: shared hit=70,375,079 read=366,997, temp read=6,010 written=11,506
2. 0.263 142,269.035 ↑ 12.7 200 1

Unique (cost=106,964.04..106,976.69 rows=2,531 width=4) (actual time=142,268.594..142,269.035 rows=200 loops=1)

  • Buffers: shared hit=70,375,079 read=366,997, temp read=6,010 written=11,506
3. 3,218.496 142,268.772 ↑ 12.7 200 1

Sort (cost=106,964.04..106,970.36 rows=2,531 width=4) (actual time=142,268.591..142,268.772 rows=200 loops=1)

  • Sort Key: comment.xlinkid DESC
  • Sort Method: external merge Disk: 44,368kB
  • Buffers: shared hit=70,375,079 read=366,997, temp read=6,010 written=11,506
4. 8,506.315 139,050.276 ↓ 1,277.2 3,232,620 1

Nested Loop (cost=5.45..106,820.96 rows=2,531 width=4) (actual time=316.394..139,050.276 rows=3,232,620 loops=1)

  • Join Filter: (((gf.groupid = 1,050) OR ((comment.contentrole)::text <> 'Comment'::text) OR ((comment.contentrole)::text <> 'File Attachment'::text)) AND ((((comment.contentrole)::text ~~ 'XRef
  • Rows Removed by Join Filter: 304,000
  • Buffers: shared hit=70,375,076 read=366,997
5. 6,002.272 121,702.426 ↓ 372.3 1,768,307 1

Nested Loop (cost=5.01..93,469.11 rows=4,750 width=55) (actual time=253.154..121,702.426 rows=1,768,307 loops=1)

  • Join Filter: ((lfx.role IS NULL) OR (lfx.xlinkid = comment.xlinkid) OR (((comment.contentrole)::text <> 'Comment'::text) AND ((comment.contentrole)::text <> 'File Attachment'::text)))
  • Buffers: shared hit=63,353,117 read=303,549
6. 15,353.683 102,440.796 ↓ 322.9 4,419,786 1

Nested Loop (cost=4.58..59,721.25 rows=13,688 width=30) (actual time=132.531..102,440.796 rows=4,419,786 loops=1)

  • Buffers: shared hit=45,759,886 read=217,636
7. 8,206.783 50,378.039 ↓ 703.0 4,078,786 1

Nested Loop (cost=1.72..19,474.49 rows=5,802 width=26) (actual time=68.396..50,378.039 rows=4,078,786 loops=1)

  • Buffers: shared hit=17,176,277 read=168,845
8. 4,389.967 25,152.634 ↓ 522.7 2,836,437 1

Nested Loop (cost=1.28..5,354.58 rows=5,427 width=4) (actual time=30.535..25,152.634 rows=2,836,437 loops=1)

  • Buffers: shared hit=5,095,792 read=114,878
9. 1,277.107 7,745.845 ↓ 517.7 500,647 1

Nested Loop (cost=0.85..2,377.47 rows=967 width=8) (actual time=30.501..7,745.845 rows=500,647 loops=1)

  • Buffers: shared hit=1,949,901 read=56,301
10. 656.735 1,462.258 ↓ 517.7 500,648 1

Nested Loop (cost=0.42..239.34 rows=967 width=4) (actual time=30.470..1,462.258 rows=500,648 loops=1)

  • Buffers: shared hit=5 read=3,605
11. 28.897 28.897 ↑ 1.0 2 1

Seq Scan on dgroupuri_for_dgroup gufg (cost=0.00..38.14 rows=2 width=4) (actual time=28.882..28.897 rows=2 loops=1)

  • Filter: (groupid = 1,050)
  • Rows Removed by Filter: 2,089
  • Buffers: shared hit=2 read=10
12. 776.626 776.626 ↓ 297.7 250,324 2

Index Scan using ufg_groupuriid_idx on uri_for_dgroupuri ufgu (cost=0.42..92.19 rows=841 width=8) (actual time=0.801..388.313 rows=250,324 loops=2)

  • Index Cond: (groupuriid = gufg.groupuriid)
  • Buffers: shared hit=3 read=3,595
13. 5,006.480 5,006.480 ↑ 1.0 1 500,648

Index Scan using uri_pkey on uri u (cost=0.42..2.21 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=500,648)

  • Index Cond: (uriid = ufgu.uriid)
  • Filter: ((path)::text <> '/ps/servlet/com.pageseeder.general/1050'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1,949,896 read=52,696
14. 13,016.822 13,016.822 ↑ 1.2 6 500,647

Index Scan using locator_uriid_idx on locator l (cost=0.43..3.01 rows=7 width=8) (actual time=0.008..0.026 rows=6 loops=500,647)

  • Index Cond: (uriid = ufgu.uriid)
  • Buffers: shared hit=3,145,891 read=58,577
15. 17,018.622 17,018.622 ↑ 2.0 1 2,836,437

Index Scan using locatorid_idx on locator_for_xlink lfx (cost=0.43..2.58 rows=2 width=30) (actual time=0.004..0.006 rows=1 loops=2,836,437)

  • Index Cond: (locatorid = l.locatorid)
  • Filter: ((((role)::text ~~ 'xref-source%'::text) AND ((label)::text <> 'image'::text)) OR (role IS NULL))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=12,080,485 read=53,967
16. 12,236.358 36,709.074 ↑ 3.0 1 4,078,786

Bitmap Heap Scan on xlink_for_xlink xfx1 (cost=2.86..6.91 rows=3 width=8) (actual time=0.008..0.009 rows=1 loops=4,078,786)

  • Recheck Cond: ((lfx.xlinkid = replytoid) OR (lfx.xlinkid = replyid))
  • Heap Blocks: exact=4,158,797
  • Buffers: shared hit=28,583,609 read=48,791
17. 8,157.572 24,472.716 ↓ 0.0 0 4,078,786

BitmapOr (cost=2.86..2.86 rows=3 width=0) (actual time=0.006..0.006 rows=0 loops=4,078,786)

  • Buffers: shared hit=24,446,598 read=27,005
18. 8,157.572 8,157.572 ↑ 2.0 1 4,078,786

Bitmap Index Scan on xfx_replytoid_idx (cost=0.00..1.43 rows=2 width=0) (actual time=0.002..0.002 rows=1 loops=4,078,786)

  • Index Cond: (replytoid = lfx.xlinkid)
  • Buffers: shared hit=12,223,741 read=13,504
19. 8,157.572 8,157.572 ↑ 1.0 1 4,078,786

Bitmap Index Scan on xfx_replyid_idx (cost=0.00..1.43 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=4,078,786)

  • Index Cond: (replyid = lfx.xlinkid)
  • Buffers: shared hit=12,222,857 read=13,501
20. 13,259.358 13,259.358 ↓ 0.0 0 4,419,786

Index Scan using xlink_pkey on xlink comment (cost=0.43..2.45 rows=1 width=29) (actual time=0.003..0.003 rows=0 loops=4,419,786)

  • Index Cond: (xlinkid = xfx1.replyid)
  • Filter: (accepted AND (creationdate > '2020-01-01 00:00:00'::timestamp without time zone) AND (((contentrole)::text ~~ 'XRef%'::text) OR ((status IS NULL) AND ((contentrole)::text
  • Rows Removed by Filter: 1
  • Buffers: shared hit=17,593,231 read=85,913
21. 8,841.535 8,841.535 ↑ 1.0 2 1,768,307

Index Scan using gfx_xlinkid_idx on dgroup_for_xlink gf (cost=0.44..2.75 rows=2 width=8) (actual time=0.004..0.005 rows=2 loops=1,768,307)

  • Index Cond: (xlinkid = xfx1.replyid)
  • Buffers: shared hit=7,021,959 read=63,448
Planning time : 13.741 ms
Execution time : 142,276.094 ms