Comment 12 for bug 1236979

I modified the records_by_item_age stored procedure to check and see if the v_results set has enough records in it to fulfill the offest + limit. If it does, then it looks for newer records only from that point forward.

I started to work on a separate commit that used the join with asset.call_number, but I realized it would be no use. I was hoping that by returned an ordered result set, the offset and limit could help decrease load times for pages deeper in the results. However, I did not realize when I started it, that the v_results skips over records that are older, so using offset and limit within each circ lib would not be practical.

So, with the modifications the speed has increased significantly for results at greater page values. Here are some stats, which were run against a copy of the Sitka database from July 23rd, 2013.

Time For new_books_by_item query

This is the original SQL query that depesz identified as being a bottleneck.

Old Query:

All Libs:

19576.464 ms

Single Library

8166.984 ms

This is the original modifications by miker to depesz suggested changes. Run at the first page.

First SQL:

All Libs:

 Function Scan on records_by_item_age "biblio.records_by_item_age" (cost=0.25..10.25 rows=1000 width=16) (actual time=179.337..179.338 rows=10 loops=1)
 Total runtime: 179.353 ms

Single Library

 Function Scan on records_by_item_age "biblio.records_by_item_age" (cost=0.25..10.25 rows=1000 width=16) (actual time=0.947..0.947 rows=1 loops=1)
 Total runtime: 0.961 ms

This is my modifications to miker's commit.
New SQL

All Libs

 Function Scan on records_by_item_age "biblio.records_by_item_age" (cost=0.25..10.25 rows=1000 width=16) (actual time=13.190..13.191 rows=10 loops=1)
 Total runtime: 13.207 ms

Single Library

 Function Scan on records_by_item_age "biblio.records_by_item_age" (cost=0.25..10.25 rows=1000 width=16) (actual time=1.295..1.295 rows=1 loops=1)
 Total runtime: 1.310 ms

At page 20:

first commit:

All Libs:

 Function Scan on records_by_item_age "biblio.records_by_item_age" (cost=0.25..10.25 rows=1000 width=16) (actual time=29645.705..29645.707 rows=10 loops=1)
 Total runtime: 29645.745 ms

my commit:

All Libs:

 Function Scan on records_by_item_age "biblio.records_by_item_age" (cost=0.25..10.25 rows=1000 width=16) (actual time=32.808..32.810 rows=10 loops=1)
 Total runtime: 32.832 ms

The modifications that only look for newer records once enough records have been found to satisfy the offset + limit make a significant difference when looking at pages further into the results. They also make a difference within the first page, but not as significant.

However, in the case of a single library, my modifications increase the time needed because it makes an unnecessary skeys check. I just realized, I could put a quick check in to see if the size of p_circ_libs array is 1 and ignore the skeys check if this is the case. I will get that done.