explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HA1qh

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 18,801.577 ↓ 0.0 0 1

Limit (cost=18,828.89..19,335.25 rows=51 width=24) (actual time=18,801.577..18,801.577 rows=0 loops=1)

2. 918.147 18,801.563 ↓ 0.0 0 1

Nested Loop (cost=18,828.89..483,497.49 rows=46,801 width=24) (actual time=18,801.563..18,801.563 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,039.842 16,195.101 ↑ 2.4 39,132 1

Nested Loop (cost=0.85..401,481.07 rows=93,591 width=82) (actual time=0.264..16,195.101 rows=39,132 loops=1)

4. 3,339.772 3,339.772 ↑ 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.036..3,339.772 rows=400,911 loops=1)

  • Filter: (NOT anonymized)
5. 6,815.487 6,815.487 ↓ 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.016..0.017 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. 860.904 860.904 ↑ 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.015..0.022 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. 0.015 827.411 ↓ 0.0 0 1

Nested Loop (cost=0.43..18,827.54 rows=33 width=16) (actual time=827.411..827.411 rows=0 loops=1)

9. 827.396 827.396 ↓ 0.0 0 1

Seq Scan on organization (cost=0.00..14,493.16 rows=43 width=36) (actual time=827.396..827.396 rows=0 loops=1)

  • Filter: (display_name ~~* '%test%'::text)
  • Rows Removed by Filter: 526,413
10. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_thread_participant_participant_id_participant_type on thread_participant_organization sub_thread_participant (cost=0.43..100.54 rows=26 width=42) (never executed)

  • Index Cond: ((organization_id = organization.id) AND (organization_type = 'SHOP'::text))
Planning time : 2.175 ms
Execution time : 18,801.649 ms