explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aPvn

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 517.052 ↓ 5.0 20 1

Limit (cost=890.69..1,019.86 rows=4 width=1,092) (actual time=514.047..517.052 rows=20 loops=1)

2. 8.673 517.051 ↓ 5.0 20 1

WindowAgg (cost=890.69..1,019.86 rows=4 width=1,092) (actual time=514.046..517.051 rows=20 loops=1)

3. 8.596 505.084 ↓ 4,958.2 19,833 1

Nested Loop Left Join (cost=890.69..906.15 rows=4 width=1,092) (actual time=443.813..505.084 rows=19,833 loops=1)

4. 11.245 456.854 ↓ 4,954.2 19,817 1

Hash Right Join (cost=890.55..904.31 rows=4 width=1,092) (actual time=443.797..456.854 rows=19,817 loops=1)

  • Hash Cond: (crosstab_1.subject_id = subjects.id)
5. 78.908 78.908 ↓ 19.9 19,891 1

Function Scan on crosstab crosstab_1 (cost=0.00..10.00 rows=1,000 width=4) (actual time=77.086..78.908 rows=19,891 loops=1)

6. 5.688 366.701 ↓ 4,954.2 19,817 1

Hash (cost=890.49..890.49 rows=4 width=1,092) (actual time=366.701..366.701 rows=19,817 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2042kB
7. 7.000 361.013 ↓ 4,954.2 19,817 1

Hash Left Join (cost=877.60..890.49 rows=4 width=1,092) (actual time=327.689..361.013 rows=19,817 loops=1)

  • Hash Cond: (subjects.id = amis_subject_lists.subject_id)
  • Filter: (((amis_subject_lists.subject_type)::text = 'Amis::Animal'::text) OR (amis_subject_lists.subject_type IS NULL))
8. 12.165 353.991 ↓ 161.1 19,811 1

Hash Left Join (cost=875.65..888.08 rows=123 width=1,092) (actual time=327.661..353.991 rows=19,811 loops=1)

  • Hash Cond: (subjects.strain_id = amis_strains.id)
9. 3.658 341.770 ↓ 161.1 19,811 1

Hash Left Join (cost=870.91..881.68 rows=123 width=1,087) (actual time=327.586..341.770 rows=19,811 loops=1)

  • Hash Cond: (projects.id = pn.project_id)
10. 3.493 338.060 ↓ 161.1 19,811 1

Hash Left Join (cost=869.39..879.08 rows=123 width=575) (actual time=327.529..338.060 rows=19,811 loops=1)

  • Hash Cond: (amis_subject_projects.project_id = projects.id)
11. 7.019 334.533 ↓ 161.1 19,811 1

Hash Right Join (cost=867.99..875.98 rows=123 width=59) (actual time=327.483..334.533 rows=19,811 loops=1)

  • Hash Cond: (amis_subject_projects.subject_id = subjects.id)
  • Filter: ((amis_subject_projects.exit_at IS NULL) AND (((amis_subject_projects.subject_type)::text = 'Amis::Animal'::text) OR (amis_subject_projects.subject_type IS NULL)))
  • Rows Removed by Filter: 3
12. 0.056 0.056 ↑ 1.0 289 1

Seq Scan on amis_subject_projects (cost=0.00..6.89 rows=289 width=29) (actual time=0.012..0.056 rows=289 loops=1)

13. 4.750 327.458 ↓ 158.5 19,811 1

Hash (cost=866.42..866.42 rows=125 width=55) (actual time=327.458..327.458 rows=19,811 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1953kB
14. 4.833 322.708 ↓ 158.5 19,811 1

Merge Right Join (cost=860.96..866.42 rows=125 width=55) (actual time=288.308..322.708 rows=19,811 loops=1)

  • Merge Cond: (amis_locations.id = amis_subject_locations.location_id)
15. 0.046 0.046 ↑ 13.7 59 1

Index Scan using amis_locations_pkey on amis_locations (cost=0.28..68.91 rows=808 width=12) (actual time=0.012..0.046 rows=59 loops=1)

16. 33.312 317.829 ↓ 158.5 19,811 1

Sort (cost=859.20..859.51 rows=125 width=47) (actual time=288.272..317.829 rows=19,811 loops=1)

  • Sort Key: amis_subject_locations.location_id
  • Sort Method: quicksort Memory: 3342kB
17. 6.657 284.517 ↓ 158.5 19,811 1

Hash Left Join (cost=827.40..854.85 rows=125 width=47) (actual time=264.780..284.517 rows=19,811 loops=1)

  • Hash Cond: (subjects.id = amis_subject_locations.subject_id)
  • Filter: ((amis_subject_locations.exit_at IS NULL) AND (((amis_subject_locations.subject_type)::text = 'Amis::Animal'::text) OR (amis_subject_locations.subject_type IS NULL)))
  • Rows Removed by Filter: 123
18. 11.446 277.732 ↓ 100.4 19,889 1

Hash Right Join (cost=815.95..841.88 rows=198 width=43) (actual time=264.641..277.732 rows=19,889 loops=1)

  • Hash Cond: (crosstab.subject_id = subjects.id)
  • Filter: ((crosstab.subject_type = 'Amis::Animal'::text) OR (crosstab.subject_type IS NULL))
19. 205.419 205.419 ↓ 19.9 19,891 1

Function Scan on crosstab (cost=0.00..10.00 rows=1,000 width=36) (actual time=203.741..205.419 rows=19,891 loops=1)

20. 5.741 60.867 ↑ 1.0 19,889 1

Hash (cost=567.34..567.34 rows=19,889 width=43) (actual time=60.867..60.867 rows=19,889 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1956kB
21. 55.126 55.126 ↑ 1.0 19,889 1

Seq Scan on amis_animals subjects (cost=0.00..567.34 rows=19,889 width=43) (actual time=0.020..55.126 rows=19,889 loops=1)

  • Filter: (animal_type_id = 12)
  • Rows Removed by Filter: 538
22. 0.061 0.128 ↑ 1.0 331 1

Hash (cost=7.31..7.31 rows=331 width=29) (actual time=0.128..0.128 rows=331 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
23. 0.067 0.067 ↑ 1.0 331 1

Seq Scan on amis_subject_locations (cost=0.00..7.31 rows=331 width=29) (actual time=0.010..0.067 rows=331 loops=1)

24. 0.028 0.034 ↑ 1.0 18 1

Hash (cost=1.18..1.18 rows=18 width=520) (actual time=0.034..0.034 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.006 0.006 ↑ 1.0 18 1

Seq Scan on projects (cost=0.00..1.18 rows=18 width=520) (actual time=0.004..0.006 rows=18 loops=1)

26. 0.007 0.052 ↓ 2.0 18 1

Hash (cost=1.41..1.41 rows=9 width=520) (actual time=0.052..0.052 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.003 0.045 ↓ 2.0 18 1

Subquery Scan on pn (cost=1.22..1.41 rows=9 width=520) (actual time=0.039..0.045 rows=18 loops=1)

28. 0.035 0.042 ↓ 2.0 18 1

HashAggregate (cost=1.22..1.31 rows=9 width=520) (actual time=0.038..0.042 rows=18 loops=1)

  • Group Key: project_numbers.project_id, project_numbers.number
29. 0.007 0.007 ↓ 2.0 18 1

Seq Scan on project_numbers (cost=0.00..1.18 rows=9 width=520) (actual time=0.004..0.007 rows=18 loops=1)

  • Filter: managed_by_nagano
30. 0.030 0.056 ↑ 1.0 122 1

Hash (cost=3.22..3.22 rows=122 width=13) (actual time=0.056..0.056 rows=122 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
31. 0.026 0.026 ↑ 1.0 122 1

Seq Scan on amis_strains (cost=0.00..3.22 rows=122 width=13) (actual time=0.007..0.026 rows=122 loops=1)

32. 0.011 0.022 ↑ 1.0 42 1

Hash (cost=1.42..1.42 rows=42 width=524) (actual time=0.022..0.022 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
33. 0.011 0.011 ↑ 1.0 42 1

Seq Scan on amis_subject_lists (cost=0.00..1.42 rows=42 width=524) (actual time=0.005..0.011 rows=42 loops=1)

34. 39.634 39.634 ↓ 0.0 0 19,817

Index Scan using index_amis_subject_file_notes_on_subject_type_and_subject_id on amis_subject_file_notes (cost=0.14..0.45 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=19,817)

  • Index Cond: (subject_id = subjects.id)
35.          

SubPlan (forWindowAgg)

36. 0.000 1.620 ↓ 0.0 0 20

Limit (cost=4.62..13.51 rows=1 width=0) (actual time=0.081..0.081 rows=0 loops=20)

37. 0.040 1.620 ↓ 0.0 0 20

Nested Loop Anti Join (cost=4.62..13.51 rows=1 width=0) (actual time=0.081..0.081 rows=0 loops=20)

  • Join Filter: (amis_task_states.task_id = amis_tasks.id)
  • Rows Removed by Join Filter: 12
38. 0.070 1.440 ↓ 0.0 0 20

Nested Loop (cost=0.00..7.86 rows=1 width=4) (actual time=0.062..0.072 rows=0 loops=20)

  • Join Filter: (amis_task_items.task_id = amis_tasks.id)
  • Rows Removed by Join Filter: 18
39. 1.080 1.080 ↑ 1.0 1 20

Seq Scan on amis_task_items (cost=0.00..6.27 rows=1 width=4) (actual time=0.040..0.054 rows=1 loops=20)

  • Filter: (((resource_type)::text = 'Amis::Animal'::text) AND (resource_id = subjects.id))
  • Rows Removed by Filter: 217
40. 0.290 0.290 ↓ 13.0 13 29

Seq Scan on amis_tasks (cost=0.00..1.58 rows=1 width=4) (actual time=0.002..0.010 rows=13 loops=29)

  • Filter: ((type)::text = 'Amis::Tasks::Coupling'::text)
  • Rows Removed by Filter: 33
41. 0.025 0.140 ↓ 24.0 24 10

Materialize (cost=4.62..5.63 rows=1 width=4) (actual time=0.007..0.014 rows=24 loops=10)

42. 0.008 0.115 ↓ 27.0 27 1

Subquery Scan on amis_task_states (cost=4.62..5.63 rows=1 width=4) (actual time=0.064..0.115 rows=27 loops=1)

  • Filter: ((amis_task_states.state)::text = 'done'::text)
  • Rows Removed by Filter: 6
43. 0.040 0.107 ↑ 1.4 33 1

Unique (cost=4.62..5.05 rows=46 width=18) (actual time=0.063..0.107 rows=33 loops=1)

44. 0.046 0.067 ↑ 1.4 61 1

Sort (cost=4.62..4.84 rows=86 width=18) (actual time=0.063..0.067 rows=61 loops=1)

  • Sort Key: amis_task_states_1.task_id, amis_task_states_1.created_at DESC
  • Sort Method: quicksort Memory: 31kB
45. 0.021 0.021 ↑ 1.0 86 1

Seq Scan on amis_task_states amis_task_states_1 (cost=0.00..1.86 rows=86 width=18) (actual time=0.005..0.021 rows=86 loops=1)

46. 0.000 1.560 ↓ 0.0 0 20

Limit (cost=4.62..13.51 rows=1 width=0) (actual time=0.078..0.078 rows=0 loops=20)

47. 0.014 1.560 ↓ 0.0 0 20

Nested Loop Anti Join (cost=4.62..13.51 rows=1 width=0) (actual time=0.078..0.078 rows=0 loops=20)

  • Join Filter: (amis_task_states_2.task_id = amis_tasks_1.id)
  • Rows Removed by Join Filter: 2
48. 0.056 1.440 ↓ 0.0 0 20

Nested Loop (cost=0.00..7.86 rows=1 width=4) (actual time=0.072..0.072 rows=0 loops=20)

  • Join Filter: (amis_task_items_1.task_id = amis_tasks_1.id)
  • Rows Removed by Join Filter: 14
49. 1.160 1.160 ↑ 1.0 1 20

Seq Scan on amis_task_items amis_task_items_1 (cost=0.00..6.27 rows=1 width=4) (actual time=0.043..0.058 rows=1 loops=20)

  • Filter: (((resource_type)::text = 'Amis::Animal'::text) AND (resource_id = subjects.id))
  • Rows Removed by Filter: 212
50. 0.224 0.224 ↓ 10.0 10 28

Seq Scan on amis_tasks amis_tasks_1 (cost=0.00..1.58 rows=1 width=4) (actual time=0.002..0.008 rows=10 loops=28)

  • Filter: ((type)::text = 'Amis::Tasks::Euthanasia'::text)
  • Rows Removed by Filter: 35
51. 0.011 0.106 ↓ 35.0 35 1

Materialize (cost=4.62..5.63 rows=1 width=4) (actual time=0.056..0.106 rows=35 loops=1)

52. 0.010 0.095 ↓ 35.0 35 1

Subquery Scan on amis_task_states_2 (cost=4.62..5.63 rows=1 width=4) (actual time=0.056..0.095 rows=35 loops=1)

  • Filter: ((amis_task_states_2.state)::text = 'done'::text)
  • Rows Removed by Filter: 11
53. 0.024 0.085 ↑ 1.0 46 1

Unique (cost=4.62..5.05 rows=46 width=18) (actual time=0.055..0.085 rows=46 loops=1)

54. 0.041 0.061 ↑ 1.0 86 1

Sort (cost=4.62..4.84 rows=86 width=18) (actual time=0.054..0.061 rows=86 loops=1)

  • Sort Key: amis_task_states_3.task_id, amis_task_states_3.created_at DESC
  • Sort Method: quicksort Memory: 31kB
55. 0.020 0.020 ↑ 1.0 86 1

Seq Scan on amis_task_states amis_task_states_3 (cost=0.00..1.86 rows=86 width=18) (actual time=0.004..0.020 rows=86 loops=1)

56. 0.000 0.114 ↓ 0.0 0 19

Limit (cost=0.00..1.39 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=19)

57. 0.114 0.114 ↓ 0.0 0 19

Seq Scan on amis_subject_couplings (cost=0.00..1.39 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=19)

  • Filter: ((exit_at IS NULL) AND ((subject_type)::text = 'Amis::Animal'::text) AND (subject_id = subjects.id))
  • Rows Removed by Filter: 21
Planning time : 5.833 ms
Execution time : 519.060 ms