[OPEN-ILS-DEV] Rewrite metabib.rec_descriptor view using crosstab()?

Fri Apr 11 13:28:37 EDT 2014

Dan,

My personal opinion is that we should kill rec_descriptor with fire,
and not spend effort to improve it.  Partly to avoid crosstab if
possible, because it brings back a dependency on the tablefuncs
extension, and partly because it's a lie.  Of course, we'll have to
keep something like it around for reporting purposes.

Looking at the code, and ignoring, upgrade, authority and JSPAC parts
of the source tree, here's what I get:

miker at foolery:~/git/head-ILS (master)$ gack rec_descriptor|grep -v
upgrade|grep -v skin|grep -v authority
Open-ILS/src/sql/Pg/030.schema.metabib.sql:439:CREATE VIEW
metabib.rec_descriptor AS
Open-ILS/src/sql/Pg/030.schema.metabib.sql:1716:        PERFORM * FROM
config.internal_flag WHERE name =
'ingest.disable_metabib_rec_descriptor' AND enabled;
Open-ILS/src/sql/Pg/example.reporter-extension.sql:107: JOIN
metabib.rec_descriptor rd ON (rd.record = cn.record)
Open-ILS/src/sql/Pg/002.schema.config.sql:46:INSERT INTO
config.internal_flag (name) VALUES
('ingest.disable_metabib_rec_descriptor');
Open-ILS/src/sql/Pg/100.circ_matrix.sql:160:    rec_descriptor
metabib.rec_descriptor%ROWTYPE;
Open-ILS/src/sql/Pg/100.circ_matrix.sql:175:    SELECT INTO
rec_descriptor  * FROM metabib.rec_descriptor   WHERE record =
cn_object.record;
Open-ILS/src/sql/Pg/100.circ_matrix.sql:252:                AND
(m.marc_type                IS NULL OR m.marc_type =
COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
Open-ILS/src/sql/Pg/100.circ_matrix.sql:253:                AND
(m.marc_form                IS NULL OR m.marc_form =
rec_descriptor.item_form)
Open-ILS/src/sql/Pg/100.circ_matrix.sql:254:                AND
(m.marc_bib_level           IS NULL OR m.marc_bib_level =
rec_descriptor.bib_level)
Open-ILS/src/sql/Pg/100.circ_matrix.sql:255:                AND
(m.marc_vr_format           IS NULL OR m.marc_vr_format =
rec_descriptor.vr_format)
Open-ILS/src/sql/Pg/110.hold_matrix.sql:73:    rec_descriptor
metabib.rec_descriptor%ROWTYPE;
Open-ILS/src/sql/Pg/110.hold_matrix.sql:84:    SELECT INTO
rec_descriptor      * FROM metabib.rec_descriptor   WHERE record =
item_cn_object.record;
Open-ILS/src/sql/Pg/110.hold_matrix.sql:181:            AND
(m.marc_type            IS NULL OR m.marc_type =
COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
Open-ILS/src/sql/Pg/110.hold_matrix.sql:182:            AND
(m.marc_form            IS NULL OR m.marc_form =
rec_descriptor.item_form)
Open-ILS/src/sql/Pg/110.hold_matrix.sql:183:            AND
(m.marc_bib_level       IS NULL OR m.marc_bib_level =
rec_descriptor.bib_level)
Open-ILS/src/sql/Pg/110.hold_matrix.sql:184:            AND
(m.marc_vr_format       IS NULL OR m.marc_vr_format =
rec_descriptor.vr_format)
Open-ILS/src/perlmods/lib/OpenILS/Application/Search/Biblio.pm:2658:
 method    => 'rec_to_mr_rec_descriptors',
Open-ILS/src/perlmods/lib/OpenILS/Application/Search/Biblio.pm:2669:sub
rec_to_mr_rec_descriptors {
Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/CDBI/metabib.pm:88:metabib::record_descriptor->table(
'metabib_rec_descriptor' );
Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/dbi.pm:658:
   metabib::record_descriptor->table( 'metabib.rec_descriptor' );
Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/dbi.pm:659:
   metabib::record_descriptor->sequence(
'metabib.rec_descriptor_id_seq' );
Open-ILS/web/opac/common/xml/item_lang_options.xml:7:   join
metabib.rec_descriptor r on (r.item_lang = m.code) group by 1,2;
Open-ILS/examples/fm_IDL.xml:3512:      <class id="mrd"
controller="open-ils.cstore"
oils_obj:fieldmapper="metabib::record_descriptor"
oils_persist:tablename="metabib.rec_descriptor" reporter:label="Basic
Record Descriptor">
Open-ILS/examples/fm_IDL.xml:3513:              <fields
oils_persist:primary="id"
oils_persist:sequence="metabib.rec_descriptor_id_seq">
miker at foolery:~/git/head-ILS (master)$ gack record_descriptor|grep -v
upgrade|grep -v authority|grep -v metabib
Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm:1826:
                     title_descriptor =>
$old_best->call_number->record->record_descriptor->next->to_fieldmapper,
Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm:2263:
                 title_descriptor =>
$c->call_number->record->record_descriptor->next->to_fieldmapper,
Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/biblio.pm:144:
   $rec->fixed_fields( $r->record_descriptor->next->to_fieldmapper );
Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/biblio.pm:231:
   my $ff = $rec->record_descriptor->next;
Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/asset.pm:808:
   $call_number->record->fixed_fields(
$cn->record->record_descriptor->next->to_fieldmapper );

Of that, only the circ and hold matrices are critical, active code
paths, and those just look up by record ID.  They need to be addressed
(but not as a 2.6 blocker), but I don't think they will cause
short-term pain.

Given the relatively slim uses in the current master codebase, should
we consider moving to metabib.record_attr_flat (or even
direct-to-intarray) where appropriate and removing the rest for
2.next?

On Thu, Apr 10, 2014 at 9:03 PM,  <dkyle at grpl.org> wrote:
> I don't know how good a test this one query is, but using the same query
> as in the expert search bug, I see performance decrease.
>
> http://pastebin.com/wRhKTPWu
>
> Doug.
>
>> Hello all,
>>
>> I was puzzling today about our planner's apparently inability to properly
>> use some of the views (e.g. record_attr, rec_descriptor).  The hstore
>> passthru step seems to be the most likely source of opacity, so I wondered
>> if we might try to use crosstab() instead.
>>
>> Here is a (fairly ugly) attempt to do so:
>>
>> http://pastebin.com/aEvqzXAc
>>
>> I have very little experience using crosstabs, so this can probably be
>> tidied up somehow.  That said, I see promising results on the test data (a
>> sample query falls from ~50ms to ~1.5ms), but we need to try it on a
>> production set to really see if it helps.
>>
>> Anyone care to try it out, or weigh in on this approach?  A good test
>> might be the "expert search" without Mike's latest patch, or perhaps just
>> do some selects of rec_descriptor and see what happens.
>>
>> Thanks!
>> Dan
>>
>>
>> Daniel Wells
>> Library Programmer/Analyst
>> Hekman Library, Calvin College
>> 616.526.7133
>>
>>
>>
>>
>

-- 
Mike Rylander
 | Director of Research and Development
 | Equinox Software, Inc. / Your Library's Guide to Open Source
 | phone:  1-877-OPEN-ILS (673-6457)
 | email:  miker at esilibrary.com
 | web:  http://www.esilibrary.com