explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NR9V

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 40,649.446 ↑ 1.0 101 1

Limit (cost=1,774,251.80..1,774,252.06 rows=101 width=400) (actual time=40,649.422..40,649.446 rows=101 loops=1)

2. 264.989 40,649.440 ↑ 2,775.5 101 1

Sort (cost=1,774,251.80..1,774,952.63 rows=280,329 width=400) (actual time=40,649.421..40,649.440 rows=101 loops=1)

  • Sort Key: thread.date_updated DESC NULLS LAST, thread.id DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 127kB
3. 12,478.753 40,384.451 ↓ 3.3 925,169 1

GroupAggregate (cost=1,755,808.66..1,763,517.71 rows=280,329 width=400) (actual time=26,713.233..40,384.451 rows=925,169 loops=1)

  • Group Key: thread.id
4. 10,882.413 27,905.698 ↓ 9.9 2,775,507 1

Sort (cost=1,755,808.66..1,756,509.49 rows=280,329 width=400) (actual time=26,712.613..27,905.698 rows=2,775,507 loops=1)

  • Sort Key: thread.id DESC NULLS LAST
  • Sort Method: external merge Disk: 1088232kB
5. 3,606.506 17,023.285 ↓ 9.9 2,775,507 1

Hash Left Join (cost=1,352,198.39..1,628,873.43 rows=280,329 width=400) (actual time=5,252.641..17,023.285 rows=2,775,507 loops=1)

  • Hash Cond: ((thread_entity.entity_type = ent.type) AND (thread_entity.entity_id = ent.id))
6. 3,212.010 12,912.348 ↓ 9.9 2,775,507 1

Hash Left Join (cost=1,307,016.36..1,538,997.98 rows=280,329 width=359) (actual time=4,608.014..12,912.348 rows=2,775,507 loops=1)

  • Hash Cond: (thread.id = thread_entity.thread_id)
7. 2,826.055 9,405.894 ↓ 9.9 2,775,507 1

Hash Join (cost=1,269,421.06..1,464,776.16 rows=280,329 width=332) (actual time=4,311.170..9,405.894 rows=2,775,507 loops=1)

  • Hash Cond: (thread_participant_organization.thread_id = thread.id)
8. 1,788.670 2,536.993 ↓ 3.0 2,775,507 1

Hash Join (cost=40,793.30..191,092.26 rows=923,159 width=102) (actual time=267.313..2,536.993 rows=2,775,507 loops=1)

  • Hash Cond: ((thread_participant_organization.organization_type = organization.type) AND (thread_participant_organization.organization_id = organization.id))
9. 482.470 482.470 ↑ 1.0 2,775,507 1

Seq Scan on thread_participant_organization (cost=0.00..55,508.07 rows=2,775,507 width=50) (actual time=0.320..482.470 rows=2,775,507 loops=1)

10. 174.961 265.853 ↑ 1.0 770,372 1

Hash (cost=18,704.72..18,704.72 rows=770,372 width=85) (actual time=265.853..265.853 rows=770,372 loops=1)

  • Buckets: 32768 Batches: 32 Memory Usage: 3083kB
11. 90.892 90.892 ↑ 1.0 770,372 1

Seq Scan on organization (cost=0.00..18,704.72 rows=770,372 width=85) (actual time=0.310..90.892 rows=770,372 loops=1)

12. 408.687 4,042.846 ↓ 3.1 925,169 1

Hash (cost=1,214,944.40..1,214,944.40 rows=299,389 width=246) (actual time=4,042.846..4,042.846 rows=925,169 loops=1)

  • Buckets: 16384 (originally 16384) Batches: 128 (originally 32) Memory Usage: 4069kB
13. 1,783.821 3,634.159 ↓ 3.1 925,169 1

Seq Scan on thread (cost=0.00..1,214,944.40 rows=299,389 width=246) (actual time=0.179..3,634.159 rows=925,169 loops=1)

  • Filter: ((NOT anonymized) AND ((SubPlan 1) && '{ROLE_MPS_OFFER_READ,ROLE_MPS_MODEL_READ,ROLE_MPS_MODEL_WRITE,ROLE_OP_TECHNICAL_SETTING_READ,ROLE_MPS_CATALOG_SEARCH,ROLE_OP_ORDER_DOCUMENT_WRITE,ROLE_OP_SHOP_ (...)
14.          

SubPlan (for Seq Scan)

15. 925.169 1,850.338 ↑ 1.0 1 925,169

Aggregate (cost=1.25..1.26 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=925,169)

16. 925.169 925.169 ↑ 25.0 4 925,169

Function Scan on jsonb_array_elements_text x (cost=0.00..1.00 rows=100 width=32) (actual time=0.001..0.001 rows=4 loops=925,169)

17. 177.937 294.444 ↑ 1.0 925,169 1

Hash (cost=17,898.69..17,898.69 rows=925,169 width=43) (actual time=294.444..294.444 rows=925,169 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 2645kB
18. 116.507 116.507 ↑ 1.0 925,169 1

Seq Scan on thread_entity (cost=0.00..17,898.69 rows=925,169 width=43) (actual time=0.191..116.507 rows=925,169 loops=1)

19. 259.673 504.431 ↑ 1.0 922,201 1

Hash (cost=17,840.01..17,840.01 rows=922,201 width=95) (actual time=504.431..504.431 rows=922,201 loops=1)

  • Buckets: 32768 Batches: 32 Memory Usage: 3830kB
20. 244.758 244.758 ↑ 1.0 922,201 1

Seq Scan on entity ent (cost=0.00..17,840.01 rows=922,201 width=95) (actual time=0.229..244.758 rows=922,201 loops=1)