explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hScG

Settings
# exclusive inclusive rows x rows loops node
1. 0.230 140,109.100 ↓ 1,568.0 3,136 1

Limit (cost=9,559,553.63..9,559,610.31 rows=2 width=127) (actual time=140,107.647..140,109.100 rows=3,136 loops=1)

2. 0.907 140,108.870 ↓ 1,568.0 3,136 1

Group (cost=9,559,553.63..9,559,610.29 rows=2 width=135) (actual time=140,107.646..140,108.870 rows=3,136 loops=1)

  • Group Key: bdd43e1440a00047b1e66cb9619865c0.name, df8d81aecbe6b10702cbd762ac04cac9.title, df8d81aecbe6b10702cbd762ac04cac9.author, "7d74f3b92b19da5e606d737d339a9679".barcode, "286d7899ae25bee8bdd7dcde49f9e0b7".label, c17ecc44e3cd2de457e5c9f1e6e0a26f.name, (date("7d74f3b92b19da5e606d737d339a9679".create_date)), "9547fadabf916ce4fd3eb232e0902c9c".name, (date((max("*SELECT* 1".xact_start)))), (((COALESCE((SubPlan 1), 0) + (SubPlan 2)) + (SubPlan 3)))
3. 5.746 140,107.963 ↓ 1,568.0 3,136 1

Sort (cost=9,559,553.63..9,559,553.64 rows=2 width=135) (actual time=140,107.644..140,107.963 rows=3,136 loops=1)

  • Sort Key: bdd43e1440a00047b1e66cb9619865c0.name, df8d81aecbe6b10702cbd762ac04cac9.title, df8d81aecbe6b10702cbd762ac04cac9.author, "7d74f3b92b19da5e606d737d339a9679".barcode, "286d7899ae25bee8bdd7dcde49f9e0b7".label, c17ecc44e3cd2de457e5c9f1e6e0a26f.name, (date("7d74f3b92b19da5e606d737d339a9679".create_date)), "9547fadabf916ce4fd3eb232e0902c9c".name, (date((max("*SELECT* 1".xact_start)))), (((COALESCE((SubPlan 1), 0) + (SubPlan 2)) + (SubPlan 3)))
  • Sort Method: quicksort Memory: 920kB
4. 9.510 140,102.217 ↓ 1,568.0 3,136 1

Nested Loop (cost=9,387,614.97..9,559,553.62 rows=2 width=135) (actual time=140,091.959..140,102.217 rows=3,136 loops=1)

  • Join Filter: ("7d74f3b92b19da5e606d737d339a9679".status = "9547fadabf916ce4fd3eb232e0902c9c".id)
  • Rows Removed by Join Filter: 68992
5. 0.006 0.006 ↑ 1.0 23 1

Seq Scan on copy_status "9547fadabf916ce4fd3eb232e0902c9c" (cost=0.00..1.23 rows=23 width=15) (actual time=0.002..0.006 rows=23 loops=1)

6. 6.655 140,092.701 ↓ 1,568.0 3,136 23

Materialize (cost=9,387,614.97..9,559,495.11 rows=2 width=128) (actual time=5,900.977..6,090.987 rows=3,136 loops=23)

7. 0.821 140,086.046 ↓ 1,568.0 3,136 1

Nested Loop (cost=9,387,614.97..9,559,495.10 rows=2 width=128) (actual time=135,722.460..140,086.046 rows=3,136 loops=1)

8. 0.009 0.009 ↑ 1.0 1 1

Index Scan using copy_location_pkey on copy_location c17ecc44e3cd2de457e5c9f1e6e0a26f (cost=0.28..4.30 rows=1 width=20) (actual time=0.006..0.009 rows=1 loops=1)

  • Index Cond: (id = 368)
9. 994.447 140,085.216 ↓ 1,568.0 3,136 1

Nested Loop Left Join (cost=9,387,614.69..9,559,490.78 rows=2 width=116) (actual time=135,722.451..140,085.216 rows=3,136 loops=1)

  • Join Filter: ("7d74f3b92b19da5e606d737d339a9679".id = ac.id)
  • Rows Removed by Join Filter: 12486738
  • Filter: ((date((max("*SELECT* 1".xact_start))) IS NULL) OR (date((max("*SELECT* 1".xact_start))) < '2019-05-20'::date))
  • Rows Removed by Filter: 26
10. 1.608 1,559.579 ↓ 3,162.0 3,162 1

Nested Loop Left Join (cost=1.85..171,842.15 rows=1 width=116) (actual time=0.922..1,559.579 rows=3,162 loops=1)

11. 3.661 1,516.865 ↓ 3,162.0 3,162 1

Nested Loop Left Join (cost=1.42..171,841.59 rows=1 width=88) (actual time=0.906..1,516.865 rows=3,162 loops=1)

12. 2.392 1,484.746 ↓ 3,162.0 3,162 1

Nested Loop Left Join (cost=0.99..171,841.13 rows=1 width=88) (actual time=0.897..1,484.746 rows=3,162 loops=1)

13. 1.429 1,191.450 ↓ 3,162.0 3,162 1

Nested Loop (cost=0.56..171,838.27 rows=1 width=80) (actual time=0.724..1,191.450 rows=3,162 loops=1)

  • Join Filter: ("286d7899ae25bee8bdd7dcde49f9e0b7".owning_lib = bdd43e1440a00047b1e66cb9619865c0.id)
14. 0.030 0.030 ↑ 1.0 1 1

Seq Scan on org_unit bdd43e1440a00047b1e66cb9619865c0 (cost=0.00..6.38 rows=1 width=31) (actual time=0.010..0.030 rows=1 loops=1)

  • Filter: ((id IS NULL) OR (id = 117))
  • Rows Removed by Filter: 189
15. 4.342 1,189.991 ↓ 13.4 3,162 1

Nested Loop (cost=0.56..171,828.95 rows=236 width=57) (actual time=0.711..1,189.991 rows=3,162 loops=1)

16. 0.753 1,154.029 ↓ 13.4 3,162 1

Append (cost=0.00..170,745.12 rows=236 width=45) (actual time=0.696..1,154.029 rows=3,162 loops=1)

17. 1,153.275 1,153.275 ↓ 13.5 3,162 1

Seq Scan on copy "7d74f3b92b19da5e606d737d339a9679" (cost=0.00..170,731.92 rows=235 width=45) (actual time=0.696..1,153.275 rows=3,162 loops=1)

  • Filter: ((NOT deleted) AND (location = 368) AND (age(now(), create_date) >= '1 day'::interval))
  • Rows Removed by Filter: 3993064
18. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on unit "7d74f3b92b19da5e606d737d339a9679_1" (cost=0.00..13.20 rows=1 width=64) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((NOT deleted) AND (location = 368) AND (age(now(), create_date) >= '1 day'::interval))
19. 31.620 31.620 ↑ 1.0 1 3,162

Index Scan using call_number_pkey on call_number "286d7899ae25bee8bdd7dcde49f9e0b7" (cost=0.56..4.58 rows=1 width=28) (actual time=0.010..0.010 rows=1 loops=3,162)

  • Index Cond: (id = "7d74f3b92b19da5e606d737d339a9679".call_number)
20. 6.324 290.904 ↑ 2.0 1 3,162

Append (cost=0.43..2.83 rows=2 width=16) (actual time=0.089..0.092 rows=1 loops=3,162)

21. 25.296 281.418 ↑ 1.0 1 3,162

Index Only Scan using copy_pkey on copy cp (cost=0.43..2.57 rows=1 width=16) (actual time=0.089..0.089 rows=1 loops=3,162)

  • Index Cond: (id = "7d74f3b92b19da5e606d737d339a9679".id)
  • Heap Fetches: 397
22.          

SubPlan (forIndex Only Scan)

23. 6.324 6.324 ↓ 0.0 0 3,162

Index Scan using legacy_circ_count_pkey on legacy_circ_count (cost=0.42..4.44 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=3,162)

  • Index Cond: (id = cp.id)
24. 9.486 237.150 ↑ 1.0 1 3,162

Aggregate (cost=21.35..21.36 rows=1 width=0) (actual time=0.075..0.075 rows=1 loops=3,162)

25. 227.664 227.664 ↑ 1.2 18 3,162

Index Only Scan using action_circulation_target_copy_idx on circulation (cost=0.43..21.30 rows=21 width=0) (actual time=0.012..0.072 rows=18 loops=3,162)

  • Index Cond: (target_copy = cp.id)
  • Heap Fetches: 34566
26. 3.162 12.648 ↑ 1.0 1 3,162

Aggregate (cost=2.48..2.49 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=3,162)

27. 9.486 9.486 ↓ 0.0 0 3,162

Index Only Scan using action_aged_circulation_target_copy_idx on aged_circulation aged_circulation_1 (cost=0.42..2.47 rows=3 width=0) (actual time=0.003..0.003 rows=0 loops=3,162)

  • Index Cond: (target_copy = cp.id)
  • Heap Fetches: 1
28. 0.000 3.162 ↓ 0.0 0 3,162

Index Only Scan using unit_pkey on unit cp_1 (cost=0.14..0.26 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=3,162)

  • Index Cond: (id = "7d74f3b92b19da5e606d737d339a9679".id)
  • Heap Fetches: 0
29.          

SubPlan (forIndex Only Scan)

30. 6.324 6.324 ↓ 0.0 0 3,162

Index Scan using legacy_circ_count_pkey on legacy_circ_count (cost=0.42..4.44 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=3,162)

  • Index Cond: (id = cp_1.id)
31. 9.486 237.150 ↑ 1.0 1 3,162

Aggregate (cost=21.35..21.36 rows=1 width=0) (actual time=0.075..0.075 rows=1 loops=3,162)

32. 227.664 227.664 ↑ 1.2 18 3,162

Index Only Scan using action_circulation_target_copy_idx on circulation (cost=0.43..21.30 rows=21 width=0) (actual time=0.012..0.072 rows=18 loops=3,162)

  • Index Cond: (target_copy = cp_1.id)
  • Heap Fetches: 34566
33. 3.162 12.648 ↑ 1.0 1 3,162

Aggregate (cost=2.48..2.49 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=3,162)

34. 9.486 9.486 ↓ 0.0 0 3,162

Index Only Scan using action_aged_circulation_target_copy_idx on aged_circulation aged_circulation_1 (cost=0.42..2.47 rows=3 width=0) (actual time=0.003..0.003 rows=0 loops=3,162)

  • Index Cond: (target_copy = cp_1.id)
  • Heap Fetches: 1
35. 28.458 28.458 ↑ 1.0 1 3,162

Index Only Scan using record_entry_pkey on record_entry "7f0e2da56d7bdf7c7434e87c5abaab5c" (cost=0.43..0.46 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=3,162)

  • Index Cond: (id = "286d7899ae25bee8bdd7dcde49f9e0b7".record)
  • Heap Fetches: 1161
36. 41.106 41.106 ↑ 1.0 1 3,162

Index Scan using materialized_simple_record_pkey on materialized_simple_record df8d81aecbe6b10702cbd762ac04cac9 (cost=0.43..0.54 rows=1 width=44) (actual time=0.012..0.013 rows=1 loops=3,162)

  • Index Cond: ("7f0e2da56d7bdf7c7434e87c5abaab5c".id = id)
37. 1,861.865 137,531.190 ↓ 4.4 3,950 3,162

HashAggregate (cost=9,387,612.84..9,387,621.79 rows=895 width=16) (actual time=42.923..43.495 rows=3,950 loops=3,162)

  • Group Key: ac.id
38. 3,054.322 135,669.325 ↑ 225.5 67,249 1

Hash Right Join (cost=140,757.01..9,311,782.90 rows=15,165,987 width=16) (actual time=824.705..135,669.325 rows=67,249 loops=1)

  • Hash Cond: ("*SELECT* 1".target_copy = ac.id)
39. 1,174.186 131,791.857 ↑ 1.0 14,865,462 1

Append (cost=0.00..8,810,833.70 rows=15,165,987 width=273) (actual time=0.021..131,791.857 rows=14,865,462 loops=1)

40. 45.225 165.196 ↓ 1.0 208,370 1

Subquery Scan on *SELECT* 1 (cost=0.00..11,670.82 rows=203,541 width=251) (actual time=0.019..165.196 rows=208,370 loops=1)

41. 119.971 119.971 ↓ 1.0 208,370 1

Seq Scan on aged_circulation (cost=0.00..9,635.41 rows=203,541 width=251) (actual time=0.014..119.971 rows=208,370 loops=1)

42. 8,030.274 130,452.475 ↑ 1.0 14,657,092 1

Unique (cost=7,153,293.82..8,649,538.42 rows=14,962,446 width=273) (actual time=107,187.008..130,452.475 rows=14,657,092 loops=1)

43. 51,735.603 122,422.201 ↑ 1.0 14,657,092 1

Sort (cost=7,153,293.82..7,190,699.93 rows=14,962,446 width=273) (actual time=107,187.008..122,422.201 rows=14,657,092 loops=1)

  • Sort Key: circ.id, (COALESCE(a.post_code, b.post_code)), p.home_ou, p.profile, ((date_part('year'::text, (p.dob)::timestamp without time zone))::integer), cp_2.call_number, circ.copy_location, cn.owning_lib, cp_2.circ_lib, cn.record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff, circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration, circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule, circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time, circ.parent_circ, circ.usr
  • Sort Method: external merge Disk: 4263816kB
44. 18,752.126 70,686.598 ↑ 1.0 14,657,092 1

Hash Join (cost=2,680,512.84..3,982,036.08 rows=14,962,446 width=273) (actual time=35,845.131..70,686.598 rows=14,657,092 loops=1)

  • Hash Cond: (circ.target_copy = cp_2.id)
45. 12,108.783 18,182.302 ↑ 1.0 14,657,092 1

Hash Join (cost=128,891.25..1,056,353.34 rows=14,962,446 width=249) (actual time=2,072.741..18,182.302 rows=14,657,092 loops=1)

  • Hash Cond: (circ.usr = p.id)
46. 4,002.203 4,002.203 ↑ 1.0 14,657,092 1

Seq Scan on circulation circ (cost=0.00..721,728.46 rows=14,962,446 width=227) (actual time=0.009..4,002.203 rows=14,657,092 loops=1)

47. 241.650 2,071.316 ↑ 1.2 741,709 1

Hash (cost=117,434.79..117,434.79 rows=916,517 width=26) (actual time=2,071.316..2,071.316 rows=741,709 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 48614kB
48. 311.737 1,829.666 ↑ 1.2 741,709 1

Hash Left Join (cost=54,167.18..117,434.79 rows=916,517 width=26) (actual time=733.642..1,829.666 rows=741,709 loops=1)

  • Hash Cond: (p.billing_address = b.id)
49. 611.270 1,161.937 ↑ 1.2 741,709 1

Hash Left Join (cost=27,083.59..82,675.22 rows=916,517 width=25) (actual time=376.209..1,161.937 rows=741,709 loops=1)

  • Hash Cond: (p.mailing_address = a.id)
50. 175.823 175.823 ↑ 1.2 741,709 1

Seq Scan on usr p (cost=0.00..43,630.17 rows=916,517 width=24) (actual time=0.005..175.823 rows=741,709 loops=1)

51. 184.621 374.844 ↓ 1.0 748,645 1

Hash (cost=17,761.04..17,761.04 rows=745,804 width=9) (actual time=374.844..374.844 rows=748,645 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 40339kB
52. 190.223 190.223 ↓ 1.0 748,645 1

Seq Scan on usr_address a (cost=0.00..17,761.04 rows=745,804 width=9) (actual time=0.015..190.223 rows=748,645 loops=1)

53. 178.840 355.992 ↓ 1.0 748,645 1

Hash (cost=17,761.04..17,761.04 rows=745,804 width=9) (actual time=355.992..355.992 rows=748,645 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 40339kB
54. 177.152 177.152 ↓ 1.0 748,645 1

Seq Scan on usr_address b (cost=0.00..17,761.04 rows=745,804 width=9) (actual time=0.006..177.152 rows=748,645 loops=1)

55. 1,530.231 33,752.170 ↑ 1.0 3,996,226 1

Hash (cost=2,501,622.77..2,501,622.77 rows=3,999,906 width=32) (actual time=33,752.170..33,752.170 rows=3,996,226 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 313753kB
56. 12,127.394 32,221.939 ↑ 1.0 3,996,226 1

Hash Join (cost=1,829,532.70..2,501,622.77 rows=3,999,906 width=32) (actual time=18,908.498..32,221.939 rows=3,996,226 loops=1)

  • Hash Cond: (cp_2.call_number = cn.id)
57. 297.036 1,268.419 ↑ 1.0 3,996,226 1

Append (cost=0.00..130,746.06 rows=3,999,906 width=20) (actual time=0.011..1,268.419 rows=3,996,226 loops=1)

58. 971.382 971.382 ↑ 1.0 3,996,226 1

Seq Scan on copy cp_2 (cost=0.00..130,734.46 rows=3,999,746 width=20) (actual time=0.010..971.382 rows=3,996,226 loops=1)

59. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on unit cp_3 (cost=0.00..11.60 rows=160 width=20) (actual time=0.001..0.001 rows=0 loops=1)

60. 10,619.053 18,826.126 ↓ 1.0 41,027,281 1

Hash (cost=1,079,176.31..1,079,176.31 rows=40,870,431 width=20) (actual time=18,826.126..18,826.126 rows=41,027,281 loops=1)

  • Buckets: 16777216 Batches: 4 Memory Usage: 691741kB
61. 8,207.073 8,207.073 ↓ 1.0 41,027,281 1

Seq Scan on call_number cn (cost=0.00..1,079,176.31 rows=40,870,431 width=20) (actual time=0.017..8,207.073 rows=41,027,281 loops=1)

62. 1.367 823.146 ↓ 4.4 3,950 1

Hash (cost=140,745.83..140,745.83 rows=895 width=8) (actual time=823.146..823.146 rows=3,950 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 187kB
63. 0.564 821.779 ↓ 4.4 3,950 1

Append (cost=0.00..140,745.83 rows=895 width=8) (actual time=0.018..821.779 rows=3,950 loops=1)

64. 821.215 821.215 ↓ 4.4 3,950 1

Seq Scan on copy ac (cost=0.00..140,733.83 rows=894 width=8) (actual time=0.017..821.215 rows=3,950 loops=1)

  • Filter: (location = 368)
  • Rows Removed by Filter: 3992276
65. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on unit ac_1 (cost=0.00..12.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (location = 368)
Planning time : 6.317 ms
Execution time : 141,098.157 ms