[OPEN-ILS-GENERAL] SQL Query Performance

[Joshua D. Drake]

On 04/26/2013 11:17 AM, Galen Charlton wrote:
>
> 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:

Actually,

The quick (and correct) answer is that asset.copy doesn't have an index 
that is on barcode. In fact the current index on asset.copy(barcode) is 
actually wrong.

"copy_barcode_key" UNIQUE, btree (barcode) WHERE deleted = false OR 
deleted IS FALSE

Although a valid index, the two conditions within the index are 
duplicative. It would be better to drop that index and just use:

CREATE UNIQUE INDEX copy_barcode_key ON asset.copy(barcode);

PostgreSQL will grab the appropriate barcodes and then filter out the 
results based on the WHERE clause.

Sincerely,

Joshua D. Drake

-- 
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC
@cmdpromptinc - 509-416-6579