| Time |
Nick |
Message |
| 04:54 |
pinesol_green |
Incoming from qatests: Test Success - http://testing.evergreen-ils.org/~live/test.html <http://testing.evergreen-ils.org/~live/test.html> |
| 05:09 |
|
berickm joined #evergreen |
| 07:38 |
bshum |
@later tell eeevil Hmm, wondering on the performance speed of record_attr_flat when there ends up being lots of uncontrolled_record_attr_value to look for. I'm getting truly terrible query performance on things like "SELECT COUNT(*) FROM metabib.record_attr_flat WHERE attr = 'oclc'" (our custom definition for tag 001) vs. looking for something that is in vlist like 'search_format' |
| 07:38 |
pinesol_green |
bshum: The operation succeeded. |
| 07:38 |
bshum |
I ran vacuum analyze on both vlist and uncontrolled tables. |
| 07:39 |
bshum |
The first has been running for over 320k ms vs. the other which took about 17k ms (still a bit of time) |
| 07:39 |
bshum |
I'll investigate further after the holiday, but it's a puzzlement :) |
| 07:47 |
bshum |
My end goal was to figure out how many bibs still need to be reingested with our custom record attribute. Generally counting from uncontrolled, I can quickly find out, but I couldn't use that to narrow exact bib IDs of what remains. |
| 07:48 |
bshum |
We've had to do them in small spurts cause it has been taking terribly long to reingest the one field. |
| 09:28 |
* dbs |
overlooked the "k" in those measurements at first |
| 09:28 |
eeevil |
bshum: I don't want to sound flippant, but that's not a use case that the software itself cares about. we can't make every single query fast, there will always be ones that could be faster if we changed the shape of the data, but that would in turn slow down some other query. the question "how many records don't have this uncontrolled attribute, globally" is not something that needs to be fast, because it's not something we'd put into, say, a |
| 09:28 |
eeevil |
search result or a checkout api call |
| 09:30 |
eeevil |
bshum: that query (and ones like it) are at the opposite end of the spectrum from how that view is used by the software and how they're designed to be used |
| 09:36 |
eeevil |
bshum: now, if you just wanted to know the count, you could likely make things faster by restructuring your query to build an array of the ids of the values from uncontrolled_record_attr that are used as oclc number, and test record_attr_vlist with the overlaps operator. or, a different tactic is to just join record_attr_vlist on uncon_record_attr with (vlist @> intset(un_rec_attr.id) and un_rec_attr.attr=$oclc_attr_def_id) ... that would be |
| 09:36 |
eeevil |
doing what the view does for exactly the one attr |
| 09:44 |
eeevil |
think of it this way, everything that lives in the metabib schema has a very targetted purpose that is /not/ storing data. that schema is, by design, a dumping ground for specific denormalizations of central, authoritative data, and each table and view is intentionally shaped so that its primary purpose (search record attributes, backing tsearch queries, turn one record's attribute-int-arrays back into something a human can use) is as fast as |
| 09:44 |
eeevil |
possible. all other purposes (that is, "how X performs in arbitrary queries") are, at best, secondary concerns, because if we need some other purpose or use case to be fast we can trade a little more disk space for that speed |
| 09:45 |
eeevil |
and, the use case for record_attr_flat is "give me the attribute names and values for this record (or some small set of records)" |
| 09:46 |
* eeevil |
disappears in a puff of uncontrolled smoke |
| 10:45 |
bshum |
eeevil++ |
| 10:46 |
bshum |
Thanks for the rundown. I understand what you're saying here. And appreciate the suggestion on ways of answering the question via database approaches |
| 10:47 |
bshum |
I'll tinker more with it once we finish moving our servers. |
| 10:47 |
bshum |
Driving everything over to the new data center today/tomorrow. Whee.... |
| 11:38 |
|
berick joined #evergreen |
| 12:14 |
|
board joined #evergreen |
| 12:46 |
|
ldw joined #evergreen |
| 15:40 |
|
dcook joined #evergreen |
| 16:12 |
|
jventuro joined #evergreen |
| 16:18 |
|
jventuro_ joined #evergreen |
| 16:47 |
|
jventuro joined #evergreen |
| 17:19 |
|
ldw joined #evergreen |
| 17:21 |
|
Guest39339 left #evergreen |
| 18:47 |
bshum |
@dessert |
| 18:47 |
* pinesol_green |
grabs some Coconut Cream Cake for bshum |
| 18:47 |
bshum |
Yuck, coconut? No thanks. |
| 18:53 |
jeffdavis |
Shouldn't pinesol_green dispense apple pie on July 4? |
| 19:04 |
bshum |
@dessert search apple |
| 19:04 |
pinesol_green |
bshum: 4 found: #21: "Apple Pie", #23: "Pineapple Upside Down Cake", #26: "pineapple chocolate things from New Zealand", and #7: "Apple Crisp" |
| 19:04 |
bshum |
@dessert 4 jeffdavis |
| 19:04 |
* pinesol_green |
grabs some Lemon Chess Pie for jeffdavis |
| 19:04 |
bshum |
Oops, hehe |
| 19:04 |
bshum |
@dessert 21 jeffdavis |
| 19:04 |
* pinesol_green |
grabs some Apple Pie for jeffdavis |
| 19:04 |
bshum |
There we go :) |
| 19:13 |
jcamins |
@dessert search pecan |
| 19:13 |
pinesol_green |
jcamins: 1 found: #17: "Pecan Pie" |
| 19:13 |
jcamins |
@dessert 17 bshum |
| 19:13 |
* pinesol_green |
grabs some Pecan Pie for bshum |
| 19:17 |
jcamins |
@dessert search mousse |
| 19:17 |
pinesol_green |
jcamins: No matching desserts were found. |
| 19:17 |
* jcamins |
grabs some chocolate mousse for himself |
| 19:19 |
jeffdavis |
:) |
| 19:22 |
bshum |
@dessert add Chocolate Mousse |
| 19:22 |
pinesol_green |
bshum: The operation succeeded. Dessert #27 added. |
| 23:11 |
|
zerick joined #evergreen |
