explain.depesz.com

PostgreSQL's explain analyze made readable

Result: f4Zh

Settings
# exclusive inclusive rows x rows loops node
1. 0.622 7,523.007 ↑ 1.0 51 1

Limit (cost=32,682.74..32,682.87 rows=51 width=40) (actual time=7,522.091..7,523.007 rows=51 loops=1)

2. 5.500 7,522.385 ↑ 25.7 51 1

Sort (cost=32,682.74..32,686.02 rows=1,312 width=40) (actual time=7,522.077..7,522.385 rows=51 loops=1)

  • Sort Key: thread.date_updated DESC NULLS LAST, thread_participant_organization.thread_id DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 28kB
3. 18.363 7,516.885 ↑ 2.1 631 1

Nested Loop (cost=16,431.44..32,638.97 rows=1,312 width=40) (actual time=497.205..7,516.885 rows=631 loops=1)

  • Join Filter: (thread_participant_organization.thread_id = thread.id)
4. 368.142 7,402.610 ↑ 3.1 631 1

Nested Loop (cost=16,431.02..31,413.29 rows=1,952 width=48) (actual time=495.054..7,402.610 rows=631 loops=1)

5. 3,085.459 6,575.884 ↓ 4.3 24,136 1

Hash Join (cost=16,430.59..26,843.73 rows=5,620 width=32) (actual time=494.665..6,575.884 rows=24,136 loops=1)

  • Hash Cond: (thread_entity.thread_id = sub_thread_participant.thread_id)
6. 2,996.590 2,996.590 ↑ 1.0 400,911 1

Seq Scan on thread_entity (cost=0.00..8,853.53 rows=400,911 width=16) (actual time=0.035..2,996.590 rows=400,911 loops=1)

  • Filter: (entity_type = ANY ('{MMP_ORDER,MMP_OFFER,MPS_ORDER}'::text[]))
7. 198.806 493.835 ↓ 4.3 24,136 1

Hash (cost=16,360.34..16,360.34 rows=5,620 width=16) (actual time=493.835..493.835 rows=24,136 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,142kB
8. 230.688 295.029 ↓ 4.3 24,136 1

Bitmap Heap Scan on thread_participant_organization sub_thread_participant (cost=490.43..16,360.34 rows=5,620 width=16) (actual time=65.926..295.029 rows=24,136 loops=1)

  • Recheck Cond: ((organization_id = '2630'::text) AND (organization_type = 'SHOP'::text))
  • Filter: has_contributed
  • Heap Blocks: exact=8,846
9. 64.341 64.341 ↓ 3.0 24,136 1

Bitmap Index Scan on idx_thread_participant_participant_id_participant_type (cost=0.00..489.03 rows=8,060 width=0) (actual time=64.341..64.341 rows=24,136 loops=1)

  • Index Cond: ((organization_id = '2630'::text) AND (organization_type = 'SHOP'::text))
10. 458.584 458.584 ↓ 0.0 0 24,136

Index Scan using idx_thread_participant_thread_id on thread_participant_organization (cost=0.43..0.80 rows=1 width=16) (actual time=0.019..0.019 rows=0 loops=24,136)

  • Index Cond: (thread_id = thread_entity.thread_id)
  • Filter: (has_contributed AND (organization_type = 'OPERATOR'::text) AND (organization_id = '8bc90562-4eeb-4c7b-8207-ad38c5e43331'::text))
  • Rows Removed by Filter: 3
11. 95.912 95.912 ↑ 1.0 1 631

Index Scan using pk_thread_id on thread (cost=0.42..0.62 rows=1 width=24) (actual time=0.144..0.152 rows=1 loops=631)

  • Index Cond: (id = thread_entity.thread_id)
  • Filter: (NOT anonymized)
Planning time : 6.262 ms
Execution time : 7,523.460 ms