explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3IORk

Settings
# exclusive inclusive rows x rows loops node
1. 55.842 17,520.125 ↑ 1.8 2,299 1

HashAggregate (cost=375,866.54..376,113.32 rows=4,113 width=1,357) (actual time=17,477.198..17,520.125 rows=2,299 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 OR (pr.nonstandardterm IS TRUE))) > 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, CASE WHEN (uc.party = 'COUNTERPARTY'::text) THEN false ELSE co.haslinkedcontracts END, 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(*) FILTER (WHERE (docs_status.status <> ALL ('{SIGNED,NSIGNED,CANCELLED,NCANCELLED}'::text[]))) > 0), (count(*) FILTER (WHERE ((da.* IS NULL) AND (dr_query.role = 'Approver'::text) AND (docs_status.status = ANY ('{APPROVING,APPROVED,NAPPROVING,NAPPROVED}'::text[])))) > 0), (count(*) FILTER (WHERE ((dr_query.role = 'Reviewer'::text) AND (docs_status.status = ANY ('{REVIEWING,NEGOTIATING}'::text[])))) > 0), reviewers_query.has_posts_by_reviwer, reviewers_query.has_not_registred_reviewers, (count(*) FILTER (WHERE (dr_query.role = 'Lead'::text)) > 0), ad.post_execution_saved_by_user, COALESCE(bool_or(dr_query.my_turn), false)
2. 1,555.314 17,464.283 ↑ 1.8 2,299 1

GroupAggregate (cost=374,447.55..375,198.17 rows=4,113 width=1,357) (actual time=15,560.638..17,464.283 rows=2,299 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, ad.post_execution_saved_by_user
  • Filter: ((uc.party = 'PARTY'::text) OR (count(CASE WHEN (docs.shared AND (NOT docs.cancelled) AND (NOT docs.removed)) THEN 1 ELSE NULL::integer END) > 0))
3. 3,760.926 15,908.969 ↓ 8.1 33,202 1

Sort (cost=374,447.55..374,457.83 rows=4,113 width=1,357) (actual time=15,560.55..15,908.969 rows=33,202 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, ad.post_execution_saved_by_user
  • Sort Method: external merge Disk: 14,480kB
4. 248.759 12,148.043 ↓ 8.1 33,202 1

Hash Join (cost=59,063.45..374,200.65 rows=4,113 width=1,357) (actual time=3,056.7..12,148.043 rows=33,202 loops=1)

5. 177.159 9,659.363 ↓ 8.1 33,202 1

Hash Join (cost=7,555.08..322,491.39 rows=4,113 width=1,355) (actual time=816.771..9,659.363 rows=33,202 loops=1)

6. 329.171 9,482.194 ↓ 8.1 33,202 1

Nested Loop (cost=7,546.89..322,467.76 rows=4,113 width=1,370) (actual time=816.753..9,482.194 rows=33,202 loops=1)

7. 282.411 7,924.549 ↓ 8.1 33,202 1

Nested Loop (cost=7,546.47..319,481.41 rows=4,113 width=1,362) (actual time=816.727..7,924.549 rows=33,202 loops=1)

8. 39.492 6,903.514 ↓ 3.7 11,541 1

Hash Join (cost=7,546.05..316,235.56 rows=3,150 width=1,343) (actual time=816.709..6,903.514 rows=11,541 loops=1)

9. 157.179 6,863.987 ↓ 3.7 11,541 1

Nested Loop (cost=7,533.35..316,179.55 rows=3,150 width=757) (actual time=816.666..6,863.987 rows=11,541 loops=1)

10. 112.242 6,395.201 ↓ 3.7 11,541 1

Nested Loop (cost=7,533.35..315,948.46 rows=3,150 width=733) (actual time=816.652..6,395.201 rows=11,541 loops=1)

11. 72.223 5,902.106 ↓ 3.7 11,541 1

Hash Join (cost=7,532.93..313,107.91 rows=3,150 width=693) (actual time=816.626..5,902.106 rows=11,541 loops=1)

12. 186.271 5,813.565 ↓ 3.3 10,474 1

Nested Loop (cost=4,870.47..309,224.63 rows=3,150 width=485) (actual time=800.296..5,813.565 rows=10,474 loops=1)

13. 125.377 5,260.704 ↓ 3.3 10,474 1

Nested Loop (cost=4,870.18..307,917.64 rows=3,150 width=463) (actual time=800.282..5,260.704 rows=10,474 loops=1)

14. 327.441 4,517.361 ↓ 4.1 10,474 1

Nested Loop (cost=4,869.75..305,209.27 rows=2,567 width=463) (actual time=800.255..4,517.361 rows=10,474 loops=1)

15. 129.079 3,865.226 ↓ 4.4 10,474 1

Nested Loop (cost=4,869.33..303,245.9 rows=2,381 width=439) (actual time=800.231..3,865.226 rows=10,474 loops=1)

16. 144.173 3,516.193 ↓ 4.4 10,474 1

Hash Join (cost=4,869.05..300,223.7 rows=2,381 width=406) (actual time=800.215..3,516.193 rows=10,474 loops=1)

17. 77.854 3,175.855 ↓ 4.4 10,474 1

Nested Loop (cost=2,715.16..297,977.28 rows=2,381 width=398) (actual time=604.034..3,175.855 rows=10,474 loops=1)

18. 67.835 861.074 ↑ 1.0 2,299 1

Nested Loop (cost=2,596.38..15,092.37 rows=2,381 width=363) (actual time=603.991..861.074 rows=2,299 loops=1)

19. 79.467 705.877 ↑ 1.0 2,299 1

Hash Join (cost=2,595.95..7,573.11 rows=2,381 width=347) (actual time=603.967..705.877 rows=2,299 loops=1)

20. 22.567 22.851 ↑ 1.0 2,299 1

Bitmap Heap Scan on contractsview uc (cost=90.91..5,029.31 rows=2,385 width=53) (actual time=0.319..22.851 rows=2,299 loops=1)

  • Heap Blocks: exact=229
21. 0.284 0.284 ↑ 1.0 2,348 1

Bitmap Index Scan on contractsview_userid_idx (cost=0..90.31 rows=2,385 width=0) (actual time=0.284..0.284 rows=2,348 loops=1)

  • Index Cond: (uc.userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
22. 314.918 603.559 ↓ 1.0 39,139 1

Hash (cost=2,016.13..2,016.13 rows=39,113 width=294) (actual time=603.559..603.559 rows=39,139 loops=1)

23. 288.641 288.641 ↓ 1.0 39,139 1

Seq Scan on contract co (cost=0..2,016.13 rows=39,113 width=294) (actual time=0.005..288.641 rows=39,139 loops=1)

24. 87.362 87.362 ↑ 1.0 1 2,299

Index Scan using idx_contractoles_roles on contractroles ccn (cost=0.43..3.15 rows=1 width=32) (actual time=0.038..0.038 rows=1 loops=2,299)

  • Index Cond: ((ccn.contractid = co.id) AND (ccn.party = 'COUNTERPARTY'::text) AND (ccn.role = 'Negotiator'::text))
25. 94.259 2,236.927 ↓ 4.0 4 2,299

Subquery Scan on di (cost=118.78..118.8 rows=1 width=51) (actual time=0.901..0.973 rows=4 loops=2,299)

  • Filter: (di.contractid = uc.contractid)
26. 317.262 2,142.668 ↓ 4.0 4 2,299

HashAggregate (cost=118.78..118.79 rows=1 width=57) (actual time=0.875..0.932 rows=4 loops=2,299)

  • Group Key: dii.id, dii.contractid, dii.queued, dii.cpqueued, dii.status, dii.party, dii.nonstandardterm, uci.party, dii.external
27. 129.192 1,825.406 ↓ 4.0 4 2,299

Nested Loop (cost=5.03..118.75 rows=1 width=57) (actual time=0.241..0.794 rows=4 loops=2,299)

28. 746.209 1,439.174 ↓ 4.0 4 2,299

Nested Loop (cost=4.61..111.71 rows=1 width=73) (actual time=0.235..0.626 rows=4 loops=2,299)

29. 137.940 137.940 ↓ 2.0 2 2,299

Index Scan using documentrolesview_contractid_userid_idx on documentrolesview uci (cost=0.43..8.45 rows=1 width=56) (actual time=0.051..0.06 rows=2 loops=2,299)

  • Index Cond: ((uci.contractid = uc.contractid) AND (uci.userid = uc.userid))
30. 454.450 555.025 ↑ 2.1 8 3,725

Bitmap Heap Scan on discussion dii (cost=4.18..94.09 rows=17 width=69) (actual time=0.072..0.149 rows=8 loops=3,725)

  • Filter: ((NOT dii.cancelled) AND ((NOT dii.external) OR dii.external OR dii.containsinternal OR dii.cpcontainsinternal) AND (dii.status <> 'HIDDEN'::text))
  • Heap Blocks: exact=13,103
31. 100.575 100.575 ↑ 2.7 9 3,725

Bitmap Index Scan on di_contractid (cost=0..4.18 rows=24 width=0) (actual time=0.027..0.027 rows=9 loops=3,725)

  • Index Cond: (dii.contractid = uc.contractid)
32. 257.040 257.040 ↓ 0.0 0 9,180

Index Only Scan using userdiscussions_discussionid_userid_closed on viewed_user_discussions uds (cost=0.42..7.03 rows=1 width=32) (actual time=0.027..0.028 rows=0 loops=9,180)

  • Index Cond: ((uds.discussionid = dii.id) AND (uds.userid = uc.userid) AND (uds.closed = false))
  • Filter: (NOT uds.closed)
  • Heap Fetches: 2,228
33. 0.491 196.165 ↓ 2.7 772 1

Hash (cost=2,150.26..2,150.26 rows=290 width=24) (actual time=196.165..196.165 rows=772 loops=1)

34. 33.376 195.674 ↓ 2.7 772 1

HashAggregate (cost=2,144.46..2,147.36 rows=290 width=16) (actual time=195.244..195.674 rows=772 loops=1)

  • Group Key: udsc.contractid
35. 162.298 162.298 ↑ 1.0 7,706 1

Seq Scan on viewed_user_discussions udsc (cost=0..2,105.35 rows=7,823 width=16) (actual time=0.008..162.298 rows=7,706 loops=1)

  • Filter: (udsc.userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
36. 219.954 219.954 ↓ 0.0 0 10,474

Index Scan using pk_additionaldata on additionaldata ad (cost=0.29..1.26 rows=1 width=49) (actual time=0.021..0.021 rows=0 loops=10,474)

  • Index Cond: (ad.contractid = uc.contractid)
37. 324.694 324.694 ↑ 1.0 2 10,474

Index Scan using es_entityid_party on entitystatus es (cost=0.42..0.78 rows=2 width=32) (actual time=0.026..0.031 rows=2 loops=10,474)

  • Index Cond: (es.entityid = co.id)
38. 617.966 617.966 ↑ 2.0 1 10,474

Index Scan using idx_contractoles_roles on contractroles cn (cost=0.43..1.03 rows=2 width=32) (actual time=0.056..0.059 rows=1 loops=10,474)

  • Index Cond: ((cn.contractid = es.entityid) AND (cn.party = 'PARTY'::text) AND (cn.role = 'Negotiator'::text))
39. 366.590 366.590 ↑ 1.0 1 10,474

Index Scan using tenant_pkey on tenant ti (cost=0.29..0.4 rows=1 width=38) (actual time=0.03..0.035 rows=1 loops=10,474)

  • Index Cond: (co.counterpartycompanyid = ti.id)
40. 0.133 16.318 ↑ 1.2 126 1

Hash (cost=2,660.17..2,660.17 rows=153 width=256) (actual time=16.318..16.318 rows=126 loops=1)

41. 0.141 16.185 ↑ 1.2 126 1

Hash Join (cost=2,080.19..2,660.17 rows=153 width=256) (actual time=15.799..16.185 rows=126 loops=1)

42. 0.290 14.652 ↑ 1.2 126 1

Bitmap Heap Scan on documentroles dr_query (cost=1,902.61..2,477.6 rows=153 width=193) (actual time=14.394..14.652 rows=126 loops=1)

  • Filter: (NOT dr_query.disabled)
  • Heap Blocks: exact=95
43. 14.362 14.362 ↑ 1.0 148 1

Bitmap Index Scan on documentroles_pk (cost=0..1,902.57 rows=153 width=0) (actual time=14.362..14.362 rows=148 loops=1)

  • Index Cond: ((dr_query.userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid) AND (dr_query.role = ANY ('{Lead,Reviewer,Approver}'::text[])))
44. 0.053 1.392 ↑ 1.2 57 1

Hash (cost=176.56..176.56 rows=68 width=111) (actual time=1.392..1.392 rows=57 loops=1)

45. 1.339 1.339 ↑ 1.2 57 1

Seq Scan on documentapprove da (cost=0..176.56 rows=68 width=111) (actual time=0.013..1.339 rows=57 loops=1)

  • Filter: (da.userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
46. 380.853 380.853 ↑ 1.0 1 11,541

Index Scan using uca_contractid_userid on entityaccessed ea (cost=0.42..0.89 rows=1 width=88) (actual time=0.031..0.033 rows=1 loops=11,541)

  • Index Cond: ((ea.entityid = co.id) AND (ea.userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid))
47. 311.607 311.607 ↓ 0.0 0 11,541

Index Scan using autonumber_sequences_assignment_contract_id_idx on autonumber_sequences_assignment asa (cost=0..0.06 rows=1 width=56) (actual time=0.027..0.027 rows=0 loops=11,541)

  • Index Cond: (asa.contract_id = co.id)
  • Filter: (asa.tenant_id = co.tenantid)
48. 0.017 0.035 ↑ 6.3 19 1

Hash (cost=11.2..11.2 rows=120 width=602) (actual time=0.035..0.035 rows=19 loops=1)

49. 0.018 0.018 ↑ 6.3 19 1

Seq Scan on autonumber_sequences aseq (cost=0..11.2 rows=120 width=602) (actual time=0.006..0.018 rows=19 loops=1)

50. 738.624 738.624 ↓ 1.5 3 11,541

Index Scan using documents_contract_id_idx on documents docs (cost=0.41..1.01 rows=2 width=35) (actual time=0.041..0.064 rows=3 loops=11,541)

  • Index Cond: (docs.contractid = co.id)
  • Filter: ((NOT docs.cancelled) AND (NOT docs.removed))
51. 1,228.474 1,228.474 ↑ 1.0 1 33,202

Index Scan using es_entityid_party on entitystatus docs_status (cost=0.42..0.71 rows=1 width=32) (actual time=0.03..0.037 rows=1 loops=33,202)

  • Index Cond: (docs_status.entityid = docs.id)
  • Filter: (docs_status.status <> ALL ('{SIGNED,NSIGNED,CANCELLED,NCANCELLED}'::text[]))
52. 0.001 0.010 ↓ 0.0 0 1

Hash (cost=8.18..8.18 rows=1 width=17) (actual time=0.01..0.01 rows=0 loops=1)

53. 0.002 0.009 ↓ 0.0 0 1

Subquery Scan on pr (cost=8.16..8.18 rows=1 width=17) (actual time=0.009..0.009 rows=0 loops=1)

54. 0.002 0.007 ↓ 0.0 0 1

HashAggregate (cost=8.16..8.17 rows=1 width=16) (actual time=0.007..0.007 rows=0 loops=1)

  • Group Key: pr_1.documentid, (count(pr_1.documentid) > 0)
55. 0.001 0.005 ↓ 0.0 0 1

GroupAggregate (cost=0.12..8.16 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=1)

  • Group Key: pr_1.documentid
56. 0.004 0.004 ↓ 0.0 0 1

Index Scan using non_st_index on paragraph pr_1 (cost=0.12..8.14 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1)

57. 0.317 2,239.921 ↓ 1.3 490 1

Hash (cost=51,503.78..51,503.78 rows=367 width=18) (actual time=2,239.921..2,239.921 rows=490 loops=1)

58. 0.291 2,239.604 ↓ 1.3 490 1

Subquery Scan on reviewers_query (cost=51,494.6..51,503.78 rows=367 width=18) (actual time=2,239.055..2,239.604 rows=490 loops=1)

59. 12.216 2,239.313 ↓ 1.3 490 1

HashAggregate (cost=51,494.6..51,500.11 rows=367 width=49) (actual time=2,239.055..2,239.313 rows=490 loops=1)

  • Group Key: dr.contractid, (count(p.id) OVER (?) > 0), (count(a.userid) FILTER (WHERE (NOT a.registered)) OVER (?) > 0)
60. 82.643 2,227.097 ↓ 48.6 17,821 1

WindowAgg (cost=51,482.67..51,491.85 rows=367 width=49) (actual time=2,112.98..2,227.097 rows=17,821 loops=1)

61. 121.925 2,144.454 ↓ 48.6 17,821 1

Sort (cost=51,482.67..51,483.59 rows=367 width=49) (actual time=2,096.461..2,144.454 rows=17,821 loops=1)

  • Sort Key: dr.contractid
  • Sort Method: quicksort Memory: 2,300kB
62. 182.625 2,022.529 ↓ 48.6 17,821 1

Hash Join (cost=45,523.55..51,467.04 rows=367 width=49) (actual time=1,583.456..2,022.529 rows=17,821 loops=1)

63. 257.188 257.188 ↑ 1.0 146,220 1

Seq Scan on post p (cost=0..4,836.7 rows=147,570 width=48) (actual time=0.008..257.188 rows=146,220 loops=1)

64. 93.152 1,582.716 ↓ 47.5 17,440 1

Hash (cost=45,518.05..45,518.05 rows=367 width=49) (actual time=1,582.716..1,582.716 rows=17,440 loops=1)

65. 26.627 1,489.564 ↓ 47.5 17,440 1

Nested Loop (cost=5,911.15..45,518.05 rows=367 width=49) (actual time=135.754..1,489.564 rows=17,440 loops=1)

66. 4.822 1,365.515 ↓ 8.8 2,498 1

Nested Loop (cost=5,911.15..42,705.6 rows=284 width=49) (actual time=135.733..1,365.515 rows=2,498 loops=1)

67. 17.314 1,290.749 ↓ 8.8 2,498 1

Hash Join (cost=5,910.86..42,583.94 rows=284 width=48) (actual time=135.715..1,290.749 rows=2,498 loops=1)

68. 1,137.742 1,137.742 ↓ 1.1 11,836 1

Seq Scan on documentroles dr (cost=0..36,629.07 rows=10,978 width=48) (actual time=0.008..1,137.742 rows=11,836 loops=1)

  • Filter: (dr.role = 'Reviewer'::text)
69. 41.304 135.693 ↑ 1.0 1,522 1

Hash (cost=5,891.77..5,891.77 rows=1,527 width=16) (actual time=135.693..135.693 rows=1,522 loops=1)

70. 94.389 94.389 ↑ 1.0 1,522 1

Seq Scan on documents d (cost=0..5,891.77 rows=1,527 width=16) (actual time=0.015..94.389 rows=1,522 loops=1)

  • Filter: (d.status = 'REVIEWING'::text)
71. 69.944 69.944 ↑ 1.0 1 2,498

Index Scan using account_pkey on account a (cost=0.29..0.42 rows=1 width=17) (actual time=0.027..0.028 rows=1 loops=2,498)

  • Index Cond: (a.userid = dr.userid)
72. 97.422 97.422 ↑ 3.2 6 2,498

Index Scan using di_documentid on discussion dis (cost=0..9.71 rows=19 width=32) (actual time=0.012..0.039 rows=6 loops=2,498)

  • Index Cond: (d.id = dis.documentid)
Planning time : 166.575 ms
Execution time : 17,561.877 ms