explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ssXs

Settings
# exclusive inclusive rows x rows loops node
1. 3.150 2,121.217 ↑ 2.1 3,208 1

Unique (cost=220,300.89..221,370.78 rows=6,793 width=1,209) (actual time=2,117.102..2,121.217 rows=3,208 loops=1)

2. 5.079 2,118.067 ↑ 2.1 3,208 1

Sort (cost=220,300.89..220,317.87 rows=6,793 width=1,209) (actual time=2,117.101..2,118.067 rows=3,208 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 (a_approvers.approve IS FALSE))), ((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: 2104kB
3. 319.067 2,112.988 ↑ 2.1 3,208 1

GroupAggregate (cost=215,231.08..216,267.02 rows=6,793 width=1,209) (actual time=1,688.342..2,112.988 rows=3,208 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. 1,155.980 1,793.921 ↓ 25.3 172,073 1

Sort (cost=215,231.08..215,248.07 rows=6,793 width=1,209) (actual time=1,688.291..1,793.921 rows=172,073 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: 106752kB
5. 108.886 637.941 ↓ 25.3 172,073 1

Hash Left Join (cost=16,330.96..211,197.22 rows=6,793 width=1,209) (actual time=115.213..637.941 rows=172,073 loops=1)

  • Hash Cond: (co.id = a_approvers.contract_id)
6. 39.774 528.757 ↓ 9.5 64,750 1

Hash Left Join (cost=16,314.90..211,081.05 rows=6,793 width=1,208) (actual time=114.906..528.757 rows=64,750 loops=1)

  • Hash Cond: (co.id = docs.contractid)
7. 9.917 482.936 ↓ 2.2 11,758 1

Hash Left Join (cost=15,424.33..209,557.42 rows=5,447 width=1,191) (actual time=108.847..482.936 rows=11,758 loops=1)

  • Hash Cond: ((uc.userid = dr_query.userid) AND (uc.contractid = dr_query.contractid))
8. 10.069 470.676 ↓ 2.0 10,834 1

Hash Left Join (cost=13,878.48..207,329.88 rows=5,447 width=1,093) (actual time=106.498..470.676 rows=10,834 loops=1)

  • Hash Cond: (co.counterpartycompanyid = ti.id)
9. 11.483 460.417 ↓ 2.0 10,834 1

Hash Left Join (cost=13,869.01..207,253.85 rows=5,447 width=1,074) (actual time=106.300..460.417 rows=10,834 loops=1)

  • Hash Cond: ((uc.userid = ea.userid) AND (co.id = ea.entityid))
10. 11.749 439.619 ↓ 2.0 10,834 1

Hash Left Join (cost=13,011.82..206,000.05 rows=5,447 width=1,018) (actual time=96.973..439.619 rows=10,834 loops=1)

  • Hash Cond: ((co.tenantid = asa.tenant_id) AND (co.id = asa.contract_id))
11. 8.462 424.634 ↓ 2.0 10,799 1

Hash Left Join (cost=12,910.91..205,819.41 rows=5,447 width=408) (actual time=93.728..424.634 rows=10,799 loops=1)

  • Hash Cond: (co.id = reviewers_query.contractid)
12. 8.925 336.100 ↓ 2.0 10,799 1

Hash Left Join (cost=711.38..193,599.18 rows=5,447 width=406) (actual time=13.650..336.100 rows=10,799 loops=1)

  • Hash Cond: (uc.contractid = ad.contractid)
13. 11.790 326.891 ↓ 2.0 10,799 1

Nested Loop (cost=699.55..193,561.07 rows=5,447 width=374) (actual time=13.357..326.891 rows=10,799 loops=1)

  • Join Filter: (co.id = cn.contractid)
14. 9.851 260.896 ↓ 3.4 10,841 1

Hash Left Join (cost=699.13..187,955.26 rows=3,236 width=374) (actual time=13.333..260.896 rows=10,841 loops=1)

  • Hash Cond: (uc.contractid = udsc.contractid)
15. 22.066 245.300 ↓ 3.4 10,841 1

Nested Loop (cost=383.69..187,391.33 rows=3,236 width=366) (actual time=7.577..245.300 rows=10,841 loops=1)

  • Join Filter: (uc.party = es.party)
  • Rows Removed by Join Filter: 10841
16. 8.820 190.711 ↓ 3.4 10,841 1

Nested Loop Left Join (cost=383.41..185,302.19 rows=3,236 width=342) (actual time=7.562..190.711 rows=10,841 loops=1)

17. 3.834 49.215 ↑ 1.0 3,236 1

Hash Join (cost=328.78..8,420.91 rows=3,236 width=305) (actual time=7.535..49.215 rows=3,236 loops=1)

  • Hash Cond: (co.id = uc.contractid)
18. 4.302 42.168 ↑ 1.0 3,623 1

Hash Right Join (cost=180.52..8,226.70 rows=3,623 width=250) (actual time=4.314..42.168 rows=3,623 loops=1)

  • Hash Cond: (ccn.contractid = co.id)
19. 33.570 33.570 ↓ 42.3 2,327 1

Seq Scan on contractroles ccn (cost=0.00..8,045.43 rows=55 width=32) (actual time=0.009..33.570 rows=2,327 loops=1)

  • Filter: ((party = 'COUNTERPARTY'::text) AND (role = 'Negotiator'::text))
  • Rows Removed by Filter: 292768
20. 2.147 4.296 ↑ 1.0 3,623 1

Hash (cost=135.23..135.23 rows=3,623 width=234) (actual time=4.296..4.296 rows=3,623 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 783kB
21. 2.149 2.149 ↑ 1.0 3,623 1

Seq Scan on contract co (cost=0.00..135.23 rows=3,623 width=234) (actual time=0.004..2.149 rows=3,623 loops=1)

22. 1.439 3.213 ↑ 1.0 3,236 1

Hash (cost=107.81..107.81 rows=3,236 width=55) (actual time=3.213..3.213 rows=3,236 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 308kB
23. 1.774 1.774 ↑ 1.0 3,236 1

Seq Scan on contractsview uc (cost=0.00..107.81 rows=3,236 width=55) (actual time=0.008..1.774 rows=3,236 loops=1)

  • Filter: ((userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid) AND ((party = 'PARTY'::text) OR (status = ANY ('{NEGOTIATING,NAPPROVING,NAPPROVED,EXECUTED,NSIGNING,NSIGNED}'::text[]))))
24. 12.944 132.676 ↓ 3.0 3 3,236

Subquery Scan on di (cost=54.63..54.65 rows=1 width=53) (actual time=0.038..0.041 rows=3 loops=3,236)

  • Filter: (di.contractid = uc.contractid)
25. 12.944 119.732 ↓ 3.0 3 3,236

HashAggregate (cost=54.63..54.64 rows=1 width=59) (actual time=0.036..0.037 rows=3 loops=3,236)

  • Group Key: dii.id, dii.contractid, dii.queued, dii.cpqueued, dii.status, dii.party, dii.nonstandardterm, uci.party, dii.external
26. 58.129 106.788 ↓ 3.0 3 3,236

Nested Loop (cost=8.37..54.61 rows=1 width=59) (actual time=0.011..0.033 rows=3 loops=3,236)

  • 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
27. 16.180 22.652 ↑ 2.3 3 3,236

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

  • 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=3068
28. 6.472 6.472 ↑ 3.0 3 3,236

Bitmap Index Scan on di_contractid (cost=0.00..4.07 rows=9 width=0) (actual time=0.002..0.002 rows=3 loops=3,236)

  • Index Cond: (contractid = uc.contractid)
29. 17.575 26.007 ↓ 2.0 4 8,669

Materialize (cost=4.30..11.29 rows=2 width=56) (actual time=0.001..0.003 rows=4 loops=8,669)

30. 5.270 8.432 ↓ 1.5 3 1,054

Bitmap Heap Scan on documentrolesview uci (cost=4.30..11.28 rows=2 width=56) (actual time=0.005..0.008 rows=3 loops=1,054)

  • Recheck Cond: ((contractid = uc.contractid) AND (userid = uc.userid))
  • Heap Blocks: exact=2699
31. 3.162 3.162 ↓ 1.5 3 1,054

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=1,054)

  • Index Cond: ((contractid = uc.contractid) AND (userid = uc.userid))
32. 32.523 32.523 ↑ 1.0 2 10,841

Index Scan using es_entityid_party on entitystatus es (cost=0.29..0.62 rows=2 width=33) (actual time=0.002..0.003 rows=2 loops=10,841)

  • Index Cond: (entityid = co.id)
33. 0.346 5.745 ↓ 1.5 850 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 55kB
34. 2.793 5.399 ↓ 1.5 850 1

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

  • Group Key: udsc.contractid
35. 2.606 2.606 ↑ 1.0 5,523 1

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

  • Filter: (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
  • Rows Removed by Filter: 5277
36. 54.205 54.205 ↑ 2.0 1 10,841

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

  • Index Cond: ((contractid = es.entityid) AND (party = 'PARTY'::text) AND (role = 'Negotiator'::text))
37. 0.143 0.284 ↑ 1.0 348 1

Hash (cost=7.48..7.48 rows=348 width=48) (actual time=0.284..0.284 rows=348 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
38. 0.141 0.141 ↑ 1.0 348 1

Seq Scan on additionaldata ad (cost=0.00..7.48 rows=348 width=48) (actual time=0.004..0.141 rows=348 loops=1)

39. 0.077 80.072 ↓ 10.2 183 1

Hash (cost=12,199.30..12,199.30 rows=18 width=18) (actual time=80.072..80.072 rows=183 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
40. 0.106 79.995 ↓ 10.2 183 1

Subquery Scan on reviewers_query (cost=12,198.85..12,199.30 rows=18 width=18) (actual time=79.828..79.995 rows=183 loops=1)

41. 0.578 79.889 ↓ 10.2 183 1

HashAggregate (cost=12,198.85..12,199.12 rows=18 width=49) (actual time=79.826..79.889 rows=183 loops=1)

  • Group Key: dr.contractid, (count(p.id) OVER (?) > 0), (count(a.userid) FILTER (WHERE (NOT a.registered)) OVER (?) > 0)
42. 1.094 79.311 ↓ 8.1 1,081 1

WindowAgg (cost=12,194.53..12,197.86 rows=133 width=49) (actual time=77.910..79.311 rows=1,081 loops=1)

43. 0.836 78.217 ↓ 8.1 1,081 1

Sort (cost=12,194.53..12,194.86 rows=133 width=49) (actual time=77.897..78.217 rows=1,081 loops=1)

  • Sort Key: dr.contractid
  • Sort Method: quicksort Memory: 139kB
44. 6.571 77.381 ↓ 8.1 1,081 1

Hash Right Join (cost=11,629.17..12,189.84 rows=133 width=49) (actual time=65.887..77.381 rows=1,081 loops=1)

  • Hash Cond: ((p.discussionid = dis.id) AND (p.userid = a.userid))
45. 5.024 5.024 ↑ 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.024 rows=15,695 loops=1)

46. 0.588 65.786 ↓ 7.9 1,050 1

Hash (cost=11,627.17..11,627.17 rows=133 width=49) (actual time=65.786..65.786 rows=1,050 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 97kB
47. 1.063 65.198 ↓ 7.9 1,050 1

Nested Loop Left Join (cost=845.11..11,627.17 rows=133 width=49) (actual time=2.456..65.198 rows=1,050 loops=1)

48. 0.794 62.727 ↓ 4.2 352 1

Nested Loop (cost=845.11..11,460.04 rows=83 width=49) (actual time=2.444..62.727 rows=352 loops=1)

49. 0.807 61.229 ↓ 4.2 352 1

Hash Join (cost=844.84..11,423.27 rows=83 width=48) (actual time=2.430..61.229 rows=352 loops=1)

  • Hash Cond: (dr.documentid = d.id)
50. 58.125 58.125 ↓ 2.5 1,485 1

Seq Scan on documentroles dr (cost=0.00..10,575.36 rows=598 width=48) (actual time=0.120..58.125 rows=1,485 loops=1)

  • Filter: (role = 'Reviewer'::text)
  • Rows Removed by Filter: 376596
51. 0.362 2.297 ↑ 1.0 859 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 49kB
52. 1.935 1.935 ↑ 1.0 859 1

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

  • Filter: (status = 'REVIEWING'::text)
  • Rows Removed by Filter: 5309
53. 0.704 0.704 ↑ 1.0 1 352

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

  • Index Cond: (userid = dr.userid)
54. 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)
55. 0.985 3.236 ↑ 1.0 1,881 1

Hash (cost=72.70..72.70 rows=1,881 width=642) (actual time=3.236..3.236 rows=1,881 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 310kB
56. 1.610 2.251 ↑ 1.0 1,881 1

Hash Left Join (cost=1.02..72.70 rows=1,881 width=642) (actual time=0.013..2.251 rows=1,881 loops=1)

  • Hash Cond: (asa.sequence_id = aseq.id)
57. 0.636 0.636 ↑ 1.0 1,881 1

Seq Scan on autonumber_sequences_assignment asa (cost=0.00..45.81 rows=1,881 width=56) (actual time=0.004..0.636 rows=1,881 loops=1)

58. 0.002 0.005 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
59. 0.003 0.003 ↑ 1.0 1 1

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

60. 3.152 9.315 ↓ 1.0 7,088 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 874kB
61. 6.163 6.163 ↓ 1.0 7,088 1

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
63. 0.090 0.090 ↑ 1.0 243 1

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

64. 0.056 2.343 ↓ 1.9 92 1

Hash (cost=1,545.13..1,545.13 rows=48 width=162) (actual time=2.343..2.343 rows=92 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
65. 2.287 2.287 ↓ 1.9 92 1

Index Scan using documentroles_pk on documentroles dr_query (cost=0.42..1,545.13 rows=48 width=162) (actual time=0.076..2.287 rows=92 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
66. 1.910 6.047 ↑ 1.0 4,421 1

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

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

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

  • Filter: ((NOT cancelled) AND (NOT removed) AND (status <> ALL ('{SIGNED,NSIGNED}'::text[])))
  • Rows Removed by Filter: 1747
68. 0.158 0.298 ↑ 1.0 358 1

Hash (cost=11.58..11.58 rows=358 width=17) (actual time=0.298..0.298 rows=358 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
69. 0.140 0.140 ↑ 1.0 358 1

Seq Scan on approval_approver a_approvers (cost=0.00..11.58 rows=358 width=17) (actual time=0.006..0.140 rows=358 loops=1)

Planning time : 10.028 ms
Execution time : 2,158.706 ms