explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pEyV

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 0.558 ↑ 42.4 11 1

Append (cost=80.90..30,193.17 rows=466 width=310) (actual time=0.174..0.558 rows=11 loops=1)

  • Buffers: shared hit=136
2. 0.013 0.250 ↑ 51.6 5 1

Hash Left Join (cost=80.90..15,788.45 rows=258 width=310) (actual time=0.174..0.250 rows=5 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=34
3. 0.003 0.144 ↑ 51.6 5 1

Hash Left Join (cost=51.77..900.77 rows=258 width=246) (actual time=0.122..0.144 rows=5 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=12
4. 0.020 0.063 ↑ 51.6 5 1

Hash Left Join (cost=43.12..891.44 rows=258 width=227) (actual time=0.042..0.063 rows=5 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=9
5. 0.009 0.022 ↑ 51.6 5 1

Bitmap Heap Scan on safekids."case" caseentity0_ (cost=14.00..861.64 rows=258 width=199) (actual time=0.017..0.022 rows=5 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
  • Recheck Cond: (to_tsvector('russian'::regconfig, caseentity0_.name) @@ '''иван'''::tsquery)
  • Filter: (caseentity0_.delete_dt IS NULL)
  • Heap Blocks: exact=4
  • Buffers: shared hit=8
6. 0.013 0.013 ↑ 51.6 5 1

Bitmap Index Scan on case_name_ts_vector_idx (cost=0.00..13.94 rows=258 width=0) (actual time=0.013..0.013 rows=5 loops=1)

  • Index Cond: (to_tsvector('russian'::regconfig, caseentity0_.name) @@ '''иван'''::tsquery)
  • Buffers: shared hit=4
7. 0.004 0.021 ↑ 283.3 3 1

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

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

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

  • Output: categoryen1_.name, categoryen1_.id
  • Buffers: shared hit=1
9. 0.035 0.078 ↑ 1.0 251 1

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

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

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

  • Output: countryent2_.name, countryent2_.id
  • Buffers: shared hit=3
11. 0.004 0.008 ↑ 121.4 7 1

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

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

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

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

SubPlan (for Hash Left Join)

14. 0.020 0.040 ↓ 0.0 0 5

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

  • Output: actualstat4_.actual_dt
  • Inner Unique: true
  • Join Filter: (actualstat4_.status_id = statusenti5_.id)
  • Buffers: shared hit=15
15. 0.015 0.015 ↑ 1.0 1 5

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

  • Output: statusenti5_.id, statusenti5_.code, statusenti5_.name
  • Index Cond: ((statusenti5_.code)::text = 'opened'::text)
  • Buffers: shared hit=10
16. 0.000 0.005 ↓ 0.0 0 5

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

  • 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=5
17. 0.005 0.005 ↓ 0.0 0 5

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

  • Index Cond: (actualstat4_.case_id = caseentity0_.id)
  • Buffers: shared hit=5
18. 0.010 0.045 ↑ 1.0 1 5

Aggregate (cost=36.73..36.74 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=5)

  • Output: string_agg((convention7_.name)::text, ';'::text)
  • Buffers: shared hit=6
19. 0.009 0.035 ↓ 0.0 0 5

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

  • Output: convention7_.name
  • Hash Cond: (convention7_.id = caseconven6_.convention_id)
  • Buffers: shared hit=6
20. 0.016 0.016 ↑ 850.0 1 1

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

  • Output: convention7_.id, convention7_.code, convention7_.name
  • Buffers: shared hit=1
21. 0.005 0.010 ↓ 0.0 0 5

Hash (cost=14.79..14.79 rows=10 width=4) (actual time=0.002..0.002 rows=0 loops=5)

  • Output: caseconven6_.convention_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=5
22. 0.000 0.005 ↓ 0.0 0 5

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

  • Output: caseconven6_.convention_id
  • Recheck Cond: (caseconven6_.case_id = caseentity0_.id)
  • Buffers: shared hit=5
23. 0.005 0.005 ↓ 0.0 0 5

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

  • Index Cond: (caseconven6_.case_id = caseentity0_.id)
  • Buffers: shared hit=5
24. 0.015 0.306 ↑ 34.7 6 1

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

  • Output: caseentity0__1.id, categoryen1__1.name, (SubPlan 3), (SubPlan 4), countryent2__1.name, stateentit3__1.name, caseentity0__1.name
  • Inner Unique: true
  • Buffers: shared hit=102
25. 0.003 0.177 ↑ 34.7 6 1

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

  • Output: caseentity0__1.id, caseentity0__1.name, caseentity0__1.state_id, categoryen1__1.name, countryent2__1.name
  • Inner Unique: true
  • Hash Cond: (caseentity0__1.country_id = countryent2__1.id)
  • Buffers: shared hit=65
26. 0.003 0.105 ↑ 34.7 6 1

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

  • Output: caseentity0__1.id, caseentity0__1.name, caseentity0__1.country_id, caseentity0__1.state_id, categoryen1__1.name
  • Inner Unique: true
  • Buffers: shared hit=62
27. 0.005 0.084 ↑ 34.7 6 1

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

  • Output: caseentity0__1.id, caseentity0__1.name, caseentity0__1.category_id, caseentity0__1.country_id, caseentity0__1.state_id
  • Inner Unique: true
  • Buffers: shared hit=50
28. 0.017 0.061 ↑ 34.7 6 1

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

  • Output: participan8_.case_id
  • Group Key: participan8_.case_id
  • Buffers: shared hit=26
29. 0.011 0.044 ↑ 34.7 6 1

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

  • Output: participan8_.case_id
  • Buffers: shared hit=26
30. 0.004 0.015 ↑ 34.7 6 1

Bitmap Heap Scan on safekids.person personenti9_ (cost=17.61..481.78 rows=208 width=4) (actual time=0.012..0.015 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
31. 0.011 0.011 ↑ 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.011..0.011 rows=6 loops=1)

  • Index Cond: (fullname_vector(personenti9_.lastname, personenti9_.firstname, personenti9_.patrname) @@ '''иван'''::tsquery)
  • Buffers: shared hit=4
32. 0.018 0.018 ↑ 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.003 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
33. 0.018 0.018 ↑ 1.0 1 6

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

  • Output: caseentity0__1.id, caseentity0__1.category_id, caseentity0__1.create_dt, caseentity0__1.steal_dt, caseentity0__1.state_id, caseentity0__1.direction_id, caseentity0__1.executor_id, caseentity0__1.country_id, caseentity0__1.central_authority_id, caseentity0__1.fabula, caseentity0__1.delete_dt, caseentity0__1.name
  • Index Cond: (caseentity0__1.id = participan8_.case_id)
  • Filter: (caseentity0__1.delete_dt IS NULL)
  • Buffers: shared hit=24
34. 0.018 0.018 ↑ 1.0 1 6

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

  • Output: categoryen1__1.id, categoryen1__1.code, categoryen1__1.name
  • Index Cond: (caseentity0__1.category_id = categoryen1__1.id)
  • Buffers: shared hit=12
35. 0.030 0.069 ↑ 1.0 251 1

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

  • Output: countryent2__1.name, countryent2__1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=3
36. 0.039 0.039 ↑ 1.0 251 1

Seq Scan on safekids.country countryent2__1 (cost=0.00..5.51 rows=251 width=27) (actual time=0.005..0.039 rows=251 loops=1)

  • Output: countryent2__1.name, countryent2__1.id
  • Buffers: shared hit=3
37. 0.048 0.048 ↑ 1.0 1 6

Index Scan using state_pkey on safekids.state stateentit3__1 (cost=0.15..0.17 rows=1 width=36) (actual time=0.008..0.008 rows=1 loops=6)

  • Output: stateentit3__1.id, stateentit3__1.code, stateentit3__1.name
  • Index Cond: (caseentity0__1.state_id = stateentit3__1.id)
  • Buffers: shared hit=12
38.          

SubPlan (for Nested Loop Left Join)

39. 0.012 0.030 ↓ 0.0 0 6

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

  • Output: actualstat4__1.actual_dt
  • Inner Unique: true
  • Join Filter: (actualstat4__1.status_id = statusenti5__1.id)
  • Buffers: shared hit=18
40. 0.012 0.012 ↑ 1.0 1 6

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

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

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

  • Output: actualstat4__1.id, actualstat4__1.case_id, actualstat4__1.status_id, actualstat4__1.grounds_decision_id, actualstat4__1.actual_dt, actualstat4__1.is_actual, actualstat4__1.reason
  • Recheck Cond: (actualstat4__1.case_id = caseentity0__1.id)
  • Filter: actualstat4__1.is_actual
  • Buffers: shared hit=6
42. 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__1.case_id = caseentity0__1.id)
  • Buffers: shared hit=6
43. 0.012 0.036 ↑ 1.0 1 6

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

  • Output: string_agg((convention7__1.name)::text, ';'::text)
  • Buffers: shared hit=7
44. 0.003 0.024 ↓ 0.0 0 6

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

  • Output: convention7__1.name
  • Hash Cond: (convention7__1.id = caseconven6__1.convention_id)
  • Buffers: shared hit=7
45. 0.003 0.003 ↑ 850.0 1 1

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

  • Output: convention7__1.id, convention7__1.code, convention7__1.name
  • Buffers: shared hit=1
46. 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__1.convention_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=6
47. 0.000 0.006 ↓ 0.0 0 6

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

  • Output: caseconven6__1.convention_id
  • Recheck Cond: (caseconven6__1.case_id = caseentity0__1.id)
  • Buffers: shared hit=6
48. 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__1.case_id = caseentity0__1.id)
  • Buffers: shared hit=6
Planning time : 1.738 ms
Execution time : 0.780 ms