explain.depesz.com

PostgreSQL's explain analyze made readable

Result: b9nm

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

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

2. 0.384 73.622 ↓ 6.8 518 1

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

  • Group Key: data.id
3. 1.436 73.238 ↑ 3.1 518 1

Subquery Scan on data (cost=7,514.96..7,735.95 rows=1,630 width=17) (actual time=64.813..73.238 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.732 71.802 ↑ 1.4 3,063 1

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

5. 4.149 70.070 ↑ 1.4 3,063 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Hash Join (cost=872.11..2,760.30 rows=2,288 width=49) (actual time=15.388..21.773 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.031 3.364 ↓ 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.499..3.364 rows=3,091 loops=1)

  • Recheck Cond: (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid)
  • Heap Blocks: exact=1418
15. 0.333 0.333 ↓ 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.333..0.333 rows=3,091 loops=1)

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

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

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

Seq Scan on entitystatus es (cost=0.00..591.67 rows=12,813 width=33) (actual time=0.006..7.909 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.097 0.218 ↑ 1.0 332 1

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

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

Seq Scan on additionaldata ad (cost=0.00..7.32 rows=332 width=24) (actual time=0.003..0.121 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.340 ms
Execution time : 73.963 ms