explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9kZa

Settings
# exclusive inclusive rows x rows loops node
1. 0.295 103,795.792 ↑ 1.0 200 1

Limit (cost=84,430.27..121,780.97 rows=200 width=4) (actual time=103,703.009..103,795.792 rows=200 loops=1)

2. 0.333 103,795.497 ↑ 16.9 200 1

Unique (cost=84,430.27..714,723.44 rows=3,375 width=4) (actual time=103,703.006..103,795.497 rows=200 loops=1)

3. 0.558 103,795.164 ↑ 16.9 200 1

Nested Loop (cost=84,430.27..714,715.00 rows=3,375 width=4) (actual time=103,703.003..103,795.164 rows=200 loops=1)

  • Join Filter: (ufgu.uriid = u.uriid)
4. 1.364 103,794.206 ↑ 16.9 200 1

Nested Loop (cost=84,429.84..707,525.36 rows=3,375 width=12) (actual time=103,702.960..103,794.206 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
5. 733.194 103,791.102 ↑ 63.6 580 1

Gather Merge (cost=84,429.41..616,483.36 rows=36,865 width=45) (actual time=103,702.853..103,791.102 rows=580 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 20.409 103,057.908 ↑ 18.5 832 3 / 3

Merge Join (cost=83,429.38..611,228.20 rows=15,360 width=45) (actual time=103,036.381..103,057.908 rows=832 loops=3)

  • Merge Cond: (gf.xlinkid = xfx1.replyid)
  • -> Parallel Index Scan Backward using gfx_xlinkid_idx on dgroup_for_xlink gf (cost=0.44..506895.93 rows=8,268,112 width=8) (actual time=0.029
7. 5,201.708 103,037.499 ↑ 11.1 1,667 3 / 3

Sort (cost=83,428.94..83,475.01 rows=18,428 width=37) (actual time=103,036.125..103,037.499 rows=1,667 loops=3)

  • Sort Key: xfx1.replyid DESC
  • Sort Method: quicksort Memory: 407,171kB
  • Worker 0: Sort Method: quicksort Memory: 407,171kB
  • Worker 1: Sort Method: quicksort Memory: 407,171kB
8. 18,682.883 97,835.791 ↓ 246.1 4,534,786 3 / 3

Nested Loop (cost=4.16..82,123.35 rows=18,428 width=37) (actual time=0.420..97,835.791 rows=4,534,786 loops=3)

9. 26,271.486 38,176.448 ↓ 704.9 4,097,646 3 / 3

Nested Loop (cost=1.29..18,592.10 rows=5,813 width=33) (actual time=0.349..38,176.448 rows=4,097,646 loops=3)

  • -> Index Scan using locatorid_idx on locator_for_xlink lfx (cost=0.43..2.77 rows=4 width=29) (actual time=0.004..0.005 ro
10. 10,615.638 11,904.962 ↓ 522.7 2,836,438 3 / 3

Nested Loop (cost=0.86..3,326.11 rows=5,426 width=12) (actual time=0.304..11,904.962 rows=2,836,438 loops=3)

  • -> Index Scan using locator_uriid_idx on locator l (cost=0.43..3.09 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
11. 1,289.324 1,289.324 ↓ 517.7 500,648 3 / 3

Nested Loop (cost=0.42..256.03 rows=967 width=4) (actual time=0.264..1,289.324 rows=500,648 loops=3)

  • -> Seq Scan on dgroupuri_for_dgroup gufg (cost=0.00..38.14 rows=2 width=4) (actual time=0.214..0.229 rows=2 l
  • Filter: (groupid = 1,050)
  • Rows Removed by Filter: 2,089
  • -> Index Scan using ufg_groupuriid_idx on uri_for_dgroupuri ufgu (cost=0.42..99.28 rows=967 width=8) (actual
  • Index Cond: (groupuriid = gufg.groupuriid)
  • Index Cond: (uriid = ufgu.uriid)
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.010..0.010 rows=1 loops=12,292,938)

  • Recheck Cond: ((lfx.xlinkid = replytoid) OR (lfx.xlinkid = replyid))
  • Heap Blocks: exact=4,191,882
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)

  • -> 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)
  • -> 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)
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
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)
Planning time : 8.153 ms
Execution time : 103,810.395 ms