explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DX2k

Settings
# exclusive inclusive rows x rows loops node
1. 0.095 3,192.912 ↑ 39.6 13 1

Hash Left Join (cost=7,879.10..41,431.06 rows=515 width=310) (actual time=305.187..3,192.912 rows=13 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=4930, temp read=1307 written=1307
2. 0.018 3,192.196 ↑ 39.6 13 1

Hash Left Join (cost=7,849.98..11,742.41 rows=515 width=246) (actual time=305.080..3,192.196 rows=13 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=4876, temp read=1307 written=1307
3. 0.026 3,192.084 ↑ 39.6 13 1

Hash Left Join (cost=7,841.33..11,732.41 rows=515 width=227) (actual time=304.982..3,192.084 rows=13 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=4873, temp read=1307 written=1307
4. 3,097.875 3,192.036 ↑ 39.6 13 1

Hash Right Join (cost=7,812.20..11,701.92 rows=515 width=199) (actual time=304.952..3,192.036 rows=13 loops=1)

  • Output: caseentity0_.id, caseentity0_.name, caseentity0_.category_id, caseentity0_.country_id, caseentity0_.state_id
  • Inner Unique: true
  • Hash Cond: (participan8_.case_id = caseentity0_.id)
  • Filter: ((to_tsvector('russian'::regconfig, caseentity0_.name) @@ '''иван'''::tsquery) OR (name_and_full_vector((caseentity0_.name)::character varying, personenti9_.lastname, personenti9_.firstname, personenti9_.patrname) @@ '''иван'''::tsquery))
  • Rows Removed by Filter: 79329
  • Buffers: shared hit=4872, temp read=1307 written=1307
5. 17.761 61.748 ↑ 1.0 41,600 1

Hash Right Join (cost=2,187.00..3,741.00 rows=41,600 width=53) (actual time=12.023..61.748 rows=41,600 loops=1)

  • Output: participan8_.case_id, personenti9_.lastname, personenti9_.firstname, personenti9_.patrname
  • Hash Cond: (personenti9_.id = participan8_.person_id)
  • Buffers: shared hit=1817
6. 32.442 32.442 ↑ 1.0 41,600 1

Seq Scan on safekids.person personenti9_ (cost=0.00..982.00 rows=41,600 width=53) (actual time=0.350..32.442 rows=41,600 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
  • Buffers: shared hit=566
7. 6.135 11.545 ↑ 1.0 41,600 1

Hash (cost=1,667.00..1,667.00 rows=41,600 width=8) (actual time=11.545..11.545 rows=41,600 loops=1)

  • Output: participan8_.case_id, participan8_.person_id
  • Buckets: 65536 Batches: 1 Memory Usage: 2137kB
  • Buffers: shared hit=1251
8. 5.410 5.410 ↑ 1.0 41,600 1

Seq Scan on safekids.participant participan8_ (cost=0.00..1,667.00 rows=41,600 width=8) (actual time=0.838..5.410 rows=41,600 loops=1)

  • Output: participan8_.case_id, participan8_.person_id
  • Buffers: shared hit=1251
9. 20.486 32.413 ↑ 1.0 51,609 1

Hash (cost=3,568.09..3,568.09 rows=51,609 width=199) (actual time=32.413..32.413 rows=51,609 loops=1)

  • Output: caseentity0_.id, caseentity0_.name, caseentity0_.category_id, caseentity0_.country_id, caseentity0_.state_id
  • Buckets: 32768 Batches: 4 Memory Usage: 3172kB
  • Buffers: shared hit=3052, temp written=1019
10. 11.927 11.927 ↑ 1.0 51,609 1

Seq Scan on safekids."case" caseentity0_ (cost=0.00..3,568.09 rows=51,609 width=199) (actual time=0.011..11.927 rows=51,609 loops=1)

  • Output: caseentity0_.id, caseentity0_.name, caseentity0_.category_id, caseentity0_.country_id, caseentity0_.state_id
  • Filter: (caseentity0_.delete_dt IS NULL)
  • Buffers: shared hit=3052
11. 0.011 0.022 ↑ 283.3 3 1

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

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

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

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

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

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

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

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

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

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

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

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

SubPlan (for Hash Left Join)

18. 0.130 0.351 ↓ 0.0 0 13

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

  • Output: actualstat4_.actual_dt
  • Inner Unique: true
  • Join Filter: (actualstat4_.status_id = statusenti5_.id)
  • Buffers: shared hit=39
19. 0.156 0.156 ↑ 1.0 1 13

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

  • Output: statusenti5_.id, statusenti5_.code, statusenti5_.name
  • Index Cond: ((statusenti5_.code)::text = 'opened'::text)
  • Buffers: shared hit=26
20. 0.039 0.065 ↓ 0.0 0 13

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

  • 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=13
21. 0.026 0.026 ↓ 0.0 0 13

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

  • Index Cond: (actualstat4_.case_id = caseentity0_.id)
  • Buffers: shared hit=13
22. 0.078 0.260 ↑ 1.0 1 13

Aggregate (cost=36.73..36.74 rows=1 width=32) (actual time=0.020..0.020 rows=1 loops=13)

  • Output: string_agg((convention7_.name)::text, ';'::text)
  • Buffers: shared hit=14
23. 0.087 0.182 ↓ 0.0 0 13

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

  • Output: convention7_.name
  • Hash Cond: (convention7_.id = caseconven6_.convention_id)
  • Buffers: shared hit=14
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.065 0.091 ↓ 0.0 0 13

Hash (cost=14.79..14.79 rows=10 width=4) (actual time=0.007..0.007 rows=0 loops=13)

  • Output: caseconven6_.convention_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=13
26. 0.013 0.026 ↓ 0.0 0 13

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

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

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

  • Index Cond: (caseconven6_.case_id = caseentity0_.id)
  • Buffers: shared hit=13
Planning time : 1.185 ms
Execution time : 3,193.524 ms