explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WnPh

Settings
# exclusive inclusive rows x rows loops node
1. 0.255 95,458.796 ↑ 1.0 200 1

Limit (cost=79,435.40..79,436.40 rows=200 width=4) (actual time=95,458.133..95,458.796 rows=200 loops=1)

2. 0.260 95,458.541 ↑ 11.6 200 1

Unique (cost=79,435.40..79,446.95 rows=2,310 width=4) (actual time=95,458.130..95,458.541 rows=200 loops=1)

3. 2,830.774 95,458.281 ↑ 11.6 200 1

Sort (cost=79,435.40..79,441.18 rows=2,310 width=4) (actual time=95,458.125..95,458.281 rows=200 loops=1)

  • Sort Key: comment.xlinkid DESC
  • Sort Method: quicksort Memory: 249,834kB
4. 7,557.645 92,627.507 ↓ 1,399.4 3,232,620 1

Nested Loop (cost=3.02..79,306.35 rows=2,310 width=4) (actual time=0.431..92,627.507 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 (
  • Rows Removed by Join Filter: 304,000
5. 9,944.496 77,996.634 ↓ 401.8 1,768,307 1

Nested Loop (cost=2.59..66,308.35 rows=4,401 width=53) (actual time=0.418..77,996.634 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)
6. 24,050.214 59,212.566 ↓ 350.4 4,419,786 1

Nested Loop (cost=2.15..35,205.98 rows=12,615 width=29) (actual time=0.243..59,212.566 rows=4,419,786 loops=1)

  • -> Index Scan using xfx_replytoid_idx on xlink_for_xlink xfx1 (cost=0.43..2.46 rows=3 width=8) (actual time=0.002..0.003 rows=1 loops=40,787
7. 22,106.458 35,162.352 ↓ 701.7 4,078,786 1

Nested Loop (cost=1.72..20,730.22 rows=5,813 width=25) (actual time=0.234..35,162.352 rows=4,078,786 loops=1)

  • -> Index Scan using locatorid_idx on locator_for_xlink lfx (cost=0.43..2.77 rows=4 width=29) (actual time=0.003..0.005 rows=1 loops=2
  • Index Cond: (replytoid = lfx.xlinkid)
8. 9,338.882 13,055.894 ↓ 522.7 2,836,437 1

Nested Loop (cost=1.28..5,464.24 rows=5,426 width=4) (actual time=0.223..13,055.894 rows=2,836,437 loops=1)

  • -> 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=6 loops=5,006
  • 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
9. 1,144.743 3,717.012 ↓ 517.7 500,647 1

Nested Loop (cost=0.85..2,394.15 rows=967 width=8) (actual time=0.201..3,717.012 rows=500,647 loops=1)

  • Index Cond: (uriid = ufgu.uriid)
10. 1,070.325 1,070.325 ↓ 517.7 500,648 1

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

  • -> Seq Scan on dgroupuri_for_dgroup gufg (cost=0.00..38.14 rows=2 width=4) (actual time=0.164..0.179 rows=2 loops=1
  • 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 time=0
  • Index Cond: (groupuriid = gufg.groupuriid)
11. 1,501.944 1,501.944 ↑ 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.003..0.003 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
12. 8,839.572 8,839.572 ↓ 0.0 0 4,419,786

Index Scan using xlink_pkey on xlink comment (cost=0.43..2.45 rows=1 width=28) (actual time=0.002..0.002 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 ((st
  • Rows Removed by Filter: 1
13. 7,073.228 7,073.228 ↑ 2.5 2 1,768,307

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

  • Index Cond: (xlinkid = xfx1.replyid)
Planning time : 8.918 ms
Execution time : 95,468.396 ms