explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5jyd

Settings
# exclusive inclusive rows x rows loops node
1. 1.481 896.440 ↑ 2.1 1,518 1

Unique (cost=112,425.82..112,921.94 rows=3,150 width=1,286) (actual time=894.530..896.440 rows=1,518 loops=1)

2. 2.639 894.959 ↑ 2.1 1,518 1

Sort (cost=112,425.82..112,433.69 rows=3,150 width=1,286) (actual time=894.530..894.959 rows=1,518 loops=1)

  • Sort 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, co.hybrid, ad.effectivedate, co.notes, 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, (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(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
  • Sort Method: quicksort Memory: 938kB
3. 104.531 892.320 ↑ 2.1 1,518 1

GroupAggregate (cost=111,730.91..112,242.78 rows=3,150 width=1,286) (actual time=757.644..892.320 rows=1,518 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
  • Filter: ((uc.party = 'PARTY'::text) OR (count(CASE WHEN docs.shared THEN 1 ELSE NULL::integer END) > 0))
4. 360.914 787.789 ↓ 16.4 51,684 1

Sort (cost=111,730.91..111,738.78 rows=3,150 width=1,286) (actual time=757.576..787.789 rows=51,684 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: 31688kB
5. 43.668 426.875 ↓ 16.4 51,684 1

Hash Left Join (cost=16,095.09..111,547.87 rows=3,150 width=1,286) (actual time=109.262..426.875 rows=51,684 loops=1)

  • Hash Cond: (co.id = reviewers_query.contractid)
6. 32.453 304.702 ↓ 16.4 51,684 1

Hash Left Join (cost=3,763.01..99,203.61 rows=3,150 width=1,284) (actual time=30.742..304.702 rows=51,684 loops=1)

  • Hash Cond: (co.id = docs.contractid)
7. 6.816 265.479 ↓ 2.7 6,827 1

Hash Left Join (cost=2,872.43..98,019.47 rows=2,526 width=1,267) (actual time=23.948..265.479 rows=6,827 loops=1)

  • Hash Cond: (asa.sequence_id = aseq.id)
8. 11.980 258.654 ↓ 2.7 6,827 1

Nested Loop Left Join (cost=2,871.41..97,983.71 rows=2,526 width=681) (actual time=23.932..258.654 rows=6,827 loops=1)

9. 6.422 226.232 ↓ 2.7 6,814 1

Hash Left Join (cost=2,871.41..97,804.74 rows=2,526 width=657) (actual time=23.915..226.232 rows=6,814 loops=1)

  • Hash Cond: (co.counterpartycompanyid = ti.id)
10. 7.462 219.612 ↓ 2.7 6,814 1

Hash Left Join (cost=2,861.94..97,764.40 rows=2,526 width=638) (actual time=23.700..219.612 rows=6,814 loops=1)

  • Hash Cond: ((uc.userid = ea.userid) AND (co.id = ea.entityid))
11. 5.950 198.813 ↓ 2.7 6,814 1

Hash Left Join (cost=2,003.77..96,728.42 rows=2,526 width=598) (actual time=10.332..198.813 rows=6,814 loops=1)

  • Hash Cond: ((uc.userid = dr_query.userid) AND (uc.contractid = dr_query.contractid))
12. 8.414 190.195 ↓ 2.3 5,890 1

Nested Loop (cost=429.64..94,755.89 rows=2,526 width=406) (actual time=7.645..190.195 rows=5,890 loops=1)

  • Join Filter: (co.id = cn.contractid)
13. 5.590 152.311 ↓ 3.9 5,894 1

Nested Loop Left Join (cost=429.22..92,135.77 rows=1,519 width=406) (actual time=7.620..152.311 rows=5,894 loops=1)

14. 5.843 134.933 ↓ 3.9 5,894 1

Hash Left Join (cost=428.95..91,648.07 rows=1,519 width=374) (actual time=7.603..134.933 rows=5,894 loops=1)

  • Hash Cond: (uc.contractid = udsc.contractid)
15. 6.020 123.166 ↓ 3.9 5,894 1

Nested Loop Left Join (cost=113.51..91,212.92 rows=1,519 width=366) (actual time=1.647..123.166 rows=5,894 loops=1)

16. 1.625 38.158 ↑ 1.0 1,519 1

Nested Loop Left Join (cost=59.19..8,652.68 rows=1,519 width=329) (actual time=1.623..38.158 rows=1,519 loops=1)

17. 3.838 25.900 ↑ 1.0 1,519 1

Nested Loop (cost=58.77..662.96 rows=1,519 width=313) (actual time=1.602..25.900 rows=1,519 loops=1)

  • Join Filter: (uc.contractid = co.id)
18. 9.992 17.505 ↑ 1.0 1,519 1

Hash Join (cost=58.77..578.56 rows=1,519 width=79) (actual time=1.579..17.505 rows=1,519 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[])))
19. 5.964 5.964 ↑ 1.0 18,309 1

Seq Scan on entitystatus es (cost=0.00..352.09 rows=18,309 width=33) (actual time=0.013..5.964 rows=18,309 loops=1)

20. 0.845 1.549 ↑ 1.0 1,519 1

Hash (cost=35.99..35.99 rows=1,519 width=55) (actual time=1.549..1.549 rows=1,519 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 147kB
21. 0.704 0.704 ↑ 1.0 1,519 1

Seq Scan on contractsview uc (cost=0.00..35.99 rows=1,519 width=55) (actual time=0.006..0.704 rows=1,519 loops=1)

  • Filter: (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
22. 4.557 4.557 ↑ 1.0 1 1,519

Index Scan using cp_id on contract co (cost=0.00..0.04 rows=1 width=234) (actual time=0.003..0.003 rows=1 loops=1,519)

  • Index Cond: (id = es.entityid)
23. 10.633 10.633 ↑ 1.0 1 1,519

Index Scan using idx_contractoles_roles on contractroles ccn (cost=0.42..5.25 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=1,519)

  • Index Cond: ((contractid = co.id) AND (party = 'COUNTERPARTY'::text) AND (role = 'Negotiator'::text))
24. 6.076 78.988 ↓ 3.0 3 1,519

Subquery Scan on di (cost=54.32..54.34 rows=1 width=53) (actual time=0.048..0.052 rows=3 loops=1,519)

  • Filter: (di.contractid = uc.contractid)
25. 9.114 72.912 ↓ 3.0 3 1,519

HashAggregate (cost=54.32..54.33 rows=1 width=59) (actual time=0.046..0.048 rows=3 loops=1,519)

  • Group Key: dii.id, dii.contractid, dii.queued, dii.cpqueued, dii.status, dii.party, dii.nonstandardterm, uci.party, dii.external
26. 36.367 63.798 ↓ 3.0 3 1,519

Nested Loop (cost=8.37..54.30 rows=1 width=59) (actual time=0.014..0.042 rows=3 loops=1,519)

  • 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: 12
27. 7.595 12.152 ↑ 2.3 3 1,519

Bitmap Heap Scan on discussion dii (cost=4.07..35.72 rows=7 width=71) (actual time=0.005..0.008 rows=3 loops=1,519)

  • 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: 0
  • Heap Blocks: exact=1995
28. 4.557 4.557 ↑ 2.2 4 1,519

Bitmap Index Scan on di_contractid (cost=0.00..4.07 rows=9 width=0) (actual time=0.003..0.003 rows=4 loops=1,519)

  • Index Cond: (contractid = uc.contractid)
29. 9.583 15.279 ↓ 2.5 5 5,093

Materialize (cost=4.30..10.98 rows=2 width=56) (actual time=0.001..0.003 rows=5 loops=5,093)

30. 3.560 5.696 ↓ 1.5 3 712

Bitmap Heap Scan on documentrolesview uci (cost=4.30..10.97 rows=2 width=56) (actual time=0.005..0.008 rows=3 loops=712)

  • Recheck Cond: ((contractid = uc.contractid) AND (userid = uc.userid))
  • Heap Blocks: exact=1829
31. 2.136 2.136 ↓ 1.5 3 712

Bitmap Index Scan on idx_documentrolesview_contractid_documentid (cost=0.00..4.30 rows=2 width=0) (actual time=0.003..0.003 rows=3 loops=712)

  • Index Cond: ((contractid = uc.contractid) AND (userid = uc.userid))
32. 0.394 5.924 ↓ 1.5 850 1

Hash (cost=308.20..308.20 rows=579 width=24) (actual time=5.924..5.924 rows=850 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 55kB
33. 2.801 5.530 ↓ 1.5 850 1

HashAggregate (cost=296.62..302.41 rows=579 width=16) (actual time=5.194..5.530 rows=850 loops=1)

  • Group Key: udsc.contractid
34. 2.729 2.729 ↑ 1.0 5,523 1

Seq Scan on userdiscussions udsc (cost=0.00..269.00 rows=5,523 width=16) (actual time=0.008..2.729 rows=5,523 loops=1)

  • Filter: (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
  • Rows Removed by Filter: 5277
35. 11.788 11.788 ↓ 0.0 0 5,894

Index Scan using pk_additionaldata on additionaldata ad (cost=0.27..0.31 rows=1 width=48) (actual time=0.002..0.002 rows=0 loops=5,894)

  • Index Cond: (contractid = uc.contractid)
36. 29.470 29.470 ↑ 2.0 1 5,894

Index Scan using idx_contractoles_roles on contractroles cn (cost=0.42..1.70 rows=2 width=32) (actual time=0.004..0.005 rows=1 loops=5,894)

  • Index Cond: ((contractid = es.entityid) AND (party = 'PARTY'::text) AND (role = 'Negotiator'::text))
37. 0.067 2.668 ↓ 1.5 92 1

Hash (cost=1,573.22..1,573.22 rows=61 width=240) (actual time=2.668..2.668 rows=92 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
38. 0.075 2.601 ↓ 1.5 92 1

Hash Left Join (cost=1,345.85..1,573.22 rows=61 width=240) (actual time=2.379..2.601 rows=92 loops=1)

  • Hash Cond: ((dr_query.userid = da.userid) AND (dr_query.documentid = da.documentid))
39. 0.194 2.443 ↓ 1.5 92 1

Bitmap Heap Scan on documentroles dr_query (cost=1,339.33..1,565.31 rows=61 width=178) (actual time=2.282..2.443 rows=92 loops=1)

  • Recheck Cond: ((userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid) AND (role = ANY ('{Lead,Reviewer,Approver}'::text[])))
  • Filter: (NOT disabled)
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=54
40. 2.249 2.249 ↓ 1.6 95 1

Bitmap Index Scan on documentroles_pk (cost=0.00..1,339.32 rows=61 width=0) (actual time=2.249..2.249 rows=95 loops=1)

  • Index Cond: ((userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid) AND (role = ANY ('{Lead,Reviewer,Approver}'::text[])))
41. 0.023 0.083 ↑ 1.0 33 1

Hash (cost=6.03..6.03 rows=33 width=110) (actual time=0.083..0.083 rows=33 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
42. 0.060 0.060 ↑ 1.0 33 1

Seq Scan on documentapprove da (cost=0.00..6.03 rows=33 width=110) (actual time=0.011..0.060 rows=33 loops=1)

  • Filter: (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
  • Rows Removed by Filter: 209
43. 3.805 13.337 ↑ 1.0 7,088 1

Hash (cost=751.85..751.85 rows=7,088 width=88) (actual time=13.337..13.337 rows=7,088 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 874kB
44. 9.532 9.532 ↑ 1.0 7,088 1

Seq Scan on entityaccessed ea (cost=0.00..751.85 rows=7,088 width=88) (actual time=0.014..9.532 rows=7,088 loops=1)

  • Filter: (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
  • Rows Removed by Filter: 30740
45. 0.104 0.198 ↑ 1.0 243 1

Hash (cost=6.43..6.43 rows=243 width=35) (actual time=0.198..0.198 rows=243 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
46. 0.094 0.094 ↑ 1.0 243 1

Seq Scan on tenant ti (cost=0.00..6.43 rows=243 width=35) (actual time=0.004..0.094 rows=243 loops=1)

47. 20.442 20.442 ↑ 1.0 1 6,814

Index Scan using autonumber_sequences_assignment_contract_id_idx on autonumber_sequences_assignment asa (cost=0.00..0.06 rows=1 width=56) (actual time=0.002..0.003 rows=1 loops=6,814)

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

Hash (cost=1.01..1.01 rows=1 width=602) (actual time=0.009..0.009 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
49. 0.006 0.006 ↑ 1.0 1 1

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

50. 2.087 6.770 ↑ 1.0 4,421 1

Hash (cost=834.10..834.10 rows=4,518 width=33) (actual time=6.770..6.770 rows=4,421 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 345kB
51. 4.683 4.683 ↑ 1.0 4,421 1

Seq Scan on documents docs (cost=0.00..834.10 rows=4,518 width=33) (actual time=0.008..4.683 rows=4,421 loops=1)

  • Filter: ((NOT cancelled) AND (NOT removed) AND (status <> ALL ('{SIGNED,NSIGNED}'::text[])))
  • Rows Removed by Filter: 1747
52. 0.089 78.505 ↓ 4.5 183 1

Hash (cost=12,331.57..12,331.57 rows=41 width=18) (actual time=78.505..78.505 rows=183 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
53. 0.118 78.416 ↓ 4.5 183 1

Subquery Scan on reviewers_query (cost=12,330.55..12,331.57 rows=41 width=18) (actual time=78.238..78.416 rows=183 loops=1)

54. 0.639 78.298 ↓ 4.5 183 1

HashAggregate (cost=12,330.55..12,331.16 rows=41 width=49) (actual time=78.235..78.298 rows=183 loops=1)

  • Group Key: dr.contractid, (count(p.id) OVER (?) > 0), (count(a.userid) FILTER (WHERE (NOT a.registered)) OVER (?) > 0)
55. 1.169 77.659 ↓ 3.5 1,081 1

WindowAgg (cost=12,320.54..12,328.24 rows=308 width=49) (actual time=76.189..77.659 rows=1,081 loops=1)

56. 0.865 76.490 ↓ 3.5 1,081 1

Sort (cost=12,320.54..12,321.31 rows=308 width=49) (actual time=76.174..76.490 rows=1,081 loops=1)

  • Sort Key: dr.contractid
  • Sort Method: quicksort Memory: 139kB
57. 6.485 75.625 ↓ 3.5 1,081 1

Hash Right Join (cost=11,747.14..12,307.81 rows=308 width=49) (actual time=64.124..75.625 rows=1,081 loops=1)

  • Hash Cond: ((p.discussionid = dis.id) AND (p.userid = a.userid))
58. 5.122 5.122 ↑ 1.0 15,695 1

Seq Scan on post p (cost=0.00..442.95 rows=15,695 width=48) (actual time=0.003..5.122 rows=15,695 loops=1)

59. 0.562 64.018 ↓ 3.4 1,050 1

Hash (cost=11,742.52..11,742.52 rows=308 width=49) (actual time=64.018..64.018 rows=1,050 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 97kB
60. 0.774 63.456 ↓ 3.4 1,050 1

Nested Loop Left Join (cost=11,349.72..11,742.52 rows=308 width=49) (actual time=60.286..63.456 rows=1,050 loops=1)

61. 0.601 61.274 ↓ 1.8 352 1

Merge Join (cost=11,349.72..11,355.89 rows=192 width=49) (actual time=60.273..61.274 rows=352 loops=1)

  • Merge Cond: (dr.userid = a.userid)
62. 0.341 59.683 ↓ 1.8 352 1

Sort (cost=11,296.08..11,296.56 rows=192 width=48) (actual time=59.586..59.683 rows=352 loops=1)

  • Sort Key: dr.userid
  • Sort Method: quicksort Memory: 52kB
63. 0.800 59.342 ↓ 1.8 352 1

Hash Join (cost=844.84..11,288.80 rows=192 width=48) (actual time=2.351..59.342 rows=352 loops=1)

  • Hash Cond: (dr.documentid = d.id)
64. 56.231 56.231 ↓ 1.1 1,485 1

Seq Scan on documentroles dr (cost=0.00..10,436.88 rows=1,378 width=48) (actual time=0.022..56.231 rows=1,485 loops=1)

  • Filter: (role = 'Reviewer'::text)
  • Rows Removed by Filter: 370923
65. 0.366 2.311 ↑ 1.0 859 1

Hash (cost=834.10..834.10 rows=859 width=16) (actual time=2.311..2.311 rows=859 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 49kB
66. 1.945 1.945 ↑ 1.0 859 1

Seq Scan on documents d (cost=0.00..834.10 rows=859 width=16) (actual time=0.004..1.945 rows=859 loops=1)

  • Filter: (status = 'REVIEWING'::text)
  • Rows Removed by Filter: 5309
67. 0.700 0.990 ↓ 1.4 946 1

Sort (cost=53.64..55.29 rows=662 width=17) (actual time=0.671..0.990 rows=946 loops=1)

  • Sort Key: a.userid
  • Sort Method: quicksort Memory: 76kB
68. 0.290 0.290 ↑ 1.0 662 1

Seq Scan on account a (cost=0.00..22.62 rows=662 width=17) (actual time=0.007..0.290 rows=662 loops=1)

69. 1.408 1.408 ↑ 3.0 2 352

Index Scan using di_documentid on discussion dis (cost=0.00..1.95 rows=6 width=32) (actual time=0.002..0.004 rows=2 loops=352)

  • Index Cond: (d.id = documentid)
Planning time : 28.996 ms
Execution time : 909.225 ms