explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I70

Settings
# exclusive inclusive rows x rows loops node
1. 0.099 2,478.141 ↑ 1.0 100 1

Limit (cost=19,378.43..22,146.93 rows=100 width=1,237) (actual time=2,476.409..2,478.141 rows=100 loops=1)

2. 0.574 2,478.042 ↑ 3.0 200 1

Result (cost=16,609.93..33,110.19 rows=596 width=1,237) (actual time=2,474.847..2,478.042 rows=200 loops=1)

3. 85.617 2,474.868 ↑ 3.0 200 1

Sort (cost=16,609.93..16,611.42 rows=596 width=1,049) (actual time=2,474.768..2,474.868 rows=200 loops=1)

  • Sort Key: animal.unit_id DESC
  • Sort Method: top-N heapsort Memory: 128kB
4. 183.123 2,389.251 ↓ 84.3 50,233 1

Nested Loop Left Join (cost=3.65..16,584.18 rows=596 width=1,049) (actual time=0.352..2,389.251 rows=50,233 loops=1)

5. 196.258 2,055.429 ↓ 84.3 50,233 1

Nested Loop Left Join (cost=3.23..16,182.96 rows=596 width=869) (actual time=0.323..2,055.429 rows=50,233 loops=1)

  • Join Filter: (animal.reason_of_death = "deathReason".id)
  • Rows Removed by Join Filter: 703262
6. 75.222 1,708.472 ↓ 84.3 50,233 1

Nested Loop Left Join (cost=3.23..16,056.63 rows=596 width=857) (actual time=0.273..1,708.472 rows=50,233 loops=1)

  • Join Filter: (research_unit.species = species.id)
  • Rows Removed by Join Filter: 100466
7. 106.169 1,583.017 ↓ 84.3 50,233 1

Nested Loop Left Join (cost=3.23..16,028.77 rows=596 width=852) (actual time=0.263..1,583.017 rows=50,233 loops=1)

  • Join Filter: (animal.source_type = "sourceType".id)
  • Rows Removed by Join Filter: 301398
8. 132.369 1,376.382 ↓ 84.3 50,233 1

Nested Loop Left Join (cost=3.23..15,965.10 rows=596 width=832) (actual time=0.246..1,376.382 rows=50,233 loops=1)

  • Join Filter: (animal.reason_not_breedable = "nonBreedableReason".id)
  • Rows Removed by Join Filter: 294849
9. 44.464 1,193.780 ↓ 84.3 50,233 1

Nested Loop Left Join (cost=3.23..15,910.39 rows=596 width=826) (actual time=0.227..1,193.780 rows=50,233 loops=1)

  • Join Filter: (animal.harvest = harvest.id)
  • Rows Removed by Join Filter: 50233
10. 133.000 1,099.083 ↓ 84.3 50,233 1

Nested Loop Left Join (cost=3.23..15,891.48 rows=596 width=820) (actual time=0.217..1,099.083 rows=50,233 loops=1)

  • Join Filter: (animal.genotype_status = "genotypeStatus".id)
  • Rows Removed by Join Filter: 251165
11. 65.807 915.850 ↓ 84.3 50,233 1

Nested Loop Left Join (cost=3.23..15,836.77 rows=596 width=809) (actual time=0.205..915.850 rows=50,233 loops=1)

  • Join Filter: (animal.gender = gender.id)
  • Rows Removed by Join Filter: 100466
12. 121.557 799.810 ↓ 84.3 50,233 1

Nested Loop Left Join (cost=3.23..15,808.91 rows=596 width=802) (actual time=0.196..799.810 rows=50,233 loops=1)

  • Join Filter: (animal.disposition = disposition.id)
  • Rows Removed by Join Filter: 200932
13. 77.514 628.020 ↓ 84.3 50,233 1

Nested Loop (cost=3.23..15,763.15 rows=596 width=790) (actual time=0.182..628.020 rows=50,233 loops=1)

14. 172.260 349.574 ↓ 84.3 50,233 1

Hash Left Join (cost=2.79..14,405.33 rows=596 width=222) (actual time=0.168..349.574 rows=50,233 loops=1)

  • Hash Cond: (animal.color2_id = color2.id)
  • Filter: (lower(NULLIF(concat_ws('/'::text, color1.short_name, color2.short_name), ''::text)) = 'bl'::text)
  • Rows Removed by Filter: 69356
15. 102.490 177.301 ↓ 1.0 119,589 1

Hash Left Join (cost=1.61..13,956.86 rows=119,273 width=211) (actual time=0.067..177.301 rows=119,589 loops=1)

  • Hash Cond: (animal.color1_id = color1.id)
16. 74.788 74.788 ↓ 1.0 119,589 1

Index Scan using idx_animal_alive_status_varchar_low on animal (cost=0.43..12,719.69 rows=119,273 width=200) (actual time=0.030..74.788 rows=119,589 loops=1)

  • Index Cond: (lower(((alive_status)::character varying)::text) = 'true'::text)
17. 0.008 0.023 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=19) (actual time=0.023..0.023 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.015 0.015 ↑ 1.0 8 1

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

19. 0.005 0.013 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=19) (actual time=0.013..0.013 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.008 0.008 ↑ 1.0 8 1

Seq Scan on color color2 (cost=0.00..1.08 rows=8 width=19) (actual time=0.003..0.008 rows=8 loops=1)

21. 200.932 200.932 ↑ 1.0 1 50,233

Index Scan using research_unit_pkey on research_unit (cost=0.43..2.27 rows=1 width=576) (actual time=0.004..0.004 rows=1 loops=50,233)

  • Index Cond: (id = animal.unit_id)
22. 50.224 50.233 ↑ 1.0 5 50,233

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

23. 0.009 0.009 ↑ 1.0 5 1

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

24. 50.226 50.233 ↑ 1.0 3 50,233

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

25. 0.007 0.007 ↑ 1.0 3 1

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

26. 50.223 50.233 ↑ 1.0 6 50,233

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

27. 0.010 0.010 ↑ 1.0 6 1

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

28. 50.229 50.233 ↑ 1.0 2 50,233

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

29. 0.004 0.004 ↑ 1.0 2 1

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

30. 50.225 50.233 ↑ 1.0 6 50,233

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

31. 0.008 0.008 ↑ 1.0 6 1

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

32. 100.456 100.466 ↑ 1.0 7 50,233

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

33. 0.010 0.010 ↑ 1.0 7 1

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

34. 50.227 50.233 ↑ 1.0 3 50,233

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

35. 0.006 0.006 ↑ 1.0 3 1

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

36. 150.688 150.699 ↑ 1.0 14 50,233

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

37. 0.011 0.011 ↑ 1.0 14 1

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

38. 150.699 150.699 ↑ 1.0 1 50,233

Index Scan using ff_animal_location_occupation on ff_animal_location location (cost=0.42..0.65 rows=1 width=72) (actual time=0.003..0.003 rows=1 loops=50,233)

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

SubPlan (forResult)

40. 0.200 1.000 ↓ 0.0 0 200

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

41. 0.200 0.800 ↓ 0.0 0 200

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

  • Sort Key: m.id
  • Sort Method: quicksort Memory: 25kB
42. 0.172 0.600 ↓ 0.0 0 200

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

  • Join Filter: (m.type_id = t.id)
  • Rows Removed by Join Filter: 0
43. 0.400 0.400 ↓ 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.002..0.002 rows=0 loops=200)

  • Index Cond: (animal_id = animal.id)
44. 0.028 0.028 ↑ 1.0 6 7

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

45. 0.000 0.800 ↓ 0.0 0 200

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

46. 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
47. 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
48. 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)
49. 0.014 0.014 ↑ 1.0 6 7

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

50. 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)

51. 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
52. 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)