marc expert search long running queries
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Evergreen |
Fix Released
|
Undecided
|
Unassigned |
Bug Description
In EG 2.6 with Pg 9.2 Marc Expert searches always return no entries found. Looking into the database I found very long running queries associated with the searches. Example from pg_stats_statements data:
SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
FROM pg_stat_statements ORDER BY total_time DESC limit 3;
-[ RECORD 1 ]------
query | SELECT f.record, sum(f.sum), count(DISTINCT cp.id), sum(f.sum)
| FROM (SELECT x.record, sum(x.sum) FROM ((SELECT record, AVG(ts_
| asset.call_number cn,
| asset.copy cp,
| config.copy_status cs,
| asset.copy_location cl,
| biblio.record_entry br,
| metabib.
| actor.org_
| WHERE br.id = f.record
| AND cn.record = f.record
| AND rd.record = f.record
| AND cp.status = cs.id
| AND cp.location = cl.id
| AND br.deleted IS FALSE
| AND cn.deleted IS FALSE
| AND cp.deleted IS FALSE
| AND cn.owning_lib = d.id
| AND cp.call_number = cn.id
| AND d.opac_visible IS TRUE AND cp.opac_visible IS TRUE AND cs.opac_visible IS TRUE AND cl.opac_visible IS TRUE
|
|
|
|
|
| GROUP BY f.record HAVING count(DISTINCT cp.id) > ?
| ORDER BY 4 DESC,3 DESC
| ;
calls | 1
total_time | 527175.736
rows | 38
hit_percent | 100.00000000000
Changed in evergreen: | |
milestone: | none → 2.6.0 |
Changed in evergreen: | |
status: | Fix Committed → Fix Released |
This is very likely caused by the (useless) inclusion of metabib. rec_descriptor in the query. Here's a branch that avoids including it in cases where it is not necessary, which is all TPAC cases.
http:// git.evergreen- ils.org/ ?p=working/ Evergreen. git;a=shortlog; h=refs/ heads/user/ miker/lp1306133 -optional- use-of- mrd