[OPEN-ILS-GENERAL] SQL Query Performance

[Galen Charlton]

Hi,

On Fri, Apr 26, 2013 at 10:58 AM, Martha Driscoll <driscoll at noblenet.org> 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:  gmc at esilibrary.com
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