explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J5he

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 1.585 ↑ 34.7 6 1

Nested Loop Left Join (cost=2,049.70..14,397.73 rows=208 width=310) (actual time=1.493..1.585 rows=6 loops=1)

  • Output: caseentity0_.id, categoryen1_.name, (SubPlan 1), (SubPlan 2), countryent2_.name, stateentit3_.name, caseentity0_.name
  • Inner Unique: true
  • Buffers: shared hit=102
2. 1.293 1.474 ↑ 34.7 6 1

Hash Left Join (cost=2,049.55..2,384.26 rows=208 width=246) (actual time=1.440..1.474 rows=6 loops=1)

  • Output: caseentity0_.id, caseentity0_.name, caseentity0_.state_id, categoryen1_.name, countryent2_.name
  • Inner Unique: true
  • Hash Cond: (caseentity0_.country_id = countryent2_.id)
  • Buffers: shared hit=65
3. 0.004 0.096 ↑ 34.7 6 1

Nested Loop Left Join (cost=2,040.91..2,375.06 rows=208 width=227) (actual time=0.064..0.096 rows=6 loops=1)

  • Output: caseentity0_.id, caseentity0_.name, caseentity0_.country_id, caseentity0_.state_id, categoryen1_.name
  • Inner Unique: true
  • Buffers: shared hit=62
4. 0.007 0.086 ↑ 34.7 6 1

Nested Loop (cost=2,040.76..2,340.03 rows=208 width=199) (actual time=0.060..0.086 rows=6 loops=1)

  • Output: caseentity0_.id, caseentity0_.name, caseentity0_.category_id, caseentity0_.country_id, caseentity0_.state_id
  • Inner Unique: true
  • Buffers: shared hit=50
5. 0.005 0.055 ↑ 34.7 6 1

HashAggregate (cost=2,040.34..2,042.42 rows=208 width=4) (actual time=0.053..0.055 rows=6 loops=1)

  • Output: participan8_.case_id
  • Group Key: participan8_.case_id
  • Buffers: shared hit=26
6. 0.002 0.050 ↑ 34.7 6 1

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

  • Output: participan8_.case_id
  • Buffers: shared hit=26
7. 0.009 0.024 ↑ 34.7 6 1

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

  • 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
  • Recheck Cond: (fullname_vector(personenti9_.lastname, personenti9_.firstname, personenti9_.patrname) @@ '''иван'''::tsquery)
  • Heap Blocks: exact=4
  • Buffers: shared hit=8
8. 0.015 0.015 ↑ 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.015..0.015 rows=6 loops=1)

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

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

  • Output: participan8_.id, participan8_.case_id, participan8_.person_id, participan8_.participant_type_id, participan8_.participant_status_id, participan8_.status_type_id
  • Index Cond: (participan8_.person_id = personenti9_.id)
  • Buffers: shared hit=18
10. 0.024 0.024 ↑ 1.0 1 6

Index Scan using case_pkey on safekids."case" caseentity0_ (cost=0.41..1.44 rows=1 width=199) (actual time=0.004..0.004 rows=1 loops=6)

  • 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
  • Index Cond: (caseentity0_.id = participan8_.case_id)
  • Filter: (caseentity0_.delete_dt IS NULL)
  • Buffers: shared hit=24
11. 0.006 0.006 ↑ 1.0 1 6

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

  • Output: categoryen1_.id, categoryen1_.code, categoryen1_.name
  • Index Cond: (caseentity0_.category_id = categoryen1_.id)
  • Buffers: shared hit=12
12. 0.038 0.085 ↑ 1.0 251 1

Hash (cost=5.51..5.51 rows=251 width=27) (actual time=0.085..0.085 rows=251 loops=1)

  • Output: countryent2_.name, countryent2_.id
  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=3
13. 0.047 0.047 ↑ 1.0 251 1

Seq Scan on safekids.country countryent2_ (cost=0.00..5.51 rows=251 width=27) (actual time=0.017..0.047 rows=251 loops=1)

  • Output: countryent2_.name, countryent2_.id
  • Buffers: shared hit=3
14. 0.012 0.012 ↑ 1.0 1 6

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=6)

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

SubPlan (for Nested Loop Left Join)

16. 0.018 0.042 ↓ 0.0 0 6

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

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

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

  • Output: statusenti5_.id, statusenti5_.code, statusenti5_.name
  • Index Cond: ((statusenti5_.code)::text = 'opened'::text)
  • Buffers: shared hit=12
18. 0.000 0.006 ↓ 0.0 0 6

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

  • 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=6
19. 0.006 0.006 ↓ 0.0 0 6

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

  • Index Cond: (actualstat4_.case_id = caseentity0_.id)
  • Buffers: shared hit=6
20. 0.012 0.048 ↑ 1.0 1 6

Aggregate (cost=36.73..36.74 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=6)

  • Output: string_agg((convention7_.name)::text, ';'::text)
  • Buffers: shared hit=7
21. 0.013 0.036 ↓ 0.0 0 6

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

  • Output: convention7_.name
  • Hash Cond: (convention7_.id = caseconven6_.convention_id)
  • Buffers: shared hit=7
22. 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
23. 0.012 0.018 ↓ 0.0 0 6

Hash (cost=14.79..14.79 rows=10 width=4) (actual time=0.003..0.003 rows=0 loops=6)

  • Output: caseconven6_.convention_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=6
24. 0.000 0.006 ↓ 0.0 0 6

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

  • Output: caseconven6_.convention_id
  • Recheck Cond: (caseconven6_.case_id = caseentity0_.id)
  • Buffers: shared hit=6
25. 0.006 0.006 ↓ 0.0 0 6

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

  • Index Cond: (caseconven6_.case_id = caseentity0_.id)
  • Buffers: shared hit=6
Planning time : 1.130 ms
Execution time : 1.714 ms