explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 69vb

Settings
# exclusive inclusive rows x rows loops node
1. 3.384 108,974.354 ↓ 359.0 359 1

Nested Loop Left Join (cost=10,821.15..12,293.97 rows=1 width=224) (actual time=1,172.807..108,974.354 rows=359 loops=1)

2. 2.407 108,970.611 ↓ 359.0 359 1

Nested Loop Left Join (cost=10,821.00..12,293.75 rows=1 width=212) (actual time=1,172.792..108,970.611 rows=359 loops=1)

3. 50.693 108,959.947 ↓ 359.0 359 1

Hash Left Join (cost=10,820.86..12,293.42 rows=1 width=198) (actual time=1,172.751..108,959.947 rows=359 loops=1)

  • Hash Cond: ((generalledger.relatedid = xjournal.xjournalvoucherid) AND (generalledger.employeeid = xjournal.employeeid) AND (generalledger.accountid = xjournal.accountid))
  • Join Filter: (generalledger.relatedresource = 'journalvouchers'::text)
  • Filter: (CASE WHEN (COALESCE(generalledger.currencyid, 1) = 1) THEN (round((CASE WHEN (generalledger.credit > '0'::double precision) THEN (generalledger.credit - COALESCE(xjournal.debitbal,
  • Rows Removed by Filter: 2147
4. 801.929 108,813.850 ↓ 2,506.0 2,506 1

Nested Loop Left Join (cost=5,408.77..6,881.28 rows=1 width=182) (actual time=69.780..108,813.850 rows=2,506 loops=1)

  • Join Filter: ((generalledger.relatedresource = 'expenserequests'::text) AND (generalledger.relatedid = generalledger_1.xexpenserequestid) AND (generalledger.employeeid = generalledger
  • Rows Removed by Join Filter: 1250072
5. 19.888 131.127 ↓ 2,506.0 2,506 1

Nested Loop Left Join (cost=17.10..1,487.54 rows=1 width=166) (actual time=0.951..131.127 rows=2,506 loops=1)

  • Join Filter: (generalledger.relatedresource = 'journalvouchers'::text)
  • Rows Removed by Join Filter: 280
6. 5.030 33.553 ↓ 2,506.0 2,506 1

Nested Loop (cost=16.81..1,483.92 rows=1 width=129) (actual time=0.936..33.553 rows=2,506 loops=1)

7. 0.205 0.205 ↓ 2.0 2 1

Index Scan using accounts_pkey on accounts (cost=0.28..39.16 rows=1 width=30) (actual time=0.029..0.205 rows=2 loops=1)

  • Filter: (accountgroup = 'Employee Receivable'::text)
  • Rows Removed by Filter: 540
8. 27.544 28.318 ↓ 626.5 1,253 2

Bitmap Heap Scan on generalledger (cost=16.54..1,444.75 rows=2 width=103) (actual time=0.468..14.159 rows=1,253 loops=2)

  • Recheck Cond: (accountid = accounts.id)
  • Filter: ((NOT isdeleted) AND isactive AND (xsalesinvoiceid IS NULL) AND (xpurchaseinvoiceid IS NULL) AND (xjournalvoucherid IS NULL) AND (xexpenserequestid IS NULL) AND (xpayrollid IS NULL) AND (relatedresource = ANY ('{journalvouchers,expenserequests}'::text[])) AND (employeeid > 0) AND (debit > '0'::double precision) AND (companyid = 1) AND ((postingdate)::date <= '2020-02-14'::date))
  • Rows Removed by Filter: 2282
  • Heap Blocks: exact=1209
9. 0.774 0.774 ↓ 6.9 3,785 2

Bitmap Index Scan on gs_generalledger_accountid_partnerid (cost=0.00..16.54 rows=549 width=0) (actual time=0.387..0.387 rows=3,785 loops=2)

  • Index Cond: (accountid = accounts.id)
10. 77.686 77.686 ↑ 1.0 1 2,506

Index Scan using journalvouchers_pkey on journalvouchers (cost=0.29..3.60 rows=1 width=41) (actual time=0.031..0.031 rows=1 loops=2,506)

  • Index Cond: (id = generalledger.relatedid)
11. 1,831.886 107,880.794 ↓ 38.4 499 2,506

Finalize GroupAggregate (cost=5,391.67..5,393.35 rows=13 width=44) (actual time=40.803..43.049 rows=499 loops=2,506)

  • Group Key: generalledger_1.employeeid, generalledger_1.xexpenserequestid, generalledger_1.accountid
12. 34,735.666 106,048.908 ↓ 54.6 546 2,506

Gather Merge (cost=5,391.67..5,392.98 rows=10 width=44) (actual time=40.771..42.318 rows=546 loops=2,506)

  • Workers Planned: 2
  • Workers Launched: 2
13. 887.124 71,313.242 ↓ 37.2 186 7,518 / 3

Partial GroupAggregate (cost=4,391.65..4,391.80 rows=5 width=44) (actual time=27.983..28.457 rows=186 loops=7,518)

  • Group Key: generalledger_1.employeeid, generalledger_1.xexpenserequestid, generalledger_1.accountid
14. 1,496.082 70,426.118 ↓ 43.2 216 7,518 / 3

Sort (cost=4,391.65..4,391.66 rows=5 width=44) (actual time=27.974..28.103 rows=216 loops=7,518)

  • Sort Key: generalledger_1.employeeid, generalledger_1.xexpenserequestid, generalledger_1.accountid
  • Sort Method: quicksort Memory: 31kB
15. 68,930.036 68,930.036 ↓ 44.2 221 7,518 / 3

Parallel Seq Scan on generalledger generalledger_1 (cost=0.00..4,391.59 rows=5 width=44) (actual time=15.650..27.506 rows=221 loops=7,518)

  • Filter: (isactive AND (NOT isdeleted) AND (employeeid IS NOT NULL) AND (xexpenserequestid > 0) AND (companyid = 1) AND ((postingdate)::date <= '2020-02-14'::date))
  • Rows Removed by Filter: 49548
16. 1.637 95.404 ↓ 23.5 2,729 1

Hash (cost=5,410.06..5,410.06 rows=116 width=28) (actual time=95.404..95.404 rows=2,729 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 192kB
17. 2.116 93.767 ↓ 23.5 2,729 1

Subquery Scan on xjournal (cost=5,392.96..5,410.06 rows=116 width=28) (actual time=48.129..93.767 rows=2,729 loops=1)

18. 29.890 91.651 ↓ 23.5 2,729 1

Finalize GroupAggregate (cost=5,392.96..5,408.90 rows=116 width=44) (actual time=48.127..91.651 rows=2,729 loops=1)

  • Group Key: generalledger_2.employeeid, generalledger_2.xjournalvoucherid, generalledger_2.accountid
19. 24.199 61.761 ↓ 29.9 2,874 1

Gather Merge (cost=5,392.96..5,405.48 rows=96 width=44) (actual time=48.117..61.761 rows=2,874 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 1.098 37.562 ↓ 20.0 958 3 / 3

Partial GroupAggregate (cost=4,392.93..4,394.37 rows=48 width=44) (actual time=36.072..37.562 rows=958 loops=3)

  • Group Key: generalledger_2.employeeid, generalledger_2.xjournalvoucherid, generalledger_2.accountid
21. 4.004 36.464 ↓ 21.0 1,010 3 / 3

Sort (cost=4,392.93..4,393.05 rows=48 width=44) (actual time=36.064..36.464 rows=1,010 loops=3)

  • Sort Key: generalledger_2.employeeid, generalledger_2.xjournalvoucherid, generalledger_2.accountid
  • Sort Method: quicksort Memory: 187kB
22. 32.460 32.460 ↓ 21.0 1,010 3 / 3

Parallel Seq Scan on generalledger generalledger_2 (cost=0.00..4,391.59 rows=48 width=44) (actual time=3.862..32.460 rows=1,010 loops=3)

  • Filter: (isactive AND (NOT isdeleted) AND (employeeid IS NOT NULL) AND (xjournalvoucherid > 0) AND (companyid = 1) AND ((postingdate)::date <= '2020-02-14'::date))
  • Rows Removed by Filter: 48759
23. 8.257 8.257 ↑ 1.0 1 359

Index Scan using employees_pkey on employees (cost=0.14..0.33 rows=1 width=18) (actual time=0.023..0.023 rows=1 loops=359)

  • Index Cond: (id = generalledger.employeeid)
24. 0.359 0.359 ↓ 0.0 0 359

Index Scan using territories_pkey on territories (cost=0.15..0.17 rows=1 width=36) (actual time=0.001..0.001 rows=0 loops=359)

  • Index Cond: (id = journalvouchers.territoryid)
Planning time : 2.741 ms
Execution time : 108,977.646 ms