Hi, On Fri, Apr 26, 2013 at 10:58 AM, Martha Driscoll <[email protected]> wrote: > In our database of 3,161,189 copies, cutting out the deleted copies > eliminates only 904 copies. Why does testing for deleted improve searching > so significantly?
The quick answer is that the asset.copy index on the barcode column also includes a clause to limit the index to copies that aren't deleted: CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted = FALSE OR deleted IS FALSE; Consequently, an SQL query on copy barcode should also include a "deleted = false" or "delete is false" clause to improve the chances that the query planner will use the copy_barcode_key index rather than doing a full table scan. Regards, Galen -- Galen Charlton Manager of Implementation Equinox Software, Inc. / The Open Source Experts email: [email protected] direct: +1 770-709-5581 cell: +1 404-984-4366 skype: gmcharlt web: http://www.esilibrary.com/ Supporting Koha and Evergreen: http://koha-community.org & http://evergreen-ils.org
