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