Skip to content

Instantly share code, notes, and snippets.

@berick

berick/simple-report-explain.sql Secret

Created August 10, 2022 15:19
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/f62bc54bd55962e361e3c8aff5469abd to your computer and use it in GitHub Desktop.
Save berick/f62bc54bd55962e361e3c8aff5469abd to your computer and use it in GitHub Desktop.
Download ZIP
Raw
simple-report-explain.sql
EXPLAIN SELECT "66a77fa2f4d77c8c2cd3273246d91825"."circ_lib_shortname" AS "Checkout Library Short (Policy) Name", "66a77fa2f4d77c8c2cd3273246d91825"."xact_start_year_mon" AS "Circulation Start Year and Month" FROM ( SELECT combcirc.id AS circ_id, combcirc.usr_post_code AS usr_post_code, combcirc.usr_birth_year AS usr_bith_year, combcirc.usr_profile AS usr_profile_id, pgtu.name AS usr_profile, combcirc.usr_home_ou AS usr_home_ou_id, aouuh.shortname AS usr_home_ou_shortname, aouuh.name AS usr_home_ou_name, (actor.org_unit_ancestor_at_depth(combcirc.usr_home_ou, 1)).id AS usr_sys_id, aouus.shortname AS usr_sys_shortname, aouus.name AS usr_sys_name, combcirc.xact_start AS xact_start, CAST(TO_CHAR(combcirc.xact_start, 'YYYY') AS INTEGER) AS xact_start_year, TO_CHAR(combcirc.xact_start, 'YYYY-MM') AS xact_start_year_mon, TO_CHAR(combcirc.xact_start, 'YYYY-MM-DD') AS xact_start_date, combcirc.circ_lib AS circ_lib_id, aouco.shortname AS circ_lib_shortname, aouco.name AS circ_lib_name, (actor.org_unit_ancestor_at_depth(combcirc.circ_lib, 1)).id AS circ_sys_id, aoucos.shortname AS circ_sys_shortname, aoucos.name AS circ_sys_name, auco.usrname AS circ_staff, awso.name AS checkout_workstation, combcirc.due_date AS due_date, CAST(TO_CHAR(combcirc.due_date, 'YYYY') AS INTEGER) AS due_date_year, TO_CHAR(combcirc.due_date, 'YYYY-MM') AS due_date_year_mon, TO_CHAR(combcirc.due_date, 'YYYY-MM-DD') AS due_date_date, combcirc.xact_finish AS xact_finish, CAST(TO_CHAR(combcirc.xact_finish, 'YYYY') AS INTEGER) AS xact_finish_year, TO_CHAR(combcirc.xact_finish, 'YYYY-MM') AS xact_finish_year_mon, TO_CHAR(combcirc.xact_finish, 'YYYY-MM-DD') AS xact_finish_date, aouci.shortname AS checkin_lib_shortname, aouci.name AS checkin_lib_name, auci.usrname AS checkin_staff, awsi.name AS checkin_workstation, aoucis.shortname AS checkin_sys_shortname, aoucis.name AS checkin_sys_name, combcirc.checkin_time AS checkin_time, combcirc.renewal_remaining AS renewal_remaining, combcirc.auto_renewal_remaining AS auto_renewal_remaining, combcirc.grace_period AS grace_period, combcirc.stop_fines AS stop_fines, combcirc.stop_fines_time AS stop_fines_time, combcirc.duration AS duration, combcirc.fine_interval AS fine_interval, combcirc.recurring_fine AS recurring_fine, combcirc.max_fine AS max_fine, combcirc.duration_rule AS duration_rule, combcirc.recurring_fine_rule AS recurring_fine_rule, combcirc.max_fine_rule AS max_fine_rule, CASE WHEN combcirc.parent_circ IS NULL THEN 'circ' ELSE 'renew' END AS circ_renew, CASE WHEN phone_renewal THEN 'phone' WHEN desk_renewal THEN 'desk' WHEN opac_renewal THEN 'opac' WHEN auto_renewal THEN 'auto' ELSE null END AS renewal_type, CASE WHEN COALESCE(combcirc.checkin_time, now()) > combcirc.due_date THEN true ELSE false END AS is_overdue, acp.barcode AS copy_barcode, acp.circ_modifier AS copy_circ_modifier_code, ccm.name AS copy_circ_modifier_name, acp.circ_lib AS copy_circ_lib_id, acp.location AS location_id, acpl.name AS location_name, aoucp.shortname AS copy_circ_lib_shortname, aoucp.name AS copy_circ_lib_name, (actor.org_unit_ancestor_at_depth(copy_circ_lib, 1)).id AS copy_sys_id, aoucps.shortname AS copy_sys_shortname, aoucps.name AS copy_sys_name, combcirc.copy_owning_lib AS copy_owning_lib_id, aoucpo.shortname AS copy_owning_lib_shortname, aoucpo.name AS copy_owning_lib_name, TRIM(BOTH ' ' FROM concat_ws(' ', acnp.label, acn.label, acns.label)) AS copy_call_number_full, acnp.label AS copy_call_number_prefix, acn.label AS copy_call_number_label, acns.label AS copy_call_number_suffix, TRIM(BOTH ' ' FROM concat_ws(' ', acnp.label_sortkey, acn.label_sortkey, acns.label_sortkey)) AS copy_call_number_sortkey_full, acnp.label_sortkey AS copy_call_number_prefix_sortkey, acn.label_sortkey AS copy_call_number_label_sortkey, acns.label_sortkey AS copy_call_number_suffix_sortkey, COALESCE(rmsr.title, acp.dummy_title) AS title, COALESCE(rmsr.author, acp.dummy_author) AS author, rmsr.publisher AS publisher, public.approximate_date(rmsr.pubdate, '0') AS pubdate, array_to_string(rmsr.isbn, ', ') AS isbn, array_to_string(rmsr.issn, ', ') AS issn, part.label AS part_label, part.label_sortkey AS part_label_sortkey, acn.record AS bib_id, rmsr.tcn_value AS tcn_value FROM action.all_circulation combcirc INNER JOIN actor.org_unit aouuh ON (combcirc.usr_home_ou = aouuh.id) LEFT JOIN actor.org_unit aouus ON ((actor.org_unit_ancestor_at_depth(combcirc.usr_home_ou, 1)).id = aouus.id) LEFT JOIN permission.grp_tree pgtu ON (combcirc.usr_profile = pgtu.id) LEFT JOIN actor.workstation awso ON (combcirc.workstation = awso.id) LEFT JOIN actor.workstation awsi ON (combcirc.checkin_workstation = awsi.id) INNER JOIN actor.org_unit aouco ON (combcirc.circ_lib = aouco.id) LEFT JOIN actor.org_unit aouci ON (combcirc.checkin_lib = aouci.id) LEFT JOIN actor.org_unit aoucos ON ((actor.org_unit_ancestor_at_depth(combcirc.circ_lib, 1)).id = aoucos.id) LEFT JOIN actor.org_unit aoucis ON ((actor.org_unit_ancestor_at_depth(combcirc.checkin_lib, 1)).id = aouci.id) LEFT JOIN actor.usr auco ON (combcirc.circ_staff = auco.id) LEFT JOIN actor.usr auci ON (combcirc.checkin_staff = auci.id) INNER JOIN asset.copy acp ON (combcirc.target_copy = acp.id) INNER JOIN asset.copy_location acpl ON (acp.location = acpl.id) INNER JOIN actor.org_unit aoucp ON (acp.circ_lib = aoucp.id) LEFT JOIN actor.org_unit aoucps ON ((actor.org_unit_ancestor_at_depth(combcirc.copy_circ_lib, 1)).id = aoucps.id) LEFT JOIN actor.org_unit aoucpo ON (combcirc.copy_owning_lib = aoucpo.id) LEFT JOIN actor.org_unit aoucpos ON ((actor.org_unit_ancestor_at_depth(combcirc.copy_owning_lib, 1)).id = aoucpos.id) LEFT JOIN config.circ_modifier ccm ON (acp.circ_modifier = ccm.code) INNER JOIN asset.call_number acn ON (acp.call_number = acn.id) INNER JOIN asset.call_number_prefix acnp ON (acn.prefix = acnp.id) INNER JOIN asset.call_number_suffix acns ON (acn.suffix = acns.id) LEFT JOIN reporter.materialized_simple_record rmsr ON (acn.record = rmsr.id) LEFT JOIN ( SELECT bmp.record, bmp.label, bmp.label_sortkey, acmp.target_copy FROM biblio.monograph_part bmp INNER JOIN asset.copy_part_map acmp ON (acmp.part = bmp.id) WHERE NOT bmp.deleted ) part ON (part.record = acn.record AND part.target_copy = acp.id) ) AS "66a77fa2f4d77c8c2cd3273246d91825" WHERE EXTRACT(YEAR FROM "66a77fa2f4d77c8c2cd3273246d91825"."xact_start") = $_199613$2018$_199613$ GROUP BY 1, 2 ORDER BY "66a77fa2f4d77c8c2cd3273246d91825"."circ_lib_shortname" ASC, "66a77fa2f4d77c8c2cd3273246d91825"."xact_start_year_mon" ASC;
Group (cost=97743109.28..97764626.63 rows=15000 width=35)
Group Key: aouco.shortname, (to_char("*SELECT* 1".xact_start, 'YYYY-MM'::text))
-> Sort (cost=97743109.28..97750031.73 rows=138449 width=35)
Sort Key: aouco.shortname, (to_char("*SELECT* 1".xact_start, 'YYYY-MM'::text))
-> Nested Loop (cost=1161.50..97506652.30 rows=138449 width=35) Join Filter: (acn.suffix = acns.id)
-> Seq Scan on call_number_suffix acns (cost=0.00..1.05 rows=1 width=4) -> Nested Loop (cost=1161.50..97485883.90 rows=138449 width=15) Join Filter: (acn.prefix = acnp.id) -> Seq Scan on call_number_prefix acnp (cost=0.00..1.05 rows=1 width=4) -> Hash Join (cost=1161.50..97472037.95 rows=138449 width=19) Hash Cond: (acp.circ_lib = aoucp.id) -> Nested Loop Left Join (cost=1148.25..97464622.29 rows=138449 width=23) -> Nested Loop (cost=1142.30..97198447.91 rows=138449 width=39)
-> Nested Loop Left Join (cost=1133.60..95966821.79 rows=138449 width=31) Join Filter: ((actor.org_unit_ancestor_at_depth("*SELECT* 1".checkin_lib, 1)).id = aouci.id)
-> Hash Left Join (cost=1133.60..42490886.04 rows=138449 width=39) Hash Cond: ("*SELECT* 1".checkin_lib = aouci.id)
-> Hash Join (cost=1120.35..42483554.05 rows=138449 width=35) Hash Cond: (acp.location = acpl.id)
-> Nested Loop (cost=35.20..42475198.88 rows=138449 width=39) -> Hash Join (cost=26.50..40934333.94 rows=138449 width=23)
Hash Cond: ("*SELECT* 1".circ_lib = aouco.id)
-> Hash Join (cost=13.25..40914803.96 rows=369198 width=24)
Hash Cond: ("*SELECT* 1".usr_home_ou = aouuh.id)
-> Append (cost=0.00..40813519.67 rows=984528 width=396)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..33981961.90 rows=904821 width=265)
-> Seq Scan on aged_circulation (cost=0.00..33891479.80 rows=904821 width=261)
Filter: (date_part('year'::text, xact_start) = '2018'::double precision)
-> HashAggregate (cost=6811631.02..6827572.42 rows=79707 width=289)
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)
-> Nested Loop (cost=4470202.74..5927211.47 rows=79707 width=257)
-> Hash Right Join (cost=4470194.04..4861545.32 rows=79707 width=245)
Hash Cond: (a.id = p.mailing_address)
-> Seq Scan on usr_address a (cost=0.00..177023.05 rows=2807341 width=10)
-> Hash (cost=4462223.34..4462223.34 rows=79707 width=243)
-> Hash Right Join (cost=4070876.57..4462223.34 rows=79707 width=243)
Hash Cond: (b.id = p.billing_address)
-> Seq Scan on usr_address b (cost=0.00..177023.05 rows=2807341 width=10)
-> Hash (cost=4062905.87..4062905.87 rows=79707 width=241)
-> Hash Join (cost=596288.60..4062905.87 rows=79707 width=241)
Hash Cond: (circ.usr = p.id)
-> Seq Scan on circulation circ (cost=0.00..3462432.65 rows=79707 width=221)
Filter: (date_part('year'::text, xact_start) = '2018'::double precision)
-> Hash (cost=313532.20..313532.20 rows=2827564 width=24)
-> Append (cost=8.70..13.27 rows=2 width=20)
-> Index Scan using copy_pkey on copy cp (cost=8.70..11.77 rows=1 width=20)
Index Cond: (id = circ.target_copy)
-> Seq Scan on unit cp_1 (cost=0.00..1.50 rows=1 width=20)
Filter: (circ.target_copy = id)
-> Index Scan using call_number_pkey on call_number cn (cost=8.70..8.90 rows=1 width=20)
Index Cond: (id = cp.call_number)
-> Hash (cost=5.75..5.75 rows=75 width=4)
-> Seq Scan on org_unit aouuh (cost=0.00..5.75 rows=75 width=4)
-> Hash (cost=5.75..5.75 rows=75 width=7)
-> Seq Scan on org_unit aouco (cost=0.00..5.75 rows=75 width=7)
-> Append (cost=8.70..11.03 rows=2 width=26)
-> Index Scan using copy_pkey on copy acp (cost=8.70..9.53 rows=1 width=26)
Index Cond: (id = "*SELECT* 1".target_copy)
-> Seq Scan on unit acp_1 (cost=0.00..1.50 rows=1 width=27)
Filter: ("*SELECT* 1".target_copy = id)
-> Hash (cost=397.05..397.05 rows=6881 width=4)
-> Seq Scan on copy_location acpl (cost=0.00..397.05 rows=6881 width=4)
-> Hash (cost=5.75..5.75 rows=75 width=4)
-> Seq Scan on org_unit aouci (cost=0.00..5.75 rows=75 width=4)
-> Materialize (cost=0.00..13.25 rows=75 width=0)
-> Seq Scan on org_unit aoucis (cost=0.00..5.75 rows=75 width=0)
-> Index Scan using call_number_pkey on call_number acn (cost=8.70..8.90 rows=1 width=24)
Index Cond: (id = acp.call_number)
-> Hash Join (cost=5.95..7.77 rows=2 width=16)
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)
Index Cond: (target_copy = acp.id)
-> Hash (cost=2.70..2.70 rows=2 width=12)
-> Index Scan using record_label_unique_idx on monograph_part bmp (cost=2.50..2.70 rows=2 width=12)
Index Cond: (record = acn.record)
-> Hash (cost=5.75..5.75 rows=75 width=4)
-> Seq Scan on org_unit aoucp (cost=0.00..5.75 rows=75 width=4)
-> Seq Scan on usr p (cost=0.00..313532.20 rows=2827564 width=24)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment