explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zFqx

Settings
# exclusive inclusive rows x rows loops node
1. 0.196 74.764 ↑ 1.0 1 1

Aggregate (cost=7,746.19..7,746.20 rows=1 width=1) (actual time=74.764..74.764 rows=1 loops=1)

2. 0.349 74.568 ↓ 6.8 518 1

GroupAggregate (cost=7,514.96..7,745.05 rows=76 width=17) (actual time=65.758..74.568 rows=518 loops=1)

  • Group Key: data.id
3. 1.332 74.219 ↑ 3.1 518 1

Subquery Scan on data (cost=7,514.96..7,735.95 rows=1,630 width=17) (actual time=65.749..74.219 rows=518 loops=1)

  • Filter: ((NOT data.cancelled) AND ((data.documentstatus = ANY ('{EXECUTED,NEXECUTED,EXPIRED,SIGNED,NSIGNED}'::text[])) OR data.createdasexecuted OR data.terminated))
  • Rows Removed by Filter: 2545
4. 1.802 72.887 ↑ 1.4 3,063 1

Unique (cost=7,514.96..7,665.88 rows=4,312 width=45) (actual time=65.719..72.887 rows=3,063 loops=1)

5. 4.076 71.085 ↑ 1.4 3,063 1

Group (cost=7,514.96..7,655.10 rows=4,312 width=45) (actual time=65.717..71.085 rows=3,063 loops=1)

  • Group Key: co.id, es.status, c.status, ad.expirationdate
6. 5.212 67.009 ↓ 1.3 5,499 1

Sort (cost=7,514.96..7,525.74 rows=4,312 width=45) (actual time=65.697..67.009 rows=5,499 loops=1)

  • Sort Key: co.id, es.status, c.status, ad.expirationdate
  • Sort Method: quicksort Memory: 626kB
7. 3.347 61.797 ↓ 1.3 5,499 1

Hash Right Join (cost=6,303.61..7,254.64 rows=4,312 width=45) (actual time=55.396..61.797 rows=5,499 loops=1)

  • Hash Cond: (d.contractid = co.id)
8. 3.076 3.076 ↓ 1.2 4,998 1

Seq Scan on documents d (cost=0.00..891.76 rows=4,306 width=16) (actual time=0.011..3.076 rows=4,998 loops=1)

  • Filter: (status <> ALL ('{SIGNED,NSIGNED}'::text[]))
  • Rows Removed by Filter: 740
9. 1.762 55.374 ↑ 1.1 3,063 1

Hash (cost=6,261.02..6,261.02 rows=3,407 width=45) (actual time=55.374..55.374 rows=3,063 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 241kB
10. 2.189 53.612 ↑ 1.1 3,063 1

Nested Loop (cost=884.01..6,261.02 rows=3,407 width=45) (actual time=15.713..53.612 rows=3,063 loops=1)

  • Join Filter: (co.id = cr.contractid)
11. 1.913 35.968 ↓ 1.4 3,091 1

Hash Left Join (cost=883.58..2,946.36 rows=2,288 width=77) (actual time=15.694..35.968 rows=3,091 loops=1)

  • Hash Cond: (c.contractid = ad.contractid)
12. 5.607 33.845 ↓ 1.4 3,091 1

Nested Loop (cost=872.11..2,923.94 rows=2,288 width=69) (actual time=15.478..33.845 rows=3,091 loops=1)

  • Join Filter: (c.contractid = co.id)
13. 3.782 22.056 ↓ 1.4 3,091 1

Hash Join (cost=872.11..2,760.30 rows=2,288 width=49) (actual time=15.459..22.056 rows=3,091 loops=1)

  • Hash Cond: ((c.contractid = es.entityid) AND (c.party = es.party))
  • Join Filter: (((es.status <> ALL ('{NEGOTIATING,NAPPROVING,NAPPROVED,EXECUTED,NSIGNING,NSIGNED}'::text[])) AND (c.party = 'PARTY'::text)) OR (es.status = ANY ('{NEGOTIATING,NAPPROVING,NAPPROVED,EXECUTED,NSIGNING,NSIGNED}'::text[])))
14. 3.007 3.331 ↓ 1.0 3,091 1

Bitmap Heap Scan on contractsview c (cost=88.24..1,882.44 rows=3,074 width=31) (actual time=0.484..3.331 rows=3,091 loops=1)

  • Recheck Cond: (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
  • Heap Blocks: exact=1418
15. 0.324 0.324 ↓ 1.0 3,091 1

Bitmap Index Scan on contractsview_userid_idx (cost=0.00..87.48 rows=3,074 width=0) (actual time=0.324..0.324 rows=3,091 loops=1)

  • Index Cond: (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
16. 7.130 14.943 ↓ 1.3 17,067 1

Hash (cost=591.67..591.67 rows=12,813 width=33) (actual time=14.943..14.943 rows=17,067 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1356kB
17. 7.813 7.813 ↓ 1.3 17,067 1

Seq Scan on entitystatus es (cost=0.00..591.67 rows=12,813 width=33) (actual time=0.007..7.813 rows=17,067 loops=1)

  • Filter: ((status <> ALL ('{NEGOTIATING,NAPPROVING,NAPPROVED,EXECUTED,NSIGNING,NSIGNED}'::text[])) OR (status = ANY ('{NEGOTIATING,NAPPROVING,NAPPROVED,EXECUTED,NSIGNING,NSIGNED}'::text[])))
18. 6.182 6.182 ↑ 1.0 1 3,091

Index Scan using cp_id on contract co (cost=0.00..0.06 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=3,091)

  • Index Cond: (id = es.entityid)
19. 0.101 0.210 ↑ 1.0 332 1

Hash (cost=7.32..7.32 rows=332 width=24) (actual time=0.210..0.210 rows=332 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
20. 0.109 0.109 ↑ 1.0 332 1

Seq Scan on additionaldata ad (cost=0.00..7.32 rows=332 width=24) (actual time=0.004..0.109 rows=332 loops=1)

21. 15.455 15.455 ↑ 2.0 1 3,091

Index Only Scan using idx_contractoles_roles on contractroles cr (cost=0.42..1.42 rows=2 width=16) (actual time=0.004..0.005 rows=1 loops=3,091)

  • Index Cond: ((contractid = es.entityid) AND (party = 'PARTY'::text) AND (role = 'Negotiator'::text))
  • Heap Fetches: 0
Planning time : 2.342 ms
Execution time : 74.910 ms