explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7YoD

Settings
# exclusive inclusive rows x rows loops node
1. 24.232 1,182.428 ↑ 1.9 3,053 1

HashAggregate (cost=147,856.18..148,182.84 rows=5,681 width=760) (actual time=1,179.597..1,182.428 rows=3,053 loops=1)

  • Group Key: co.id, co.userid, co.creationdate, co.duedate, co.value, co.valueccy, uc.statuschanged, co.name, co.cancelled, co.counterpartycompanyid, co.companyid, co.tenantid, uc.party, 'cd9a210b-1779-480e-9990-65e846e4d4e9'::text, co.hybrid, ad.effectivedate, co.notes, uc.party, 'None'::text, cn.userid, ccn.userid, (count(1) FILTER (WHERE di.nonstandardterm) > 0), count(*) FILTER (WHERE ((di.status <> 'CLOSED'::text) AND ((di.queued AND (di.party = 'PARTY'::text)) OR (di.cpqueued AND (di.party = 'COUNTERPARTY'::text))))), count(*) FILTER (WHERE (di.status = 'CLOSED'::text)), count(DISTINCT di.id) FILTER (WHERE (di.status <> 'CLOSED'::text)), (count(1) FILTER (WHERE di.external) > 0), ((count(*) FILTER (WHERE (di.status <> 'CLOSED'::text)) - (count(*))))::integer, co.readysign, ti.name, CASE WHEN ((es.status <> 'TERMINATED'::text) AND (((ad.expirationdate)::date - ('now'::cstring)::date) < 0)) THEN 'EXPIRED'::text WHEN (es.status = ANY ('{NEXECUTED,EXECUTED,CANCELLED,NCANCELLED}'::text[])) THEN es.status WHEN co.cancelled THEN 'CANCELLED'::text WHEN co.terminated THEN 'TERMINATED'::text ELSE uc.status END, (count(ea.*) = 0), co.createdasexecuted, ad.renewaldate, ad.expirationdate, co.counterpartypaper, co.terminated, co.categoryid, co.entityid, co.divisionid, co.countryid, co.regionid, co.side, ad.signdate, co.haslinkedcontracts, co.last_activity, aseq.id, aseq.seq_name, aseq.is_cycle, aseq.is_zero_fill, aseq.increment_by, aseq.min_val, aseq.max_val, aseq.start_on, aseq.scope, aseq.created_at, max(asa.contract_num_val), (('cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid = cn.userid) OR (count(dr_query.*) > 0)), (count(docs.id) > 0), ((count(*) FILTER (WHERE (da.* IS NOT NULL)) = 0) AND (count(*) FILTER (WHERE (dr_query.role = 'Approver'::text)) > 0)), (count(*) FILTER (WHERE (dr_query.role = 'Reviewer'::text)) > 0), reviewers_query.has_posts_by_reviwer, reviewers_query.has_not_registred_reviewers
2. 158.505 1,158.196 ↑ 1.9 3,053 1

GroupAggregate (cost=146,109.27..146,975.62 rows=5,681 width=760) (actual time=955.289..1,158.196 rows=3,053 loops=1)

  • Group Key: co.id, es.status, uc.statuschanged, uc.party, uc.status, (count(*)), ti.name, ad.effectivedate, cn.userid, ccn.userid, ad.expirationdate, ad.renewaldate, ad.signdate, aseq.id, reviewers_query.has_posts_by_reviwer, reviewers_query.has_not_registred_reviewers
3. 477.915 999.691 ↓ 12.4 70,669 1

Sort (cost=146,109.27..146,123.47 rows=5,681 width=760) (actual time=955.219..999.691 rows=70,669 loops=1)

  • Sort Key: co.id, es.status, uc.statuschanged, uc.party, uc.status, (count(*)), ti.name, ad.effectivedate, cn.userid, ccn.userid, ad.expirationdate, ad.renewaldate, ad.signdate, aseq.id, reviewers_query.has_posts_by_reviwer, reviewers_query.has_not_registred_reviewers
  • Sort Method: external merge Disk: 41,272kB
4. 46.549 521.776 ↓ 12.4 70,669 1

Hash Left Join (cost=19,198.86..145,755.01 rows=5,681 width=760) (actual time=104.160..521.776 rows=70,669 loops=1)

  • Hash Cond: (co.id = reviewers_query.contractid)
5. 35.507 415.831 ↓ 12.4 70,669 1

Hash Left Join (cost=7,529.49..134,064.06 rows=5,681 width=758) (actual time=44.758..415.831 rows=70,669 loops=1)

  • Hash Cond: (co.id = docs.contractid)
6. 9.053 375.616 ↓ 2.8 10,923 1

Hash Left Join (cost=6,566.73..132,642.35 rows=3,876 width=742) (actual time=40.035..375.616 rows=10,923 loops=1)

  • Hash Cond: (asa.sequence_id = aseq.id)
7. 16.134 366.557 ↓ 2.8 10,923 1

Nested Loop Left Join (cost=6,565.70..132,588.03 rows=3,876 width=681) (actual time=40.023..366.557 rows=10,923 loops=1)

8. 8.934 328.647 ↓ 2.8 10,888 1

Hash Left Join (cost=6,565.70..132,228.14 rows=3,876 width=657) (actual time=40.013..328.647 rows=10,888 loops=1)

  • Hash Cond: (co.counterpartycompanyid = ti.id)
9. 10.325 319.560 ↓ 2.8 10,888 1

Hash Left Join (cost=6,556.51..132,171.98 rows=3,876 width=638) (actual time=39.852..319.560 rows=10,888 loops=1)

  • Hash Cond: ((uc.userid = ea.userid) AND (co.id = ea.entityid))
10. 8.488 301.642 ↓ 2.8 10,888 1

Hash Left Join (cost=5,598.88..130,951.42 rows=3,876 width=598) (actual time=32.240..301.642 rows=10,888 loops=1)

  • Hash Cond: ((uc.userid = dr_query.userid) AND (uc.contractid = dr_query.contractid))
11. 16.590 291.316 ↓ 2.6 9,946 1

Nested Loop (cost=3,210.57..128,116.84 rows=3,876 width=406) (actual time=30.395..291.316 rows=9,946 loops=1)

  • Join Filter: (co.id = cn.contractid)
12. 6.861 234.774 ↓ 4.1 9,988 1

Hash Left Join (cost=3,210.15..122,016.35 rows=2,431 width=406) (actual time=30.377..234.774 rows=9,988 loops=1)

  • Hash Cond: (uc.contractid = ad.contractid)
13. 7.443 227.684 ↓ 4.1 9,988 1

Hash Left Join (cost=3,198.73..121,993.31 rows=2,431 width=374) (actual time=30.140..227.684 rows=9,988 loops=1)

  • Hash Cond: (uc.contractid = udsc.contractid)
14. 6.521 216.245 ↓ 4.1 9,988 1

Nested Loop Left Join (cost=2,907.01..121,536.71 rows=2,431 width=366) (actual time=26.137..216.245 rows=9,988 loops=1)

15. 5.063 64.917 ↓ 1.3 3,081 1

Hash Join (cost=2,861.37..10,499.93 rows=2,431 width=329) (actual time=26.090..64.917 rows=3,081 loops=1)

  • Hash Cond: ((co.id = es.entityid) AND (uc.party = es.party))
  • Join Filter: (((es.status <> ALL ('{NEGOTIATING,NAPPROVING,NAPPROVED,EXECUTED,NSIGNING,NSIGNED}'::text[])) AND (uc.party = 'PARTY'::text)) OR (es.status = ANY ('{NEGOTIATING,NAPPROVING,NAPPROVED,EXECUTED,NSIGNING,NSIGNED}'::text[])))
16. 3.754 45.251 ↑ 1.1 3,081 1

Hash Right Join (cost=2,082.15..9,620.78 rows=3,269 width=305) (actual time=11.458..45.251 rows=3,081 loops=1)

  • Hash Cond: (ccn.contractid = co.id)
17. 30.059 30.059 ↓ 47.1 2,118 1

Seq Scan on contractroles ccn (cost=0.00..7,538.02 rows=45 width=32) (actual time=0.009..30.059 rows=2,118 loops=1)

  • Filter: ((party = 'COUNTERPARTY'::text) AND (role = 'Negotiator'::text))
  • Rows Removed by Filter: 270,177
18. 2.301 11.438 ↑ 1.1 3,081 1

Hash (cost=2,041.29..2,041.29 rows=3,269 width=289) (actual time=11.438..11.438 rows=3,081 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 852kB
19. 2.524 9.137 ↑ 1.1 3,081 1

Hash Join (cost=280.65..2,041.29 rows=3,269 width=289) (actual time=4.159..9.137 rows=3,081 loops=1)

  • Hash Cond: (uc.contractid = co.id)
20. 2.624 2.955 ↑ 1.1 3,081 1

Bitmap Heap Scan on contractsview uc (cost=93.75..1,809.44 rows=3,269 width=55) (actual time=0.489..2.955 rows=3,081 loops=1)

  • Recheck Cond: (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
  • Heap Blocks: exact=1,405
21. 0.331 0.331 ↑ 1.1 3,092 1

Bitmap Index Scan on contractsview_userid_idx (cost=0.00..92.94 rows=3,269 width=0) (actual time=0.331..0.331 rows=3,092 loops=1)

  • Index Cond: (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
22. 1.819 3.658 ↓ 1.0 3,384 1

Hash (cost=144.73..144.73 rows=3,373 width=234) (actual time=3.658..3.658 rows=3,384 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 728kB
23. 1.839 1.839 ↓ 1.0 3,384 1

Seq Scan on contract co (cost=0.00..144.73 rows=3,373 width=234) (actual time=0.004..1.839 rows=3,384 loops=1)

24. 6.911 14.603 ↓ 1.3 16,950 1

Hash (cost=588.15..588.15 rows=12,738 width=33) (actual time=14.603..14.603 rows=16,950 loops=1)

  • Buckets: 32,768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1,348kB
25. 7.692 7.692 ↓ 1.3 16,950 1

Seq Scan on entitystatus es (cost=0.00..588.15 rows=12,738 width=33) (actual time=0.008..7.692 rows=16,950 loops=1)

  • Filter: ((status <> ALL ('{NEGOTIATING,NAPPROVING,NAPPROVED,EXECUTED,NSIGNING,NSIGNED}'::text[])) OR (status = ANY ('{NEGOTIATING,NAPPROVING,NAPPROVED,EXECUTED,NSIGNING,NSIGNED}'::text[])))
26. 9.243 144.807 ↓ 3.0 3 3,081

Subquery Scan on di (cost=45.64..45.67 rows=1 width=53) (actual time=0.044..0.047 rows=3 loops=3,081)

  • Filter: (di.contractid = uc.contractid)
27. 12.324 135.564 ↓ 3.0 3 3,081

HashAggregate (cost=45.64..45.65 rows=1 width=59) (actual time=0.043..0.044 rows=3 loops=3,081)

  • Group Key: dii.id, dii.contractid, dii.queued, dii.cpqueued, dii.status, dii.party, dii.nonstandardterm, uci.party, dii.external
28. 44.226 123.240 ↓ 3.0 3 3,081

Nested Loop (cost=4.48..45.62 rows=1 width=59) (actual time=0.019..0.040 rows=3 loops=3,081)

  • 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: 8
29. 30.810 30.810 ↓ 2.0 2 3,081

Index Scan using documentrolesview_contractid_documentid_userid_idx on documentrolesview uci (cost=0.42..9.41 rows=1 width=56) (actual time=0.007..0.010 rows=2 loops=3,081)

  • Index Cond: ((contractid = uc.contractid) AND (userid = uc.userid))
30. 37.492 48.204 ↑ 1.2 6 5,356

Bitmap Heap Scan on discussion dii (cost=4.06..32.43 rows=7 width=71) (actual time=0.004..0.009 rows=6 loops=5,356)

  • Recheck Cond: (contractid = uc.contractid)
  • Filter: ((NOT cancelled) AND ((NOT external) OR external OR containsinternal OR cpcontainsinternal) AND (status <> 'HIDDEN'::text))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=12,241
31. 10.712 10.712 ↑ 1.0 8 5,356

Bitmap Index Scan on di_contractid (cost=0.00..4.06 rows=8 width=0) (actual time=0.002..0.002 rows=8 loops=5,356)

  • Index Cond: (contractid = uc.contractid)
32. 0.225 3.996 ↓ 1.5 738 1

Hash (cost=285.39..285.39 rows=506 width=24) (actual time=3.996..3.996 rows=738 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 49kB
33. 1.755 3.771 ↓ 1.5 738 1

HashAggregate (cost=275.27..280.33 rows=506 width=16) (actual time=3.565..3.771 rows=738 loops=1)

  • Group Key: udsc.contractid
34. 2.016 2.016 ↑ 1.0 4,510 1

Seq Scan on userdiscussions udsc (cost=0.00..252.65 rows=4,524 width=16) (actual time=0.004..2.016 rows=4,510 loops=1)

  • Filter: (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
  • Rows Removed by Filter: 4,721
35. 0.119 0.229 ↑ 1.0 330 1

Hash (cost=7.30..7.30 rows=330 width=48) (actual time=0.229..0.229 rows=330 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
36. 0.110 0.110 ↑ 1.0 330 1

Seq Scan on additionaldata ad (cost=0.00..7.30 rows=330 width=48) (actual time=0.006..0.110 rows=330 loops=1)

37. 39.952 39.952 ↑ 2.0 1 9,988

Index Scan using idx_contractoles_roles on contractroles cn (cost=0.42..2.48 rows=2 width=32) (actual time=0.004..0.004 rows=1 loops=9,988)

  • Index Cond: ((contractid = es.entityid) AND (party = 'PARTY'::text) AND (role = 'Negotiator'::text))
38. 0.049 1.838 ↓ 2.0 87 1

Hash (cost=2,387.64..2,387.64 rows=44 width=240) (actual time=1.838..1.838 rows=87 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
39. 0.066 1.789 ↓ 2.0 87 1

Hash Left Join (cost=5.16..2,387.64 rows=44 width=240) (actual time=0.126..1.789 rows=87 loops=1)

  • Hash Cond: ((dr_query.userid = da.userid) AND (dr_query.documentid = da.documentid))
40. 1.675 1.675 ↓ 2.0 87 1

Index Scan using documentroles_pk on documentroles dr_query (cost=0.42..2,382.35 rows=44 width=178) (actual time=0.070..1.675 rows=87 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
41. 0.012 0.048 ↑ 1.0 35 1

Hash (cost=4.21..4.21 rows=35 width=110) (actual time=0.048..0.048 rows=35 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
42. 0.036 0.036 ↑ 1.0 35 1

Seq Scan on documentapprove da (cost=0.00..4.21 rows=35 width=110) (actual time=0.006..0.036 rows=35 loops=1)

  • Filter: (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
  • Rows Removed by Filter: 151
43. 2.424 7.593 ↑ 1.0 6,636 1

Hash (cost=857.78..857.78 rows=6,657 width=88) (actual time=7.593..7.593 rows=6,636 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 823kB
44. 5.169 5.169 ↑ 1.0 6,636 1

Seq Scan on entityaccessed ea (cost=0.00..857.78 rows=6,657 width=88) (actual time=0.008..5.169 rows=6,636 loops=1)

  • Filter: (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
  • Rows Removed by Filter: 29,791
45. 0.080 0.153 ↓ 1.0 237 1

Hash (cost=6.31..6.31 rows=231 width=35) (actual time=0.153..0.153 rows=237 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
46. 0.073 0.073 ↓ 1.0 237 1

Seq Scan on tenant ti (cost=0.00..6.31 rows=231 width=35) (actual time=0.005..0.073 rows=237 loops=1)

47. 21.776 21.776 ↑ 1.0 1 10,888

Index Scan using autonumber_sequences_assignment_contract_id_idx on autonumber_sequences_assignment asa (cost=0.00..0.08 rows=1 width=56) (actual time=0.002..0.002 rows=1 loops=10,888)

  • Index Cond: (contract_id = co.id)
  • Filter: (tenant_id = co.tenantid)
48. 0.003 0.006 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=77) (actual time=0.006..0.006 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
49. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on autonumber_sequences aseq (cost=0.00..1.01 rows=1 width=77) (actual time=0.003..0.003 rows=1 loops=1)

50. 1.684 4.708 ↓ 1.0 4,960 1

Hash (cost=900.96..900.96 rows=4,944 width=32) (actual time=4.708..4.708 rows=4,960 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 374kB
51. 3.024 3.024 ↓ 1.0 4,960 1

Seq Scan on documents docs (cost=0.00..900.96 rows=4,944 width=32) (actual time=0.006..3.024 rows=4,960 loops=1)

  • Filter: (status <> ALL ('{SIGNED,NSIGNED}'::text[]))
  • Rows Removed by Filter: 733
52. 0.051 59.396 ↓ 9.1 146 1

Hash (cost=11,669.18..11,669.18 rows=16 width=18) (actual time=59.396..59.396 rows=146 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
53. 0.062 59.345 ↓ 9.1 146 1

Subquery Scan on reviewers_query (cost=11,668.78..11,669.18 rows=16 width=18) (actual time=59.249..59.345 rows=146 loops=1)

54. 0.263 59.283 ↓ 9.1 146 1

HashAggregate (cost=11,668.78..11,669.02 rows=16 width=49) (actual time=59.247..59.283 rows=146 loops=1)

  • Group Key: dr.contractid, (count(p.id) OVER (?) > 0), (count(a.userid) FILTER (WHERE (NOT a.registered)) OVER (?) > 0)
55. 0.456 59.020 ↓ 4.7 517 1

WindowAgg (cost=11,665.17..11,667.94 rows=111 width=49) (actual time=58.469..59.020 rows=517 loops=1)

56. 0.431 58.564 ↓ 4.7 517 1

Sort (cost=11,665.17..11,665.45 rows=111 width=49) (actual time=58.451..58.564 rows=517 loops=1)

  • Sort Key: dr.contractid
  • Sort Method: quicksort Memory: 70kB
57. 0.384 58.133 ↓ 4.7 517 1

Hash Left Join (cost=1,681.44..11,661.40 rows=111 width=49) (actual time=11.252..58.133 rows=517 loops=1)

  • Hash Cond: ((dis.id = p.discussionid) AND (a.userid = p.userid))
58. 0.495 48.207 ↓ 4.4 491 1

Nested Loop Left Join (cost=911.14..10,881.65 rows=111 width=49) (actual time=1.685..48.207 rows=491 loops=1)

59. 0.316 46.959 ↓ 3.5 251 1

Nested Loop (cost=911.14..10,736.32 rows=72 width=49) (actual time=1.675..46.959 rows=251 loops=1)

60. 0.515 46.141 ↓ 3.5 251 1

Hash Join (cost=910.86..10,701.75 rows=72 width=48) (actual time=1.663..46.141 rows=251 loops=1)

  • Hash Cond: (dr.documentid = d.id)
61. 43.979 43.979 ↓ 2.4 1,269 1

Seq Scan on documentroles dr (cost=0.00..9,788.23 rows=518 width=48) (actual time=0.006..43.979 rows=1,269 loops=1)

  • Filter: (role = 'Reviewer'::text)
  • Rows Removed by Filter: 348,427
62. 0.296 1.647 ↓ 1.0 800 1

Hash (cost=900.96..900.96 rows=792 width=16) (actual time=1.647..1.647 rows=800 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 46kB
63. 1.351 1.351 ↓ 1.0 800 1

Seq Scan on documents d (cost=0.00..900.96 rows=792 width=16) (actual time=0.003..1.351 rows=800 loops=1)

  • Filter: (status = 'REVIEWING'::text)
  • Rows Removed by Filter: 4,893
64. 0.502 0.502 ↑ 1.0 1 251

Index Scan using ac_userid on account a (cost=0.28..0.47 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=251)

  • Index Cond: (userid = dr.userid)
65. 0.753 0.753 ↑ 6.0 1 251

Index Scan using di_documentid on discussion dis (cost=0.00..1.96 rows=6 width=32) (actual time=0.002..0.003 rows=1 loops=251)

  • Index Cond: (d.id = documentid)
66. 4.899 9.542 ↓ 1.0 13,823 1

Hash (cost=563.72..563.72 rows=13,772 width=48) (actual time=9.542..9.542 rows=13,823 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,208kB
67. 4.643 4.643 ↓ 1.0 13,823 1

Seq Scan on post p (cost=0.00..563.72 rows=13,772 width=48) (actual time=0.004..4.643 rows=13,823 loops=1)

Planning time : 10.871 ms
Execution time : 1,190.369 ms