explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CKg9

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 20,185.234 ↓ 0.0 0 1

Limit (cost=39,657.31..40,163.67 rows=51 width=24) (actual time=20,185.234..20,185.234 rows=0 loops=1)

2. 974.841 20,185.219 ↓ 0.0 0 1

Nested Loop (cost=39,657.31..504,325.91 rows=46,801 width=24) (actual time=20,185.219..20,185.219 rows=0 loops=1)

  • Join Filter: ((thread.id = thread_entity.thread_id) AND (((thread.topic_type = 'FREE_TEXT'::text) AND (thread.topic_value ~~* '%test%'::text)) OR (thread_entity.entity_id ~~* '%test%'::text) OR (hashed SubPlan 1)))
  • Rows Removed by Join Filter: 39,132
3. 6,417.294 17,199.010 ↑ 2.4 39,132 1

Nested Loop (cost=0.85..401,481.07 rows=93,591 width=82) (actual time=0.188..17,199.010 rows=39,132 loops=1)

4. 3,565.318 3,565.318 ↑ 1.0 400,911 1

Index Scan Backward using toto4 on thread (cost=0.42..75,505.02 rows=400,911 width=66) (actual time=0.037..3,565.318 rows=400,911 loops=1)

  • Filter: (NOT anonymized)
5. 7,216.398 7,216.398 ↓ 0.0 0 400,911

Index Scan using idx_thread_participant_thread_id on thread_participant_organization (cost=0.43..0.80 rows=1 width=16) (actual time=0.017..0.018 rows=0 loops=400,911)

  • Index Cond: (thread_id = thread.id)
  • Filter: (has_contributed AND (organization_type = 'OPERATOR'::text) AND (organization_id = '8bc90562-4eeb-4c7b-8207-ad38c5e43331'::text))
  • Rows Removed by Filter: 3
6. 900.036 900.036 ↑ 1.0 1 39,132

Index Scan using idx_thread_entity_thread_id on thread_entity (cost=0.42..0.65 rows=1 width=28) (actual time=0.016..0.023 rows=1 loops=39,132)

  • Index Cond: (thread_id = thread_participant_organization.thread_id)
  • Filter: (entity_type = ANY ('{MMP_ORDER,MMP_OFFER,MPS_ORDER}'::text[]))
7.          

SubPlan (for Nested Loop)

8. 1,111.332 1,111.332 ↓ 0.0 0 1

Seq Scan on thread_participant_organization sub_thread_participant (cost=0.00..39,655.99 rows=16 width=16) (actual time=1,111.332..1,111.332 rows=0 loops=1)

  • Filter: ((organization_id ~~* '%test%'::text) AND (organization_type = 'SHOP'::text))
  • Rows Removed by Filter: 1,202,733
Planning time : 1.652 ms
Execution time : 20,185.412 ms