explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GM5u

Settings
# exclusive inclusive rows x rows loops node
1. 0.070 957.499 ↑ 2,357.6 11 1

Hash Left Join (cost=2,168.02..1,512,605.97 rows=25,934 width=310) (actual time=36.330..957.499 rows=11 loops=1)

  • Output: caseentity0_.id, categoryen1_.name, (SubPlan 1), (SubPlan 2), countryent2_.name, stateentit3_.name, caseentity0_.name
  • Inner Unique: true
  • Hash Cond: (caseentity0_.state_id = stateentit3_.id)
  • Buffers: shared hit=3128
2. 0.629 957.013 ↑ 2,357.6 11 1

Hash Left Join (cost=2,138.89..19,003.78 rows=25,934 width=246) (actual time=36.262..957.013 rows=11 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=3082
3. 0.052 956.314 ↑ 2,357.6 11 1

Hash Left Join (cost=2,130.25..18,927.05 rows=25,934 width=227) (actual time=35.571..956.314 rows=11 loops=1)

  • Output: caseentity0_.id, caseentity0_.name, caseentity0_.country_id, caseentity0_.state_id, categoryen1_.name
  • Inner Unique: true
  • Hash Cond: (caseentity0_.category_id = categoryen1_.id)
  • Buffers: shared hit=3079
4. 956.195 956.243 ↑ 2,357.6 11 1

Seq Scan on safekids."case" caseentity0_ (cost=2,101.12..18,829.51 rows=25,934 width=199) (actual time=35.518..956.243 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 (hashed SubPlan 3)))
  • Rows Removed by Filter: 51598
  • Buffers: shared hit=3078
5.          

SubPlan (for Seq Scan)

6. 0.006 0.048 ↑ 34.7 6 1

Sort (cost=2,100.08..2,100.60 rows=208 width=8) (actual time=0.047..0.048 rows=6 loops=1)

  • Output: participan8_.case_id, personenti9_.id
  • Sort Key: personenti9_.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=26
7. 0.005 0.042 ↑ 34.7 6 1

Nested Loop (cost=18.15..2,092.07 rows=208 width=8) (actual time=0.027..0.042 rows=6 loops=1)

  • Output: participan8_.case_id, personenti9_.id
  • Buffers: shared hit=26
8. 0.007 0.025 ↑ 34.7 6 1

Bitmap Heap Scan on safekids.person personenti9_ (cost=17.86..534.03 rows=208 width=4) (actual time=0.021..0.025 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) @@ to_tsquery('иванов'::text))
  • Heap Blocks: exact=4
  • Buffers: shared hit=8
9. 0.018 0.018 ↑ 34.7 6 1

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

  • Index Cond: (fullname_vector(personenti9_.lastname, personenti9_.firstname, personenti9_.patrname) @@ to_tsquery('иванов'::text))
  • Buffers: shared hit=4
10. 0.012 0.012 ↑ 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.002..0.002 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
11. 0.004 0.019 ↑ 283.3 3 1

Hash (cost=18.50..18.50 rows=850 width=36) (actual time=0.019..0.019 rows=3 loops=1)

  • Output: categoryen1_.name, categoryen1_.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
12. 0.015 0.015 ↑ 283.3 3 1

Seq Scan on safekids.category categoryen1_ (cost=0.00..18.50 rows=850 width=36) (actual time=0.014..0.015 rows=3 loops=1)

  • Output: categoryen1_.name, categoryen1_.id
  • Buffers: shared hit=1
13. 0.032 0.070 ↑ 1.0 251 1

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

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

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

  • Output: countryent2_.name, countryent2_.id
  • Buffers: shared hit=3
15. 0.002 0.009 ↑ 121.4 7 1

Hash (cost=18.50..18.50 rows=850 width=36) (actual time=0.009..0.009 rows=7 loops=1)

  • Output: stateentit3_.name, stateentit3_.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
16. 0.007 0.007 ↑ 121.4 7 1

Seq Scan on safekids.state stateentit3_ (cost=0.00..18.50 rows=850 width=36) (actual time=0.006..0.007 rows=7 loops=1)

  • Output: stateentit3_.name, stateentit3_.id
  • Buffers: shared hit=1
17.          

SubPlan (for Hash Left Join)

18. 0.110 0.231 ↓ 0.0 0 11

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

  • Output: actualstat4_.actual_dt
  • Inner Unique: true
  • Join Filter: (actualstat4_.status_id = statusenti5_.id)
  • Buffers: shared hit=33
19. 0.088 0.088 ↑ 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.008..0.008 rows=1 loops=11)

  • Output: statusenti5_.id, statusenti5_.code, statusenti5_.name
  • Index Cond: ((statusenti5_.code)::text = 'opened'::text)
  • Buffers: shared hit=22
20. 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
21. 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
22. 0.066 0.176 ↑ 1.0 1 11

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

  • Output: string_agg((convention7_.name)::text, ';'::text)
  • Buffers: shared hit=12
23. 0.051 0.110 ↓ 0.0 0 11

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

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

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

  • Output: convention7_.id, convention7_.code, convention7_.name
  • Buffers: shared hit=1
25. 0.033 0.055 ↓ 0.0 0 11

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

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

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

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

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

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