explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XWRb

Settings
# exclusive inclusive rows x rows loops node
1. 212.832 2,155.698 ↓ 1.0 51,162 1

WindowAgg (cost=33,359.24..57,648.56 rows=51,057 width=889) (actual time=2,111.682..2,155.698 rows=51,162 loops=1)

2. 1,271.074 1,942.866 ↓ 1.0 51,162 1

GroupAggregate (cost=33,359.24..57,010.34 rows=51,057 width=881) (actual time=315.619..1,942.866 rows=51,162 loops=1)

  • Group Key: entries._id
3. 54.058 671.792 ↓ 1.5 167,866 1

Merge Left Join (cost=33,359.24..53,848.87 rows=115,011 width=2,564) (actual time=315.432..671.792 rows=167,866 loops=1)

  • Merge Cond: (entries._id = "keyDates"."entryId")
4. 86.721 527.625 ↓ 1.5 81,614 1

Gather Merge (cost=33,358.82..48,244.55 rows=56,171 width=2,512) (actual time=315.405..527.625 rows=81,614 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 8.741 440.904 ↓ 1.2 27,205 3 / 3

Nested Loop Left Join (cost=32,358.80..40,761.00 rows=23,405 width=2,512) (actual time=295.271..440.904 rows=27,205 loops=3)

6. 22.089 404.958 ↓ 1.2 27,205 3 / 3

Nested Loop Left Join (cost=32,358.65..36,943.24 rows=23,405 width=1,972) (actual time=295.250..404.958 rows=27,205 loops=3)

7. 21.441 355.664 ↓ 1.2 27,205 3 / 3

Merge Left Join (cost=32,358.51..33,182.33 rows=23,405 width=911) (actual time=295.214..355.664 rows=27,205 loops=3)

  • Merge Cond: (entries._id = "entryAttachedFiles"."entryId")
8. 72.870 211.292 ↑ 1.2 17,054 3 / 3

Sort (cost=22,193.07..22,246.25 rows=21,274 width=814) (actual time=200.271..211.292 rows=17,054 loops=3)

  • Sort Key: entries._id
  • Sort Method: external merge Disk: 21,464kB
  • Worker 0: Sort Method: external merge Disk: 6,960kB
  • Worker 1: Sort Method: external merge Disk: 13,992kB
9. 20.544 138.422 ↑ 1.2 17,054 3 / 3

Hash Left Join (cost=3,361.50..13,026.81 rows=21,274 width=814) (actual time=86.637..138.422 rows=17,054 loops=3)

  • Hash Cond: (entries._id = "entryKeyDatesForSort"."entryId")
10. 19.350 43.893 ↑ 1.2 17,054 3 / 3

Hash Left Join (cost=249.33..9,596.33 rows=21,274 width=806) (actual time=12.426..43.893 rows=17,054 loops=3)

  • Hash Cond: (entries."issuingAuthorityId" = "issuingAuthority"._id)
11. 12.200 12.200 ↑ 1.2 17,054 3 / 3

Parallel Seq Scan on entries (cost=0.00..9,291.12 rows=21,274 width=713) (actual time=0.005..12.200 rows=17,054 loops=3)

  • Filter: ("isValid" AND "isPublished")
  • Rows Removed by Filter: 2,476
12. 6.129 12.343 ↑ 1.0 7,126 3 / 3

Hash (cost=160.26..160.26 rows=7,126 width=97) (actual time=12.342..12.343 rows=7,126 loops=3)

  • Buckets: 8,192 Batches: 1 Memory Usage: 949kB
13. 6.214 6.214 ↑ 1.0 7,126 3 / 3

Seq Scan on "issuingAuthorities" "issuingAuthority" (cost=0.00..160.26 rows=7,126 width=97) (actual time=0.026..6.214 rows=7,126 loops=3)

14. 28.221 73.985 ↓ 1.0 58,589 3 / 3

Hash (cost=2,382.54..2,382.54 rows=58,370 width=12) (actual time=73.985..73.985 rows=58,589 loops=3)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,030kB
15. 45.764 45.764 ↓ 1.0 58,589 3 / 3

Seq Scan on "entryKeyDates" "entryKeyDatesForSort" (cost=0.00..2,382.54 rows=58,370 width=12) (actual time=0.023..45.764 rows=58,589 loops=3)

  • Filter: ("dateTypeId" = 6)
  • Rows Removed by Filter: 61,374
16. 13.078 122.931 ↓ 1.0 64,497 3 / 3

Materialize (cost=10,165.41..10,487.70 rows=64,457 width=101) (actual time=94.929..122.931 rows=64,497 loops=3)

17. 64.797 109.853 ↓ 1.0 64,497 3 / 3

Sort (cost=10,165.41..10,326.55 rows=64,457 width=101) (actual time=94.925..109.853 rows=64,497 loops=3)

  • Sort Key: "entryAttachedFiles"."entryId
  • Sort Method: external merge Disk: 7,376kB
  • Worker 0: Sort Method: external merge Disk: 7,376kB
  • Worker 1: Sort Method: external merge Disk: 7,376kB
18. 45.056 45.056 ↓ 1.0 64,532 3 / 3

Seq Scan on "entryAttachedFiles" (cost=0.00..1,488.57 rows=64,457 width=101) (actual time=0.035..45.056 rows=64,532 loops=3)

19. 27.205 27.205 ↑ 1.0 1 81,614 / 3

Index Scan using "entryTypes_pkey" on "entryTypes" (cost=0.14..0.16 rows=1 width=1,065) (actual time=0.001..0.001 rows=1 loops=81,614)

  • Index Cond: (_id = entries."entryTypeId")
20. 27.205 27.205 ↑ 1.0 1 81,614 / 3

Index Scan using "fileTypes_pkey" on "fileTypes" (cost=0.14..0.16 rows=1 width=548) (actual time=0.001..0.001 rows=1 loops=81,614)

  • Index Cond: (_id = "entryAttachedFiles"."fileTypeId")
21. 90.109 90.109 ↓ 1.5 182,961 1

Index Scan using entrykeydates_entryid_idx on "entryKeyDates" "keyDates" (cost=0.42..4,013.86 rows=119,963 width=56) (actual time=0.023..90.109 rows=182,961 loops=1)