explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M9x54

Settings
# exclusive inclusive rows x rows loops node
1. 0.051 948.385 ↑ 2,357.6 11 1

Nested Loop Left Join (cost=0.44..3,283,072.45 rows=25,934 width=310) (actual time=41.230..948.385 rows=11 loops=1)

  • Output: caseentity0_.id, categoryen1_.name, (SubPlan 1), (SubPlan 2), countryent2_.name, stateentit3_.name, caseentity0_.name
  • Inner Unique: true
  • Buffers: shared hit=3167
2. 0.036 947.828 ↑ 2,357.6 11 1

Nested Loop Left Join (cost=0.29..1,785,176.46 rows=25,934 width=246) (actual time=41.119..947.828 rows=11 loops=1)

  • Output: caseentity0_.id, caseentity0_.name, caseentity0_.state_id, categoryen1_.name, countryent2_.name
  • Inner Unique: true
  • Buffers: shared hit=3100
3. 0.037 947.792 ↑ 2,357.6 11 1

Nested Loop Left Join (cost=0.15..1,780,942.18 rows=25,934 width=227) (actual time=41.116..947.792 rows=11 loops=1)

  • Output: caseentity0_.id, caseentity0_.name, caseentity0_.country_id, caseentity0_.state_id, categoryen1_.name
  • Inner Unique: true
  • Buffers: shared hit=3100
4. 947.653 947.700 ↑ 2,357.6 11 1

Seq Scan on safekids."case" caseentity0_ (cost=0.00..1,776,550.84 rows=25,934 width=199) (actual time=41.102..947.700 rows=11 loops=1)

  • Output: caseentity0_.id, caseentity0_.category_id, caseentity0_.create_dt, caseentity0_.steal_dt, caseentity0_.state_id, caseentity0_.direction_id, caseentity0_.executor_id, caseentity0_.country_id, caseentity0_.central_authority_id, caseentity0_.fabula, caseentity0_.delete_dt, caseentity0_.name
  • Filter: (((caseentity0_.delete_dt IS NULL) AND (to_tsvector('russian'::regconfig, caseentity0_.name) @@ '''иван'''::tsquery)) OR (alternatives: SubPlan 3 or hashed SubPlan 4))
  • Rows Removed by Filter: 51598
  • Buffers: shared hit=3078
5.          

SubPlan (for Seq Scan)

6. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.58..34.10 rows=1 width=0) (never executed)

  • Inner Unique: true
7. 0.000 0.000 ↓ 0.0 0

Index Scan using participant_case_ix on safekids.participant participan8_ (cost=0.29..8.34 rows=3 width=4) (never executed)

  • Output: participan8_.id, participan8_.case_id, participan8_.person_id, participan8_.participant_type_id, participan8_.participant_status_id, participan8_.status_type_id
  • Index Cond: (caseentity0_.id = participan8_.case_id)
8. 0.000 0.000 ↓ 0.0 0

Index Scan using person_pkey on safekids.person personenti9_ (cost=0.29..8.56 rows=1 width=4) (never executed)

  • Output: personenti9_.id, personenti9_.gender_id, personenti9_.firstname, personenti9_.lastname, personenti9_.patrname, personenti9_.birth_dt, personenti9_.birth_year, personenti9_.citizenship_id, personenti9_.birthplace, personenti9_.address_reg, personenti9_.address_fact, personenti9_.employment_type_id, personenti9_.employment_place, personenti9_.phone, personenti9_.email, personenti9_.social_links, personenti9_.mother_initials, personenti9_.father_initials, personenti9_.employment_existence, personenti9_.employment_status, personenti9_.employment_dt, personenti9_.contacts
  • Index Cond: (personenti9_.id = participan8_.person_id)
  • Filter: (fullname_vector(personenti9_.lastname, personenti9_.firstname, personenti9_.patrname) @@ '''иван'''::tsquery)
9. 0.006 0.047 ↑ 34.7 6 1

Nested Loop (cost=17.90..2,039.82 rows=208 width=4) (actual time=0.027..0.047 rows=6 loops=1)

  • Output: participan8__1.case_id
  • Buffers: shared hit=26
10. 0.007 0.023 ↑ 34.7 6 1

Bitmap Heap Scan on safekids.person personenti9__1 (cost=17.61..481.78 rows=208 width=4) (actual time=0.019..0.023 rows=6 loops=1)

  • Output: personenti9__1.id, personenti9__1.gender_id, personenti9__1.firstname, personenti9__1.lastname, personenti9__1.patrname, personenti9__1.birth_dt, personenti9__1.birth_year, personenti9__1.citizenship_id, personenti9__1.birthplace, personenti9__1.address_reg, personenti9__1.address_fact, personenti9__1.employment_type_id, personenti9__1.employment_place, personenti9__1.phone, personenti9__1.email, personenti9__1.social_links, personenti9__1.mother_initials, personenti9__1.father_initials, personenti9__1.employment_existence, personenti9__1.employment_status, personenti9__1.employment_dt, personenti9__1.contacts
  • Recheck Cond: (fullname_vector(personenti9__1.lastname, personenti9__1.firstname, personenti9__1.patrname) @@ '''иван'''::tsquery)
  • Heap Blocks: exact=4
  • Buffers: shared hit=8
11. 0.016 0.016 ↑ 34.7 6 1

Bitmap Index Scan on person_func_name_trgm_idx (cost=0.00..17.56 rows=208 width=0) (actual time=0.016..0.016 rows=6 loops=1)

  • Index Cond: (fullname_vector(personenti9__1.lastname, personenti9__1.firstname, personenti9__1.patrname) @@ '''иван'''::tsquery)
  • Buffers: shared hit=4
12. 0.018 0.018 ↑ 1.0 1 6

Index Scan using participant_person_ix on safekids.participant participan8__1 (cost=0.29..7.48 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=6)

  • Output: participan8__1.id, participan8__1.case_id, participan8__1.person_id, participan8__1.participant_type_id, participan8__1.participant_status_id, participan8__1.status_type_id
  • Index Cond: (participan8__1.person_id = personenti9__1.id)
  • Buffers: shared hit=18
13. 0.055 0.055 ↑ 1.0 1 11

Index Scan using category_pkey on safekids.category categoryen1_ (cost=0.15..0.17 rows=1 width=36) (actual time=0.005..0.005 rows=1 loops=11)

  • Output: categoryen1_.id, categoryen1_.code, categoryen1_.name
  • Index Cond: (caseentity0_.category_id = categoryen1_.id)
  • Buffers: shared hit=22
14. 0.000 0.000 ↓ 0.0 0 11

Index Scan using country_pkey on safekids.country countryent2_ (cost=0.14..0.16 rows=1 width=27) (actual time=0.000..0.000 rows=0 loops=11)

  • Output: countryent2_.id, countryent2_.code, countryent2_.name, countryent2_.translit_name
  • Index Cond: (caseentity0_.country_id = countryent2_.id)
15. 0.022 0.022 ↑ 1.0 1 11

Index Scan using state_pkey on safekids.state stateentit3_ (cost=0.15..0.17 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=11)

  • Output: stateentit3_.id, stateentit3_.code, stateentit3_.name
  • Index Cond: (caseentity0_.state_id = stateentit3_.id)
  • Buffers: shared hit=22
16.          

SubPlan (for Nested Loop Left Join)

17. 0.099 0.209 ↓ 0.0 0 11

Nested Loop (cost=4.34..20.85 rows=1 width=4) (actual time=0.019..0.019 rows=0 loops=11)

  • Output: actualstat4_.actual_dt
  • Inner Unique: true
  • Join Filter: (actualstat4_.status_id = statusenti5_.id)
  • Buffers: shared hit=33
18. 0.077 0.077 ↑ 1.0 1 11

Index Scan using status_code_key on safekids.status statusenti5_ (cost=0.15..8.17 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=11)

  • Output: statusenti5_.id, statusenti5_.code, statusenti5_.name
  • Index Cond: ((statusenti5_.code)::text = 'opened'::text)
  • Buffers: shared hit=22
19. 0.011 0.033 ↓ 0.0 0 11

Bitmap Heap Scan on safekids.actual_status actualstat4_ (cost=4.19..12.66 rows=2 width=8) (actual time=0.003..0.003 rows=0 loops=11)

  • Output: actualstat4_.id, actualstat4_.case_id, actualstat4_.status_id, actualstat4_.grounds_decision_id, actualstat4_.actual_dt, actualstat4_.is_actual, actualstat4_.reason
  • Recheck Cond: (actualstat4_.case_id = caseentity0_.id)
  • Filter: actualstat4_.is_actual
  • Buffers: shared hit=11
20. 0.022 0.022 ↓ 0.0 0 11

Bitmap Index Scan on actual_status_case_status_uix (cost=0.00..4.19 rows=5 width=0) (actual time=0.002..0.002 rows=0 loops=11)

  • Index Cond: (actualstat4_.case_id = caseentity0_.id)
  • Buffers: shared hit=11
21. 0.077 0.275 ↑ 1.0 1 11

Aggregate (cost=36.73..36.74 rows=1 width=32) (actual time=0.025..0.025 rows=1 loops=11)

  • Output: string_agg((convention7_.name)::text, ';'::text)
  • Buffers: shared hit=12
22. 0.105 0.198 ↓ 0.0 0 11

Hash Join (cost=14.91..36.70 rows=10 width=32) (actual time=0.018..0.018 rows=0 loops=11)

  • Output: convention7_.name
  • Hash Cond: (convention7_.id = caseconven6_.convention_id)
  • Buffers: shared hit=12
23. 0.005 0.005 ↑ 850.0 1 1

Seq Scan on safekids.convention convention7_ (cost=0.00..18.50 rows=850 width=36) (actual time=0.005..0.005 rows=1 loops=1)

  • Output: convention7_.id, convention7_.code, convention7_.name
  • Buffers: shared hit=1
24. 0.044 0.088 ↓ 0.0 0 11

Hash (cost=14.79..14.79 rows=10 width=4) (actual time=0.008..0.008 rows=0 loops=11)

  • Output: caseconven6_.convention_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=11
25. 0.011 0.044 ↓ 0.0 0 11

Bitmap Heap Scan on safekids.case_convention caseconven6_ (cost=4.23..14.79 rows=10 width=4) (actual time=0.004..0.004 rows=0 loops=11)

  • Output: caseconven6_.convention_id
  • Recheck Cond: (caseconven6_.case_id = caseentity0_.id)
  • Buffers: shared hit=11
26. 0.033 0.033 ↓ 0.0 0 11

Bitmap Index Scan on case_convention_case_ix (cost=0.00..4.23 rows=10 width=0) (actual time=0.003..0.003 rows=0 loops=11)

  • Index Cond: (caseconven6_.case_id = caseentity0_.id)
  • Buffers: shared hit=11
Planning time : 1.097 ms
Execution time : 948.523 ms