[OPEN-ILS-DEV] very slow ISBN search
Any thoughts on why searching by ISBN would be painfully slow?
In the following srfsh tests, you'll see that it took over 40 seconds to
return a result. I can use other methods, such as
open-ils.search.biblio.tcn, and get results within a second.
srfsh# request open-ils.search open-ils.search.biblio.isbn 1400061946
Received Data: {
count:1,
ids:[
126360
]
}
Request Completed Successfully
Request Time in seconds: 43.756154
Re: [OPEN-ILS-DEV] very slow ISBN search
On Fri, Jun 20, 2008 at 1:46 PM, Bill Ott [EMAIL PROTECTED] wrote: Any thoughts on why searching by ISBN would be painfully slow? In order to speed up initial load there are some indexes that are left out of the default schema. Issue the following at the psql prompt: CREATE INDEX metabib_full_rec_tag_subfield_idx ON metabib.full_rec (tag,subfield); CREATE INDEX metabib_full_rec_value_idx ON metabib.full_rec (value); The second one will take a while, and it is possible for it to fail if there are records with fields greater than 2.7k and Postgres was not adjusted at compile time to increase the page size. -- Mike Rylander | VP, Research and Design | Equinox Software, Inc. / The Evergreen Experts | phone: 1-877-OPEN-ILS (673-6457) | email: [EMAIL PROTECTED] | web: http://www.esilibrary.com
Re: [OPEN-ILS-DEV] very slow ISBN search
The second one will take a while, and it is possible for it to fail if there are records with fields greater than 2.7k and Postgres was not adjusted at compile time to increase the page size. Doh! ERROR: index row size 4232 exceeds btree maximum, 2713 HINT: Values larger than 1/3 of a buffer page cannot be indexed. Consider a function index of an MD5 hash of the value, or use full text indexing. I believe you noted to me previously that this requires an initdb after the recompile?
Re: [OPEN-ILS-DEV] very slow ISBN search
On Fri, Jun 20, 2008 at 2:58 PM, Bill Ott [EMAIL PROTECTED] wrote: The second one will take a while, and it is possible for it to fail if there are records with fields greater than 2.7k and Postgres was not adjusted at compile time to increase the page size. Doh! ERROR: index row size 4232 exceeds btree maximum, 2713 HINT: Values larger than 1/3 of a buffer page cannot be indexed. Consider a function index of an MD5 hash of the value, or use full text indexing. I believe you noted to me previously that this requires an initdb after the recompile? Indeed. The on-disk format will be different Attached is a patch for Postgres 8.2.9, but it should apply to any version without any trouble. -- Mike Rylander | VP, Research and Design | Equinox Software, Inc. / The Evergreen Experts | phone: 1-877-OPEN-ILS (673-6457) | email: [EMAIL PROTECTED] | web: http://www.esilibrary.com diff -pu postgresql-8.2.9/src/include/pg_config_manual.h postgresql-8.2.9-patch/src/include/pg_config_manual.h --- postgresql-8.2.9/src/include/pg_config_manual.h 2006-09-18 18:40:40.0 -0400 +++ postgresql-8.2.9-patch/src/include/pg_config_manual.h 2008-06-20 15:10:06.0 -0400 @@ -23,7 +23,7 @@ * * Changing BLCKSZ requires an initdb. */ -#define BLCKSZ 8192 +#define BLCKSZ 32768 /* * RELSEG_SIZE is the maximum number of blocks allowed in one disk
Re: [OPEN-ILS-DEV] very slow ISBN search
I believe you noted to me previously that this requires an initdb after the recompile? Indeed. The on-disk format will be different Attached is a patch for Postgres 8.2.9, but it should apply to any version without any trouble. Thanks again Mike ...but on a Friday afternoon, delete from metabib.full_rec where length(value) 2700; Ah, test records!
Re: [OPEN-ILS-DEV] very slow ISBN search
On Fri, Jun 20, 2008 at 3:20 PM, Bill Ott [EMAIL PROTECTED] wrote: I believe you noted to me previously that this requires an initdb after the recompile? Indeed. The on-disk format will be different Attached is a patch for Postgres 8.2.9, but it should apply to any version without any trouble. Thanks again Mike ...but on a Friday afternoon, delete from metabib.full_rec where length(value) 2700; That'll get it done! :) Of course, if you edit and then save the same records, they'll fail due to index constraints. I'm now toying with the idea of hiding that table behind a view that would allow a substring() index to be used on arbitrary queries ... I'll let the list know how the testing of that goes. -- Mike Rylander | VP, Research and Design | Equinox Software, Inc. / The Evergreen Experts | phone: 1-877-OPEN-ILS (673-6457) | email: [EMAIL PROTECTED] | web: http://www.esilibrary.com
