explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BAG

Settings
# exclusive inclusive rows x rows loops node
1. 3,494.470 14,795.692 ↑ 1.1 271,322 1

GroupAggregate (cost=1,569,229.90..1,652,728.70 rows=303,632 width=112) (actual time=10,980.464..14,795.692 rows=271,322 loops=1)

  • Group Key: usercontracts.contractid, doc.id, usercontracts.userid, usercontracts.party
2. 1,927.275 11,301.222 ↓ 1.8 556,866 1

Sort (cost=1,569,229.90..1,569,988.98 rows=303,632 width=112) (actual time=10,980.191..11,301.222 rows=556,866 loops=1)

  • Sort Key: usercontracts.contractid, doc.id, usercontracts.userid, usercontracts.party
  • Sort Method: external sort Disk: 41,464kB
3. 933.961 9,373.947 ↓ 1.8 556,866 1

Nested Loop (cost=105,025.50..1,523,937.73 rows=303,632 width=112) (actual time=2,852.376..9,373.947 rows=556,866 loops=1)

4. 523.421 4,718.333 ↓ 1.4 413,517 1

Subquery Scan on usercontracts (cost=105,024.91..127,576.77 rows=300,692 width=112) (actual time=2,852.303..4,718.333 rows=413,517 loops=1)

  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 199,108
5. 567.757 4,194.907 ↓ 1.0 612,625 1

Unique (cost=105,024.88..120,059.45 rows=601,383 width=80) (actual time=2,852.278..4,194.907 rows=612,625 loops=1)

6. 2,818.884 3,627.150 ↓ 1.0 612,625 1

Sort (cost=105,024.88..106,528.33 rows=601,383 width=80) (actual time=2,852.275..3,627.150 rows=612,625 loops=1)

  • Sort Key: contractroles.contractid, (NULL::uuid), contractroles.userid, contractroles.party, contractroles.disabled, contractroles.created, contractroles.role, contractroles.fromadmin, contractroles.invitationdate
  • Sort Method: external merge Disk: 54,496kB
7. 409.125 808.266 ↓ 1.0 612,625 1

Append (cost=0.00..20,575.85 rows=601,383 width=80) (actual time=0.020..808.266 rows=612,625 loops=1)

8. 161.166 161.166 ↓ 1.0 269,032 1

Seq Scan on contractroles (cost=0.00..5,973.49 rows=265,131 width=70) (actual time=0.019..161.166 rows=269,032 loops=1)

  • Filter: (NOT disabled)
  • Rows Removed by Filter: 6
9. 237.975 237.975 ↓ 1.0 343,593 1

Seq Scan on documentroles (cost=0.00..8,588.53 rows=336,252 width=87) (actual time=0.015..237.975 rows=343,593 loops=1)

  • Filter: (NOT disabled)
  • Rows Removed by Filter: 105
10.          

SubPlan (for Subquery Scan)

11. 0.005 0.005 ↑ 1.0 2 1

Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=32) (actual time=0.004..0.005 rows=2 loops=1)

12. 1,654.068 3,721.653 ↑ 1.0 1 413,517

Bitmap Heap Scan on documents doc (cost=0.59..4.63 rows=1 width=32) (actual time=0.007..0.009 rows=1 loops=413,517)

  • Recheck Cond: ((usercontracts.contractid = contractid) OR (usercontracts.documentid = id))
  • Filter: (((usercontracts.documentid IS NULL) AND (usercontracts.contractid = contractid)) OR (usercontracts.documentid = id))
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=1,165,044
13. 827.034 2,067.585 ↓ 0.0 0 413,517

BitmapOr (cost=0.59..0.59 rows=3 width=0) (actual time=0.005..0.005 rows=0 loops=413,517)

14. 827.034 827.034 ↓ 2.5 5 413,517

Bitmap Index Scan on documents_contract_id_idx (cost=0.00..0.30 rows=2 width=0) (actual time=0.002..0.002 rows=5 loops=413,517)

  • Index Cond: (usercontracts.contractid = contractid)
15. 413.517 413.517 ↑ 1.0 1 413,517

Bitmap Index Scan on pk_documents (cost=0.00..0.29 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=413,517)

  • Index Cond: (usercontracts.documentid = id)
Planning time : 1.286 ms
Execution time : 14,904.793 ms