explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lra3

Settings
# exclusive inclusive rows x rows loops node
1. 0.274 100,138.186 ↑ 1.0 200 1

Limit (cost=84,889.84..122,357.72 rows=200 width=4) (actual time=100,073.529..100,138.186 rows=200 loops=1)

  • Buffers: shared hit=132,258,117 read=110,557
2. 0.291 100,137.912 ↑ 16.8 200 1

Unique (cost=84,889.84..715,474.24 rows=3,366 width=4) (actual time=100,073.526..100,137.912 rows=200 loops=1)

  • Buffers: shared hit=132,258,117 read=110,557
3. 0.503 100,137.621 ↑ 16.8 200 1

Nested Loop (cost=84,889.84..715,465.83 rows=3,366 width=4) (actual time=100,073.523..100,137.621 rows=200 loops=1)

  • Join Filter: (ufgu.uriid = u.uriid)
  • Buffers: shared hit=132,258,117 read=110,557
4. 1.227 100,136.718 ↑ 16.8 200 1

Nested Loop (cost=84,889.42..708,293.23 rows=3,367 width=12) (actual time=100,073.483..100,136.718 rows=200 loops=1)

  • Join Filter: ((xfx1.replyid = comment.xlinkid) AND ((lfx.role IS NULL) OR (lfx.xlinkid = comment.xlinkid) OR (((comment.contentrole)::text <> 'Comment'::
  • Rows Removed by Join Filter: 200
  • Buffers: shared hit=132,257,321 read=110,553
5. 686.729 100,133.751 ↑ 63.8 580 1

Gather Merge (cost=84,888.98..616,947.47 rows=36,988 width=45) (actual time=100,073.383..100,133.751 rows=580 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=132,255,034 read=110,520
6. 3.818 99,447.022 ↑ 17.5 883 3 / 3

Merge Join (cost=83,888.96..611,678.11 rows=15,412 width=45) (actual time=99,442.159..99,447.022 rows=883 loops=3)

  • Merge Cond: (gf.xlinkid = xfx1.replyid)
  • Buffers: shared hit=132,255,034 read=110,520
  • -> Parallel Index Scan Backward using gfx_xlinkid_idx on dgroup_for_xlink gf (cost=0.44..506901.27 rows=8,268,112 width=8) (actual time=0.028
  • Buffers: shared hit=733 read=27
7. 5,123.596 99,443.204 ↑ 10.4 1,779 3 / 3

Sort (cost=83,860.00..83,906.22 rows=18,489 width=37) (actual time=99,441.835..99,443.204 rows=1,779 loops=3)

  • Sort Key: xfx1.replyid DESC
  • Sort Method: quicksort Memory: 454,936kB
  • Worker 0: Sort Method: quicksort Memory: 454,936kB
  • Worker 1: Sort Method: quicksort Memory: 454,936kB
  • Buffers: shared hit=132,254,301 read=110,493
8. 16,824.983 94,319.608 ↓ 245.3 4,534,786 3 / 3

Nested Loop (cost=4.16..82,549.65 rows=18,489 width=37) (actual time=0.400..94,319.608 rows=4,534,786 loops=3)

  • Buffers: shared hit=132,254,294 read=110,492
9. 25,023.546 36,518.165 ↓ 702.5 4,097,646 3 / 3

Nested Loop (cost=1.29..18,800.15 rows=5,833 width=33) (actual time=0.330..36,518.165 rows=4,097,646 loops=3)

  • Buffers: shared hit=45,966,745 read=61,352
  • -> Index Scan using locatorid_idx on locator_for_xlink lfx (cost=0.43..2.81 rows=4 width=29) (actual time=0.003..0.005 ro
10. 10,246.423 11,494.619 ↓ 522.7 2,836,438 3 / 3

Nested Loop (cost=0.86..3,339.51 rows=5,426 width=12) (actual time=0.287..11,494.619 rows=2,836,438 loops=3)

  • Buffers: shared hit=9,610,064 read=14,186
  • -> Index Scan using locator_uriid_idx on locator l (cost=0.43..3.11 rows=8 width=8) (actual time=0.003..0.010 rows=
  • 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=36,356,681 read=47,166
11. 1,248.196 1,248.196 ↓ 517.7 500,648 3 / 3

Nested Loop (cost=0.42..258.71 rows=967 width=4) (actual time=0.229..1,248.196 rows=500,648 loops=3)

  • Buffers: shared hit=7,225 read=3,607
  • -> Seq Scan on dgroupuri_for_dgroup gufg (cost=0.00..38.14 rows=2 width=4) (actual time=0.191..0.206 rows=2 l
  • Filter: (groupid = 1,050)
  • Rows Removed by Filter: 2,089
  • Buffers: shared hit=26 read=10
  • -> Index Scan using ufg_groupuriid_idx on uri_for_dgroupuri ufgu (cost=0.42..100.64 rows=964 width=8) (actual
  • Index Cond: (groupuriid = gufg.groupuriid)
  • Buffers: shared hit=7,199 read=3,597
  • Index Cond: (uriid = ufgu.uriid)
  • Buffers: shared hit=9,602,839 read=10,579
12. 12,292.938 40,976.460 ↑ 4.0 1 12,292,938 / 3

Bitmap Heap Scan on xlink_for_xlink xfx1 (cost=2.87..10.89 rows=4 width=8) (actual time=0.009..0.010 rows=1 loops=12,292,938)

  • Recheck Cond: ((lfx.xlinkid = replytoid) OR (lfx.xlinkid = replyid))
  • Heap Blocks: exact=4,191,882
  • Buffers: shared hit=86,287,549 read=49,140
13. 28,683.522 28,683.522 ↓ 0.0 0 12,292,938 / 3

BitmapOr (cost=2.87..2.87 rows=4 width=0) (actual time=0.007..0.007 rows=0 loops=12,292,938)

  • Buffers: shared hit=73,733,837 read=27,206
  • -> Bitmap Index Scan on xfx_replytoid_idx (cost=0.00..1.44 rows=3 width=0) (actual time=0.002..0.002 rows=1 loops=1
  • Index Cond: (replytoid = lfx.xlinkid)
  • Buffers: shared hit=36,868,628 read=13,599
  • -> 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=122
  • Index Cond: (replyid = lfx.xlinkid)
  • Buffers: shared hit=36,865,209 read=13,607
14. 1.740 1.740 ↑ 1.0 1 580

Index Scan using xlink_pkey on xlink comment (cost=0.43..2.44 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=580)

  • Index Cond: (xlinkid = gf.xlinkid)
  • Filter: (accepted AND (creationdate > '2020-01-01 00:00:00'::timestamp without time zone) AND (((contentrole)::text ~~ 'XRef%'::text) OR ((status I
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2,287 read=33
15. 0.400 0.400 ↑ 1.0 1 200

Index Scan using uri_pkey on uri u (cost=0.42..2.12 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=200)

  • Index Cond: (uriid = l.uriid)
  • Filter: ((path)::text <> '/ps/servlet/com.pageseeder.general/1050'::text)
  • Buffers: shared hit=796 read=4
Planning time : 8.577 ms
Execution time : 100,162.344 ms