explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NaiTB

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 39,742.871 ↓ 45.6 17,375 1

Nested Loop Left Join (cost=2,782.37..163,924.63 rows=381 width=1,281) (actual time=44.761..39,742.871 rows=17,375 loops=1)

2. 26.252 39,679.058 ↓ 45.6 17,375 1

Nested Loop Left Join (cost=2,782.10..163,807.13 rows=381 width=1,249) (actual time=44.754..39,679.058 rows=17,375 loops=1)

  • Join Filter: (ea.userid = uc.userid)
3. 29.469 39,583.306 ↓ 45.6 17,375 1

Merge Left Join (cost=2,781.68..163,268.42 rows=381 width=1,193) (actual time=44.730..39,583.306 rows=17,375 loops=1)

  • Merge Cond: (co.id = asa.contract_id)
  • Join Filter: (asa.tenant_id = co.tenantid)
4. 27.185 39,547.848 ↓ 45.5 17,340 1

Nested Loop (cost=2,514.04..162,980.20 rows=381 width=583) (actual time=41.497..39,547.848 rows=17,340 loops=1)

  • Join Filter: (co.id = cn.contractid)
5. 16.054 39,433.963 ↓ 118.8 17,340 1

Nested Loop Left Join (cost=2,513.62..162,613.81 rows=146 width=583) (actual time=41.483..39,433.963 rows=17,340 loops=1)

6. 23.409 39,313.869 ↓ 118.8 17,340 1

Nested Loop (cost=2,513.20..161,629.59 rows=146 width=567) (actual time=41.466..39,313.869 rows=17,340 loops=1)

  • Join Filter: (((uc.party = 'PARTY'::text) OR (NOT co.hybrid)) AND (uc.contractid = co.id))
7. 14.087 39,221.100 ↓ 145.7 17,340 1

Nested Loop Left Join (cost=2,512.92..161,588.43 rows=119 width=228) (actual time=41.438..39,221.100 rows=17,340 loops=1)

8. 7.574 41.849 ↓ 33.6 3,994 1

Merge Left Join (cost=1,164.88..1,166.02 rows=119 width=144) (actual time=31.191..41.849 rows=3,994 loops=1)

  • Merge Cond: (uc.contractid = udsc.contractid)
9. 5.137 25.501 ↓ 33.6 3,994 1

Sort (cost=880.06..880.35 rows=119 width=136) (actual time=23.064..25.501 rows=3,994 loops=1)

  • Sort Key: uc.contractid
  • Sort Method: quicksort Memory: 663kB
10. 1.290 20.364 ↓ 33.6 3,994 1

Hash Left Join (cost=174.76..875.95 rows=119 width=136) (actual time=2.832..20.364 rows=3,994 loops=1)

  • Hash Cond: (uc.contractid = ad.contractid)
11. 11.522 18.713 ↓ 33.6 3,994 1

Hash Join (cost=152.84..853.72 rows=119 width=103) (actual time=2.463..18.713 rows=3,994 loops=1)

  • Hash Cond: ((es.entityid = uc.contractid) AND (es.party = uc.party))
  • Join Filter: ((uc.party = 'PARTY'::text) OR (es.status = ANY ('{NEGOTIATING,NAPPROVING,NAPPROVED,EXECUTED,NSIGNING,NSIGNED}'::text[])))
12. 4.765 4.765 ↓ 1.5 35,528 1

Seq Scan on entitystatus es (cost=0.00..576.30 rows=23,730 width=80) (actual time=0.007..4.765 rows=35,528 loops=1)

13. 1.332 2.426 ↑ 1.0 3,994 1

Hash (cost=92.93..92.93 rows=3,994 width=55) (actual time=2.425..2.426 rows=3,994 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 372kB
14. 1.094 1.094 ↑ 1.0 3,994 1

Seq Scan on contractsview uc (cost=0.00..92.93 rows=3,994 width=55) (actual time=0.010..1.094 rows=3,994 loops=1)

  • Filter: (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
15. 0.163 0.361 ↓ 1.1 587 1

Hash (cost=15.30..15.30 rows=530 width=49) (actual time=0.361..0.361 rows=587 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 46kB
16. 0.198 0.198 ↓ 1.1 587 1

Seq Scan on additionaldata ad (cost=0.00..15.30 rows=530 width=49) (actual time=0.011..0.198 rows=587 loops=1)

17. 1.111 8.774 ↓ 11.1 1,154 1

Sort (cost=284.82..285.08 rows=104 width=24) (actual time=8.121..8.774 rows=1,154 loops=1)

  • Sort Key: udsc.contractid
  • Sort Method: quicksort Memory: 139kB
18. 3.409 7.663 ↓ 11.1 1,154 1

HashAggregate (cost=279.25..280.29 rows=104 width=24) (actual time=7.410..7.663 rows=1,154 loops=1)

  • Group Key: udsc.contractid
19. 3.255 4.254 ↓ 78.1 11,483 1

Bitmap Heap Scan on viewed_user_discussions udsc (cost=9.55..278.52 rows=147 width=16) (actual time=1.047..4.254 rows=11,483 loops=1)

  • Recheck Cond: (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
  • Heap Blocks: exact=315
20. 0.999 0.999 ↓ 78.1 11,483 1

Bitmap Index Scan on pk_userdiscussions (cost=0.00..9.51 rows=147 width=0) (actual time=0.999..0.999 rows=11,483 loops=1)

  • Index Cond: (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
21. 7.988 39,165.164 ↓ 4.0 4 3,994

Subquery Scan on di (cost=1,348.04..1,348.08 rows=1 width=100) (actual time=9.801..9.806 rows=4 loops=3,994)

  • Filter: (di.contractid = uc.contractid)
22. 19.970 39,157.176 ↓ 4.0 4 3,994

Unique (cost=1,348.04..1,348.07 rows=1 width=106) (actual time=9.801..9.804 rows=4 loops=3,994)

23. 43.934 39,137.206 ↓ 4.0 4 3,994

Sort (cost=1,348.04..1,348.05 rows=1 width=106) (actual time=9.799..9.799 rows=4 loops=3,994)

  • Sort Key: dii.id, dii.queued, dii.cpqueued, dii.status, dii.party, dii.nonstandardterm, uci.party, dii.external
  • Sort Method: quicksort Memory: 25kB
24. 120.098 39,093.272 ↓ 4.0 4 3,994

Nested Loop (cost=0.28..1,348.03 rows=1 width=106) (actual time=7.211..9.788 rows=4 loops=3,994)

  • Join Filter: (((uci.documentid = dii.documentid) OR (uci.documentid IS NULL)) AND (((dii.status = ANY ('{REVIEWING,INTERNAL,CLOSED,QUEUED_EXTERNAL}'::text[])) AND (dii.party = uci.party)) OR (dii.status = ANY ('{EXTERNAL,CLOSED}'::text[]))) AND (((NOT dii.external) AND (uci.party = dii.party)) OR (dii.external AND (api_role.priority_to_role((uci.role)::integer) <> 'NegotiationInternalDocumentParticipant'::text)) OR ((api_role.priority_to_role((uci.role)::integer) = 'NegotiationInternalDocumentParticipant'::text) AND (((uci.party = 'PARTY'::text) AND dii.containsinternal) OR ((uci.party = 'COUNTERPARTY'::text) AND dii.cpcontainsinternal)))))
  • Rows Removed by Join Filter: 16
25. 38,913.542 38,913.542 ↓ 4.0 4 3,994

Seq Scan on discussion dii (cost=0.00..1,336.88 rows=1 width=118) (actual time=7.195..9.743 rows=4 loops=3,994)

  • Filter: ((NOT cancelled) AND (status <> 'HIDDEN'::text) AND (contractid = uc.contractid) AND ((status = ANY ('{REVIEWING,INTERNAL,CLOSED,QUEUED_EXTERNAL}'::text[])) OR (status = ANY ('{EXTERNAL,CLOSED}'::text[]))))
  • Rows Removed by Filter: 25,589
26. 59.632 59.632 ↓ 2.5 5 14,908

Index Scan using documentrolesview_contractid_documentid_userid_idx on documentrolesview uci (cost=0.28..10.07 rows=2 width=56) (actual time=0.003..0.004 rows=5 loops=14,908)

  • Index Cond: ((contractid = uc.contractid) AND (userid = uc.userid))
27. 69.360 69.360 ↑ 1.0 1 17,340

Index Scan using pk_contract on contract co (cost=0.28..0.33 rows=1 width=339) (actual time=0.004..0.004 rows=1 loops=17,340)

  • Index Cond: (id = es.entityid)
28. 104.040 104.040 ↑ 1.0 1 17,340

Index Scan using contractroles_party_role on contractroles ccn (cost=0.42..6.73 rows=1 width=32) (actual time=0.005..0.006 rows=1 loops=17,340)

  • Index Cond: ((contractid = co.id) AND (party = 'COUNTERPARTY'::text) AND (role = 'Negotiator'::text))
29. 86.700 86.700 ↑ 2.0 1 17,340

Index Scan using contractroles_party_role on contractroles cn (cost=0.42..2.48 rows=2 width=32) (actual time=0.005..0.005 rows=1 loops=17,340)

  • Index Cond: ((contractid = es.entityid) AND (party = 'PARTY'::text) AND (role = 'Negotiator'::text))
30. 4.239 5.989 ↓ 5.0 15,143 1

Sort (cost=267.64..275.23 rows=3,034 width=642) (actual time=3.224..5.989 rows=15,143 loops=1)

  • Sort Key: asa.contract_id
  • Sort Method: quicksort Memory: 800kB
31. 1.361 1.750 ↑ 1.1 2,650 1

Hash Left Join (cost=12.70..92.17 rows=3,034 width=642) (actual time=0.027..1.750 rows=2,650 loops=1)

  • Hash Cond: (asa.sequence_id = aseq.id)
32. 0.380 0.380 ↑ 1.1 2,650 1

Seq Scan on autonumber_sequences_assignment asa (cost=0.00..71.34 rows=3,034 width=56) (actual time=0.008..0.380 rows=2,650 loops=1)

33. 0.004 0.009 ↑ 120.0 1 1

Hash (cost=11.20..11.20 rows=120 width=602) (actual time=0.008..0.009 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
34. 0.005 0.005 ↑ 120.0 1 1

Seq Scan on autonumber_sequences aseq (cost=0.00..11.20 rows=120 width=602) (actual time=0.004..0.005 rows=1 loops=1)

35. 69.500 69.500 ↑ 1.0 1 17,375

Index Scan using uca_contractid_userid on entityaccessed ea (cost=0.41..1.40 rows=1 width=88) (actual time=0.004..0.004 rows=1 loops=17,375)

  • Index Cond: ((entityid = co.id) AND (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid))
36. 34.750 34.750 ↑ 1.0 1 17,375

Index Scan using tenant_pkey on tenant ti (cost=0.27..0.31 rows=1 width=48) (actual time=0.002..0.002 rows=1 loops=17,375)

  • Index Cond: (id = co.counterpartycompanyid)
37. 198.989 208.500 ↓ 1.2 137 17,375

Materialize (cost=0.42..3,865.16 rows=113 width=64) (actual time=0.000..0.012 rows=137 loops=17,375)

38. 9.511 9.511 ↓ 1.2 137 1

Index Scan using documentroles_pk on documentroles dr_query (cost=0.42..3,864.60 rows=113 width=64) (actual time=0.141..9.511 rows=137 loops=1)

  • Index Cond: ((userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid) AND (role = ANY ('{Lead,Reviewer,Approver}'::text[])))
  • Filter: (NOT disabled)
  • Rows Removed by Filter: 3
39. 146.041 146.125 ↓ 13.8 55 29,225

Materialize (cost=0.00..19.27 rows=4 width=137) (actual time=0.000..0.005 rows=55 loops=29,225)

40. 0.084 0.084 ↓ 13.8 55 1

Seq Scan on documentapprove da (cost=0.00..19.25 rows=4 width=137) (actual time=0.018..0.084 rows=55 loops=1)

  • Filter: (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
  • Rows Removed by Filter: 319
41. 292.250 292.250 ↑ 4.5 8 29,225

Index Scan using documents_contract_id_idx on documents docs (cost=0.29..4.94 rows=36 width=35) (actual time=0.003..0.010 rows=8 loops=29,225)

  • Index Cond: (contractid = co.id)
  • Filter: ((NOT cancelled) AND (NOT removed))
  • Rows Removed by Filter: 1
42. 0.003 0.035 ↑ 200.0 1 1

Hash (cost=224,839.53..224,839.53 rows=200 width=17) (actual time=0.035..0.035 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
43. 0.002 0.032 ↑ 200.0 1 1

Subquery Scan on pr (cost=224,836.03..224,839.53 rows=200 width=17) (actual time=0.031..0.032 rows=1 loops=1)

44. 0.000 0.030 ↑ 200.0 1 1

Unique (cost=224,836.03..224,837.53 rows=200 width=17) (actual time=0.030..0.030 rows=1 loops=1)

45. 0.013 0.030 ↑ 200.0 1 1

Sort (cost=224,836.03..224,836.53 rows=200 width=17) (actual time=0.029..0.030 rows=1 loops=1)

  • Sort Key: pr_1.documentid, ((count(pr_1.documentid) > 0))
  • Sort Method: quicksort Memory: 25kB
46. 0.010 0.017 ↑ 200.0 1 1

HashAggregate (cost=224,825.88..224,828.38 rows=200 width=17) (actual time=0.014..0.017 rows=1 loops=1)

  • Group Key: pr_1.documentid
47. 0.003 0.007 ↑ 1,865,496.0 1 1

Bitmap Heap Scan on paragraph pr_1 (cost=475.44..215,498.40 rows=1,865,496 width=16) (actual time=0.007..0.007 rows=1 loops=1)

  • Recheck Cond: (nonstandardterm IS TRUE)
  • Heap Blocks: exact=1
48. 0.004 0.004 ↑ 1,865,496.0 1 1

Bitmap Index Scan on non_st_index (cost=0.00..9.07 rows=1,865,496 width=0) (actual time=0.004..0.004 rows=1 loops=1)

49. 0.099 214.933 ↓ 23.6 331 1

Hash (cost=25,128.45..25,128.45 rows=14 width=18) (actual time=214.933..214.933 rows=331 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
50. 0.064 214.834 ↓ 23.6 331 1

Subquery Scan on reviewers_query (cost=25,128.17..25,128.45 rows=14 width=18) (actual time=214.081..214.834 rows=331 loops=1)

51. 0.484 214.770 ↓ 23.6 331 1

Unique (cost=25,128.17..25,128.31 rows=14 width=18) (actual time=214.079..214.770 rows=331 loops=1)

52. 0.903 214.286 ↓ 172.1 2,410 1

Sort (cost=25,128.17..25,128.21 rows=14 width=18) (actual time=214.078..214.286 rows=2,410 loops=1)

  • Sort Key: dr.contractid, ((count(p.id) OVER (?) > 0)), ((count(a.userid) FILTER (WHERE (NOT a.registered)) OVER (?) > 0))
  • Sort Method: quicksort Memory: 285kB
53. 1.827 213.383 ↓ 172.1 2,410 1

WindowAgg (cost=25,127.55..25,127.90 rows=14 width=18) (actual time=211.359..213.383 rows=2,410 loops=1)

54. 1.426 211.556 ↓ 172.1 2,410 1

Sort (cost=25,127.55..25,127.59 rows=14 width=49) (actual time=211.342..211.556 rows=2,410 loops=1)

  • Sort Key: dr.contractid
  • Sort Method: quicksort Memory: 295kB
55. 1.272 210.130 ↓ 172.1 2,410 1

Hash Left Join (cost=4,396.34..25,127.29 rows=14 width=49) (actual time=42.018..210.130 rows=2,410 loops=1)

  • Hash Cond: ((dis.id = p.discussionid) AND (a.userid = p.userid))
56. 1.729 186.552 ↓ 169.6 2,374 1

Nested Loop (cost=3,221.47..23,889.05 rows=14 width=49) (actual time=19.669..186.552 rows=2,374 loops=1)

57. 1.418 180.075 ↓ 169.6 2,374 1

Hash Join (cost=3,221.19..23,884.10 rows=14 width=48) (actual time=19.649..180.075 rows=2,374 loops=1)

  • Hash Cond: (dr.documentid = d.id)
58. 159.024 159.024 ↑ 1.0 2,633 1

Seq Scan on documentroles dr (cost=0.00..20,652.46 rows=2,749 width=48) (actual time=0.005..159.024 rows=2,633 loops=1)

  • Filter: (role = 'Reviewer'::text)
  • Rows Removed by Filter: 708,364
59. 1.027 19.633 ↓ 27.0 3,860 1

Hash (cost=3,219.40..3,219.40 rows=143 width=32) (actual time=19.633..19.633 rows=3,860 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 263kB
60. 4.234 18.606 ↓ 27.0 3,860 1

Hash Right Join (cost=2,044.85..3,219.40 rows=143 width=32) (actual time=4.669..18.606 rows=3,860 loops=1)

  • Hash Cond: (dis.documentid = d.id)
61. 9.722 9.722 ↓ 1.6 25,593 1

Seq Scan on discussion dis (cost=0.00..1,131.39 rows=16,439 width=32) (actual time=0.010..9.722 rows=25,593 loops=1)

62. 0.394 4.650 ↓ 10.0 1,428 1

Hash (cost=2,043.06..2,043.06 rows=143 width=16) (actual time=4.650..4.650 rows=1,428 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 83kB
63. 4.256 4.256 ↓ 10.0 1,428 1

Seq Scan on documents d (cost=0.00..2,043.06 rows=143 width=16) (actual time=0.005..4.256 rows=1,428 loops=1)

  • Filter: (status = 'REVIEWING'::text)
  • Rows Removed by Filter: 8,698
64. 4.748 4.748 ↑ 1.0 1 2,374

Index Scan using ac_userid on account a (cost=0.28..0.35 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=2,374)

  • Index Cond: (userid = dr.userid)
65. 12.296 22.306 ↓ 2.0 37,001 1

Hash (cost=903.75..903.75 rows=18,075 width=48) (actual time=22.306..22.306 rows=37,001 loops=1)

  • Buckets: 65,536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 3,403kB
66. 10.010 10.010 ↓ 2.0 37,001 1

Seq Scan on post p (cost=0.00..903.75 rows=18,075 width=48) (actual time=0.011..10.010 rows=37,001 loops=1)

67. 664.419 664.419 ↑ 1.0 1 221,473

Index Scan using es_entityid_party on entitystatus docs_status (cost=0.41..1.03 rows=1 width=80) (actual time=0.003..0.003 rows=1 loops=221,473)

  • Index Cond: ((entityid = docs.id) AND (party = uc.party))
  • Filter: (status <> ALL ('{SIGNED,NSIGNED,CANCELLED,NCANCELLED}'::text[]))
  • Rows Removed by Filter: 0
Planning time : 13.647 ms
Execution time : 44,154.676 ms