marc expert search long running queries

Bug #1306133 reported by Doug Kyle on 2014-04-10
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Evergreen
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 /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          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_rank(index_vector, to_tsquery(?,?))) as sum FROM metabib.full_rec WHERE ( tag = ? AND subfield LIKE ? AND index_vector @@ to_tsquery(?,?) ) GROUP BY record)) x GROUP BY 1 HAVING COUNT(x.record) = ? ORDER BY 2 DESC ) f,
            | asset.call_number cn,
            | asset.copy cp,
            | config.copy_status cs,
            | asset.copy_location cl,
            | biblio.record_entry br,
            | metabib.rec_descriptor rd,
            | actor.org_unit_descendants(?) d
            | 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.0000000000000000

Revision history for this message
Mike Rylander (mrylander) wrote :

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

tags: added: pullrequest
Changed in evergreen:
milestone: none → 2.6.0
Revision history for this message
Doug Kyle (dkyle) wrote :

Signed-off-by: Doug Kyle <email address hidden>

Revision history for this message
Doug Kyle (dkyle) wrote :
Revision history for this message
Dan Wells (dbw2) wrote :

Pushed to master. Thanks, Mike and Doug!

Changed in evergreen:
status: New → Fix Committed
Changed in evergreen:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers