explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YbIP

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.011 7,281.553 ↑ 1.0 101 1

Limit (cost=637,638.79..637,639.05 rows=101 width=303) (actual time=7,281.534..7,281.553 rows=101 loops=1)

2. 77.372 7,281.542 ↑ 910.9 101 1

Sort (cost=637,638.79..637,868.79 rows=91,998 width=303) (actual time=7,281.532..7,281.542 rows=101 loops=1)

  • Sort Key: thread.date_updated DESC NULLS LAST, thread.id DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 191kB
3. 2,640.153 7,204.170 ↓ 3.1 284,290 1

GroupAggregate (cost=629,286.20..634,116.09 rows=91,998 width=303) (actual time=4,380.339..7,204.170 rows=284,290 loops=1)

  • Group Key: thread.id
4. 1,232.022 4,564.017 ↓ 3.1 852,870 1

Sort (cost=629,286.20..629,976.18 rows=275,994 width=385) (actual time=4,380.302..4,564.017 rows=852,870 loops=1)

  • Sort Key: thread.id DESC NULLS LAST
  • Sort Method: external merge Disk: 318888kB
5. 814.195 3,331.995 ↓ 3.1 852,870 1

Hash Join (cost=434,079.49..506,232.24 rows=275,994 width=385) (actual time=1,859.076..3,331.995 rows=852,870 loops=1)

  • Hash Cond: ((thread_participant_organization.organization_type = organization.type) AND (thread_participant_organization.organization_id = organization.id))
6. 560.025 2,431.950 ↓ 3.1 852,870 1

Hash Join (cost=422,416.89..465,887.67 rows=275,994 width=331) (actual time=1,772.618..2,431.950 rows=852,870 loops=1)

  • Hash Cond: (thread_participant_organization.thread_id = thread.id)
7. 99.331 99.331 ↑ 1.0 852,870 1

Seq Scan on thread_participant_organization (cost=0.00..17,057.70 rows=852,870 width=50) (actual time=0.009..99.331 rows=852,870 loops=1)

8. 122.636 1,772.594 ↓ 3.1 284,290 1

Hash (cost=417,402.91..417,402.91 rows=91,998 width=313) (actual time=1,772.594..1,772.594 rows=284,290 loops=1)

  • Buckets: 16384 (originally 16384) Batches: 32 (originally 16) Memory Usage: 3969kB
9. 270.535 1,649.958 ↓ 3.1 284,290 1

Hash Join (cost=24,073.47..417,402.91 rows=91,998 width=313) (actual time=208.773..1,649.958 rows=284,290 loops=1)

  • Hash Cond: ((thread_entity.entity_type = ent.type) AND (thread_entity.entity_id = ent.id))
10. 215.096 1,251.234 ↓ 3.1 284,290 1

Hash Join (cost=10,988.52..393,397.97 rows=91,998 width=277) (actual time=80.424..1,251.234 rows=284,290 loops=1)

  • Hash Cond: (thread.id = thread_entity.thread_id)
11. 387.268 955.848 ↓ 3.1 284,290 1

Seq Scan on thread (cost=0.00..372,992.47 rows=91,998 width=239) (actual time=0.025..955.848 rows=284,290 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 (...)
12.          

SubPlan (forSeq Scan)

13. 284.290 568.580 ↑ 1.0 1 284,290

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

14. 284.290 284.290 ↑ 25.0 4 284,290

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=284,290)

15. 49.953 80.290 ↑ 1.0 284,290 1

Hash (cost=5,212.90..5,212.90 rows=284,290 width=38) (actual time=80.290..80.290 rows=284,290 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2937kB
16. 30.337 30.337 ↑ 1.0 284,290 1

Seq Scan on thread_entity (cost=0.00..5,212.90 rows=284,290 width=38) (actual time=0.007..30.337 rows=284,290 loops=1)

17. 61.577 128.189 ↑ 1.0 284,278 1

Hash (cost=5,211.78..5,211.78 rows=284,278 width=80) (actual time=128.188..128.189 rows=284,278 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 2415kB
18. 66.612 66.612 ↑ 1.0 284,278 1

Seq Scan on entity ent (cost=0.00..5,211.78 rows=284,278 width=80) (actual time=0.010..66.612 rows=284,278 loops=1)

19. 56.531 85.850 ↑ 1.0 217,544 1

Hash (cost=5,424.44..5,424.44 rows=217,544 width=87) (actual time=85.850..85.850 rows=217,544 loops=1)

  • Buckets: 32768 Batches: 8 Memory Usage: 3474kB
20. 29.319 29.319 ↑ 1.0 217,544 1

Seq Scan on organization (cost=0.00..5,424.44 rows=217,544 width=87) (actual time=0.056..29.319 rows=217,544 loops=1)