Skip to content

Instantly share code, notes, and snippets.

@berick

berick/simple-rep-explain-analyze.sql Secret

Created August 12, 2022 14:00
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save berick/ec95e8756e5287373ca46d3335a558b9 to your computer and use it in GitHub Desktop.
Save berick/ec95e8756e5287373ca46d3335a558b9 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
simple-rep-explain-analyze.sql
Group (cost=97743109.28..97764626.63 rows=15000 width=35) (actual time=126687590.747..127092216.597 rows=694 loops=1)
Group Key: aouco.shortname, (to_char("*SELECT* 1".xact_start, 'YYYY-MM'::text))
-> Sort (cost=97743109.28..97750031.73 rows=138449 width=35) (actual time=126687590.738..126959697.812 rows=1157294095 loops=1)
Sort Key: aouco.shortname, (to_char("*SELECT* 1".xact_start, 'YYYY-MM'::text))
Sort Method: external merge Disk: 23781488kB
-> Nested Loop (cost=1161.50..97506652.30 rows=138449 width=35) (actual time=5.580..125827973.644 rows=1157294095 loops=1)
Join Filter: (acn.suffix = acns.id)
-> Seq Scan on call_number_suffix acns (cost=0.00..1.05 rows=1 width=4) (actual time=0.015..0.027 rows=1 loops=1) -> Nested Loop (cost=1161.50..97485883.90 rows=138449 width=15) (actual time=5.498..123693171.389 rows=1157294095 loops=1)
Join Filter: (acn.prefix = acnp.id)
-> Seq Scan on call_number_prefix acnp (cost=0.00..1.05 rows=1 width=4) (actual time=0.017..0.039 rows=1 loops=1)
-> Hash Join (cost=1161.50..97472037.95 rows=138449 width=19) (actual time=5.477..123433165.197 rows=1157294095 loops=1)
Hash Cond: (acp.circ_lib = aoucp.id)
-> Nested Loop Left Join (cost=1148.25..97464622.29 rows=138449 width=23) (actual time=5.367..123001036.375 rows=1157294095 loops=1)
-> Nested Loop (cost=1142.30..97198447.91 rows=138449 width=39) (actual time=5.352..119109634.924 rows=1157294095 loops=1) -> Nested Loop Left Join (cost=1133.60..95966821.79 rows=138449 width=31) (actual time=5.274..115169539.114 rows=1157294095 loops=1)
Join Filter: ((actor.org_unit_ancestor_at_depth("*SELECT* 1".checkin_lib, 1)).id = aouci.id)
Rows Removed by Join Filter: 2283375
-> Hash Left Join (cost=1133.60..42490886.04 rows=138449 width=39) (actual time=3.151..622688.039 rows=15460627 loops=1)
Hash Cond: ("*SELECT* 1".checkin_lib = aouci.id)
-> Hash Join (cost=1120.35..42483554.05 rows=138449 width=35) (actual time=3.090..612926.957 rows=15460627 loops=1)
Hash Cond: (acp.location = acpl.id)
-> Nested Loop (cost=35.20..42475198.88 rows=138449 width=39) (actual time=0.335..600224.137 rows=15460627 loops=1)
-> Hash Join (cost=26.50..40934333.94 rows=138449 width=23) (actual time=0.236..340928.482 rows=15460627 loops=1) Hash Cond: ("*SELECT* 1".circ_lib = aouco.id)
-> Hash Join (cost=13.25..40914803.96 rows=369198 width=24) (actual time=0.163..331013.823 rows=15460627 loops=1)
Hash Cond: ("*SELECT* 1".usr_home_ou = aouuh.id)
-> Append (cost=0.00..40813519.67 rows=984528 width=396) (actual time=0.094..321508.336 rows=15460627 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..33981961.90 rows=904821 width=265) (actual time=0.094..286405.206 rows=14885866 loops=1)
-> Seq Scan on aged_circulation (cost=0.00..33891479.80 rows=904821 width=261) (actual time=0.092..277709.924 rows=14885866 loops=1)
Filter: (date_part('year'::text, xact_start) = '2018'::double precision)
Rows Removed by Filter: 166072787
-> HashAggregate (cost=6811631.02..6827572.42 rows=79707 width=289) (actual time=29308.617..32344.754 rows=574761 loops=1)
Group 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.call_number, circ.copy_location, cn.owning_lib, cp.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.auto_renewal, circ.auto_renewal_remaining, circ.usr
-> Nested Loop (cost=4470211.44..6648231.67 rows=79707 width=289) (actual time=17342.608..27806.931 rows=574761 loops=1)
-> Nested Loop (cost=4470202.74..5927211.47 rows=79707 width=257) (actual time=17342.531..24500.883 rows=574761 loops=1)
-> Hash Right Join (cost=4470194.04..4861545.32 rows=79707 width=245) (actual time=17342.433..19411.896 rows=574761 loops=1)
Hash Cond: (a.id = p.mailing_address)
ctual time=0.035..326.472 rows=2807343 loops=1)
-> Hash (cost=4462223.34..4462223.34 rows=79707 width=243) (actual time=1734
1.814..17341.838 rows=574761 loops=1)
Buckets: 1048576 (originally 131072) Batches: 1 (originally 1) Memory
Usage: 166205kB
-> Hash Right Join (cost=4070876.57..4462223.34 rows=79707 width=243)
(actual time=13010.123..16756.258 rows=574761 loops=1)
Hash Cond: (b.id = p.billing_address)
-> Seq Scan on usr_address b (cost=0.00..177023.05 rows=2807341
width=10) (actual time=0.074..1837.532 rows=2807343 loops=1)
-> Hash (cost=4062905.87..4062905.87 rows=79707 width=241) (actu
al time=13008.849..13008.861 rows=574761 loops=1)
Buckets: 1048576 (originally 131072) Batches: 1 (originally
1) Memory Usage: 164808kB
-> Hash Join (cost=596288.60..4062905.87 rows=79707 width=
241) (actual time=2347.467..12178.361 rows=574761 loops=1)
Hash Cond: (circ.usr = p.id)
-> Seq Scan on circulation circ (cost=0.00..3462432.
65 rows=79707 width=221) (actual time=0.094..9011.451 rows=574761 loops=1)
Filter: (date_part('year'::text, xact_start) = '
2018'::double precision)
Rows Removed by Filter: 14512000
-> Hash (cost=313532.20..313532.20 rows=2827564 widt
h=24) (actual time=2329.338..2329.340 rows=2838538 loops=1)
Buckets: 4194304 Batches: 1 Memory Usage: 1865
62kB
-> Seq Scan on usr p (cost=0.00..313532.20 row
s=2827564 width=24) (actual time=0.030..1485.943 rows=2838538 loops=1)
-> Append (cost=8.70..13.27 rows=2 width=20) (actual time=0.005..0.008 rows=1 loop
s=574761)
-> Index Scan using copy_pkey on copy cp (cost=8.70..11.77 rows=1 width=20)
(actual time=0.005..0.005 rows=1 loops=574761)
Index Cond: (id = circ.target_copy)
-> Seq Scan on unit cp_1 (cost=0.00..1.50 rows=1 width=20) (actual time=0.00
1..0.001 rows=0 loops=574761)
Filter: (circ.target_copy = id)
Rows Removed by Filter: 5
-> Index Scan using call_number_pkey on call_number cn (cost=8.70..8.90 rows=1 width=20)
(actual time=0.005..0.005 rows=1 loops=574761)
Index Cond: (id = cp.call_number)
-> Hash (cost=5.75..5.75 rows=75 width=4) (actual time=0.040..0.040 rows=75 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> Seq Scan on org_unit aouuh (cost=0.00..5.75 rows=75 width=4) (actual time=0.005..0.018 rows=75 lo
ops=1)
-> Hash (cost=5.75..5.75 rows=75 width=7) (actual time=0.048..0.049 rows=75 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> Seq Scan on org_unit aouco (cost=0.00..5.75 rows=75 width=7) (actual time=0.006..0.023 rows=75 loops=1)
-> Append (cost=8.70..11.03 rows=2 width=26) (actual time=0.011..0.016 rows=1 loops=15460627)
-> Index Scan using copy_pkey on copy acp (cost=8.70..9.53 rows=1 width=26) (actual time=0.011..0.011 rows=1 loo
ps=15460627)
Index Cond: (id = "*SELECT* 1".target_copy)
-> Seq Scan on unit acp_1 (cost=0.00..1.50 rows=1 width=27) (actual time=0.002..0.002 rows=0 loops=15460627)
Filter: ("*SELECT* 1".target_copy = id)
Rows Removed by Filter: 5
-> Hash (cost=397.05..397.05 rows=6881 width=4) (actual time=2.680..2.680 rows=6881 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 306kB
-> Seq Scan on copy_location acpl (cost=0.00..397.05 rows=6881 width=4) (actual time=0.025..1.373 rows=6881 loops=1)
-> Hash (cost=5.75..5.75 rows=75 width=4) (actual time=0.036..0.036 rows=75 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> Seq Scan on org_unit aouci (cost=0.00..5.75 rows=75 width=4) (actual time=0.007..0.019 rows=75 loops=1)
-> Materialize (cost=0.00..13.25 rows=75 width=0) (actual time=0.000..0.009 rows=75 loops=15460627)
-> Seq Scan on org_unit aoucis (cost=0.00..5.75 rows=75 width=0) (actual time=0.005..0.023 rows=75 loops=1)
-> Index Scan using call_number_pkey on call_number acn (cost=8.70..8.90 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=1157294095)
Index Cond: (id = acp.call_number)
-> Hash Join (cost=5.95..7.77 rows=2 width=16) (actual time=0.003..0.003 rows=0 loops=1157294095)
Hash Cond: (acmp.part = bmp.id)
-> Index Only Scan using copy_part_map_cp_part_idx on copy_part_map acmp (cost=3.05..4.40 rows=9 width=12) (never executed)
Index Cond: (target_copy = acp.id)
Heap Fetches: 0
-> Hash (cost=2.70..2.70 rows=2 width=12) (actual time=0.001..0.001 rows=0 loops=1157294095)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Index Scan using record_label_unique_idx on monograph_part bmp (cost=2.50..2.70 rows=2 width=12) (actual time=0.001..0.001 rows=0 loo
ps=1157294095)
Index Cond: (record = acn.record)
-> Hash (cost=5.75..5.75 rows=75 width=4) (actual time=0.078..0.079 rows=75 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> Seq Scan on org_unit aoucp (cost=0.00..5.75 rows=75 width=4) (actual time=0.038..0.055 rows=75 loops=1)
Planning time: 19.161 ms
Execution time: 127095178.213 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment