Comment 18 for bug 1482757

I tested this branch with a large deletion of 51,655 URIs from 51,422 bib records over the past week. I ran the deletion twice on Pg 10 and twice on Pg 9.6. The first run was without this branch installed, and the second run with. Both databases use the default PostgreSQL configuration, except for the port.

For the purposes of this test, I added code using the Time::HiRes Perl module to the script that I normally run to delete located URIs by vendor and library in order to time the record updates. My script works by first finding the records that need to have URIs removed, extracting the MARC from the record, making a MARC::Record object, deleting the appropriate 856 tags, and then updating the biblio.record_entry with the modified MARC. The timing code is wrapped around the DBI call to do the database update, so it should be an accurate measure of the impact of this branch on the database.

Between test runs, I use pg_restore to load the same dump used in previous tests, and ran vacuumdb with the -f and -z options on the restored database.

It should be noted that this database server runs multiple instances of different versions of PostgreSQL. However, the other instances were not used during the test runs.

I got differing results from Pg 10 vs. Pg 9.6. (NOTE: I rounded the times below to the nearest hundredth of a second. Tim::HiRes gives much finer grained results.)

On Pg 10 WITHOUT the patch, the average update time for a bib record with located URIs was 1.85 seconds. WITH the patch on Pg 10, the average time to update a record increased to 2.25 seconds.

On Pg 9.6 the average time to update a record was 2.76 seconds WITHOUT the patch, and it decreased to 2.38 seconds WITH the patch.

In order to be scientifically useful, the results should be run thousands of times in each configuration and then averaged. Since it takes about 3 days to run a complete test including the database reload, etc., this is no feasible at this time.

Given what I've seen with this test, I have added my signoff and with an extra commit to speed up the deletion of orphaned asset.uri entries in the upgrade script:

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/collab/dyrcona/lp1482757

I think more testing may be warranted for this branch, particularly since it seems to degrade performance on PG 10. Given the cursory nature of my testing, I cannot rule out some other factor having an effect on this number.