explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5Wz

Settings
# exclusive inclusive rows x rows loops node
1. 0.091 41.251 ↑ 1.0 100 1

Limit (cost=10,042.36..20,083.30 rows=100 width=1,237) (actual time=22.755..41.251 rows=100 loops=1)

2. 1.008 41.160 ↑ 102.7 200 1

Nested Loop Left Join (cost=1.42..2,062,511.24 rows=20,541 width=1,237) (actual time=3.918..41.160 rows=200 loops=1)

3. 0.807 36.952 ↑ 102.7 200 1

Nested Loop Left Join (cost=1.00..1,484,772.68 rows=20,541 width=869) (actual time=3.840..36.952 rows=200 loops=1)

  • Join Filter: (animal.reason_of_death = "deathReason".id)
  • Rows Removed by Join Filter: 2800
4. 0.349 35.545 ↑ 102.7 200 1

Nested Loop Left Join (cost=1.00..1,480,457.90 rows=20,541 width=857) (actual time=3.818..35.545 rows=200 loops=1)

  • Join Filter: (research_unit.species = species.id)
  • Rows Removed by Join Filter: 398
5. 0.423 34.996 ↑ 102.7 200 1

Nested Loop Left Join (cost=1.00..1,479,532.51 rows=20,541 width=852) (actual time=3.811..34.996 rows=200 loops=1)

  • Join Filter: (animal.source_type = "sourceType".id)
  • Rows Removed by Join Filter: 1198
6. 0.556 34.173 ↑ 102.7 200 1

Nested Loop Left Join (cost=1.00..1,477,374.62 rows=20,541 width=832) (actual time=3.802..34.173 rows=200 loops=1)

  • Join Filter: (animal.reason_not_breedable = "nonBreedableReason".id)
  • Rows Removed by Join Filter: 997
7. 0.191 33.417 ↑ 102.7 200 1

Nested Loop Left Join (cost=1.00..1,475,524.86 rows=20,541 width=826) (actual time=3.793..33.417 rows=200 loops=1)

  • Join Filter: (animal.harvest = harvest.id)
  • Rows Removed by Join Filter: 199
8. 0.537 33.026 ↑ 102.7 200 1

Nested Loop Left Join (cost=1.00..1,474,907.60 rows=20,541 width=820) (actual time=3.786..33.026 rows=200 loops=1)

  • Join Filter: (animal.genotype_status = "genotypeStatus".id)
  • Rows Removed by Join Filter: 996
9. 0.267 32.289 ↑ 102.7 200 1

Nested Loop Left Join (cost=1.00..1,473,057.84 rows=20,541 width=809) (actual time=3.778..32.289 rows=200 loops=1)

  • Join Filter: (animal.gender = gender.id)
  • Rows Removed by Join Filter: 399
10. 0.535 31.822 ↑ 102.7 200 1

Nested Loop Left Join (cost=1.00..1,472,132.45 rows=20,541 width=802) (actual time=3.769..31.822 rows=200 loops=1)

  • Join Filter: (animal.disposition = disposition.id)
  • Rows Removed by Join Filter: 799
11. 0.202 31.087 ↑ 102.7 200 1

Nested Loop (cost=1.00..1,470,590.82 rows=20,541 width=790) (actual time=3.756..31.087 rows=200 loops=1)

12. 3.945 30.085 ↑ 102.7 200 1

Nested Loop Left Join (cost=0.56..1,460,057.63 rows=20,541 width=222) (actual time=3.744..30.085 rows=200 loops=1)

  • Filter: (lower(NULLIF(concat_ws('/'::text, color1.short_name, color2.short_name), ''::text)) = 'bl'::text)
  • Rows Removed by Filter: 2512
13. 6.770 26.140 ↑ 1,514.8 2,712 1

Nested Loop Left Join (cost=0.43..761,657.58 rows=4,108,165 width=211) (actual time=0.040..26.140 rows=2,712 loops=1)

  • Join Filter: (animal.color1_id = color1.id)
  • Rows Removed by Join Filter: 21202
14. 13.946 13.946 ↑ 1,514.8 2,712 1

Index Scan Backward using animal_unit_index on animal (cost=0.43..268,676.68 rows=4,108,165 width=200) (actual time=0.025..13.946 rows=2,712 loops=1)

  • Filter: (lower(((alive_status)::character varying)::text) = 'false'::text)
  • Rows Removed by Filter: 15170
15. 5.415 5.424 ↑ 1.0 8 2,712

Materialize (cost=0.00..1.12 rows=8 width=19) (actual time=0.000..0.002 rows=8 loops=2,712)

16. 0.009 0.009 ↑ 1.0 8 1

Seq Scan on color color1 (cost=0.00..1.08 rows=8 width=19) (actual time=0.007..0.009 rows=8 loops=1)

17. 0.000 0.000 ↓ 0.0 0 2,712

Index Scan using color_pkey on color color2 (cost=0.13..0.15 rows=1 width=19) (actual time=0.000..0.000 rows=0 loops=2,712)

  • Index Cond: (animal.color2_id = id)
18. 0.800 0.800 ↑ 1.0 1 200

Index Scan using research_unit_pkey on research_unit (cost=0.43..0.50 rows=1 width=576) (actual time=0.003..0.004 rows=1 loops=200)

  • Index Cond: (id = animal.unit_id)
19. 0.192 0.200 ↑ 1.0 5 200

Materialize (cost=0.00..1.07 rows=5 width=20) (actual time=0.000..0.001 rows=5 loops=200)

20. 0.008 0.008 ↑ 1.0 5 1

Seq Scan on disposition (cost=0.00..1.05 rows=5 width=20) (actual time=0.006..0.008 rows=5 loops=1)

21. 0.194 0.200 ↑ 1.0 3 200

Materialize (cost=0.00..1.04 rows=3 width=15) (actual time=0.000..0.001 rows=3 loops=200)

22. 0.006 0.006 ↑ 1.0 3 1

Seq Scan on gender (cost=0.00..1.03 rows=3 width=15) (actual time=0.003..0.006 rows=3 loops=1)

23. 0.197 0.200 ↑ 1.0 6 200

Materialize (cost=0.00..1.09 rows=6 width=19) (actual time=0.000..0.001 rows=6 loops=200)

24. 0.003 0.003 ↑ 1.0 6 1

Seq Scan on genotype_status "genotypeStatus" (cost=0.00..1.06 rows=6 width=19) (actual time=0.002..0.003 rows=6 loops=1)

25. 0.195 0.200 ↑ 1.0 2 200

Materialize (cost=0.00..1.03 rows=2 width=14) (actual time=0.000..0.001 rows=2 loops=200)

26. 0.005 0.005 ↑ 1.0 2 1

Seq Scan on harvest (cost=0.00..1.02 rows=2 width=14) (actual time=0.003..0.005 rows=2 loops=1)

27. 0.196 0.200 ↑ 1.0 6 200

Materialize (cost=0.00..1.09 rows=6 width=14) (actual time=0.000..0.001 rows=6 loops=200)

28. 0.004 0.004 ↑ 1.0 6 1

Seq Scan on reason_not_breedable "nonBreedableReason" (cost=0.00..1.06 rows=6 width=14) (actual time=0.003..0.004 rows=6 loops=1)

29. 0.394 0.400 ↑ 1.0 7 200

Materialize (cost=0.00..1.10 rows=7 width=28) (actual time=0.000..0.002 rows=7 loops=200)

30. 0.006 0.006 ↑ 1.0 7 1

Seq Scan on source_type "sourceType" (cost=0.00..1.07 rows=7 width=28) (actual time=0.003..0.006 rows=7 loops=1)

31. 0.196 0.200 ↑ 1.0 3 200

Materialize (cost=0.00..1.04 rows=3 width=13) (actual time=0.000..0.001 rows=3 loops=200)

32. 0.004 0.004 ↑ 1.0 3 1

Seq Scan on species (cost=0.00..1.03 rows=3 width=13) (actual time=0.003..0.004 rows=3 loops=1)

33. 0.582 0.600 ↑ 1.0 14 200

Materialize (cost=0.00..1.21 rows=14 width=20) (actual time=0.000..0.003 rows=14 loops=200)

34. 0.018 0.018 ↑ 1.0 14 1

Seq Scan on death_reason "deathReason" (cost=0.00..1.14 rows=14 width=20) (actual time=0.004..0.018 rows=14 loops=1)

35. 0.000 0.000 ↓ 0.0 0 200

Index Scan using ff_animal_location_occupation on ff_animal_location location (cost=0.42..0.44 rows=1 width=72) (actual time=0.000..0.000 rows=0 loops=200)

  • Index Cond: (animal.cage = id)
36.          

SubPlan (forNested Loop Left Join)

37. 0.200 1.400 ↓ 0.0 0 200

Limit (cost=9.59..9.60 rows=1 width=16) (actual time=0.007..0.007 rows=0 loops=200)

38. 0.400 1.200 ↓ 0.0 0 200

Sort (cost=9.59..9.60 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=200)

  • Sort Key: m.id
  • Sort Method: quicksort Memory: 25kB
39. 0.182 0.800 ↓ 0.0 0 200

Nested Loop (cost=0.43..9.58 rows=1 width=16) (actual time=0.003..0.004 rows=0 loops=200)

  • Join Filter: (m.type_id = t.id)
  • Rows Removed by Join Filter: 0
40. 0.600 0.600 ↓ 0.0 0 200

Index Scan using animal_marker_animal_id_type_id_key on animal_marker m (cost=0.43..8.45 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=200)

  • Index Cond: (animal_id = animal.id)
41. 0.018 0.018 ↑ 1.0 6 6

Seq Scan on marker_type t (cost=0.00..1.06 rows=6 width=16) (actual time=0.001..0.003 rows=6 loops=6)

42. 0.200 1.000 ↓ 0.0 0 200

Limit (cost=9.59..9.60 rows=1 width=10) (actual time=0.005..0.005 rows=0 loops=200)

43. 0.400 0.800 ↓ 0.0 0 200

Sort (cost=9.59..9.60 rows=1 width=10) (actual time=0.004..0.004 rows=0 loops=200)

  • Sort Key: m_1.id
  • Sort Method: quicksort Memory: 25kB
44. 0.000 0.400 ↓ 0.0 0 200

Nested Loop (cost=0.43..9.58 rows=1 width=10) (actual time=0.002..0.002 rows=0 loops=200)

  • Join Filter: (m_1.type_id = t_1.id)
  • Rows Removed by Join Filter: 0
45. 0.400 0.400 ↓ 0.0 0 200

Index Scan using animal_marker_animal_id_type_id_key on animal_marker m_1 (cost=0.43..8.45 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=200)

  • Index Cond: (animal_id = animal.id)
46. 0.012 0.012 ↑ 1.0 6 6

Seq Scan on marker_type t_1 (cost=0.00..1.06 rows=6 width=10) (actual time=0.001..0.002 rows=6 loops=6)

47. 0.200 0.800 ↓ 0.0 0 200

Limit (cost=8.46..8.46 rows=1 width=10) (actual time=0.004..0.004 rows=0 loops=200)

48. 0.200 0.600 ↓ 0.0 0 200

Sort (cost=8.46..8.46 rows=1 width=10) (actual time=0.003..0.003 rows=0 loops=200)

  • Sort Key: m_2.id
  • Sort Method: quicksort Memory: 25kB
49. 0.400 0.400 ↓ 0.0 0 200

Index Scan using animal_marker_animal_id_type_id_key on animal_marker m_2 (cost=0.43..8.45 rows=1 width=10) (actual time=0.002..0.002 rows=0 loops=200)

  • Index Cond: (animal_id = animal.id)