From 28df5614f0d849c8edb0bca71f9e7dc0107e9344 Mon Sep 17 00:00:00 2001 From: dbs Date: Fri, 4 Jun 2010 03:29:03 +0000 Subject: [PATCH] Improve database performance for partial indexes on boolean columns (bug 587028 in Launchpad) PostgreSQL 8.3/8.4 treats "IS FALSE" and "= FALSE" expressions differently, which can have a significant impact on database performance if an index is created with one and a query uses the other. This patch addresses all existing partial indexes on boolean columns to enable the query planner to use an index scan whether the query uses either expression. Thanks to James Fournie of BC Sitka for noticing the problem with the index on asset.copy and opening bug 587028 in Launchpad with the suggested fix! git-svn-id: svn://svn.open-ils.org/ILS/trunk@16588 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/010.schema.biblio.sql | 2 +- Open-ILS/src/sql/Pg/011.schema.authority.sql | 2 +- Open-ILS/src/sql/Pg/040.schema.asset.sql | 4 ++-- .../src/sql/Pg/upgrade/0291.schema.boolean_indexes.sql | 17 +++++++++++++++++ 5 files changed, 22 insertions(+), 5 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0291.schema.boolean_indexes.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 6c20c9ed5..3a93091b8 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -65,7 +65,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0290'); -- phasefx +INSERT INTO config.upgrade_log (version) VALUES ('0291'); -- dbs CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/010.schema.biblio.sql b/Open-ILS/src/sql/Pg/010.schema.biblio.sql index 7a5c1b48d..b0f87c14f 100644 --- a/Open-ILS/src/sql/Pg/010.schema.biblio.sql +++ b/Open-ILS/src/sql/Pg/010.schema.biblio.sql @@ -48,7 +48,7 @@ CREATE INDEX biblio_record_entry_create_date_idx ON biblio.record_entry ( create CREATE INDEX biblio_record_entry_editor_idx ON biblio.record_entry ( editor ); CREATE INDEX biblio_record_entry_edit_date_idx ON biblio.record_entry ( edit_date ); CREATE INDEX biblio_record_entry_fp_idx ON biblio.record_entry ( fingerprint ); -CREATE UNIQUE INDEX biblio_record_unique_tcn ON biblio.record_entry (tcn_value) WHERE deleted IS FALSE; +CREATE UNIQUE INDEX biblio_record_unique_tcn ON biblio.record_entry (tcn_value) WHERE deleted = FALSE OR deleted IS FALSE; CREATE TABLE biblio.record_note ( id BIGSERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/011.schema.authority.sql b/Open-ILS/src/sql/Pg/011.schema.authority.sql index c03275a63..9b010beb1 100644 --- a/Open-ILS/src/sql/Pg/011.schema.authority.sql +++ b/Open-ILS/src/sql/Pg/011.schema.authority.sql @@ -36,7 +36,7 @@ CREATE TABLE authority.record_entry ( ); CREATE INDEX authority_record_entry_creator_idx ON authority.record_entry ( creator ); CREATE INDEX authority_record_entry_editor_idx ON authority.record_entry ( editor ); -CREATE UNIQUE INDEX authority_record_unique_tcn ON authority.record_entry (arn_source,arn_value) WHERE deleted IS FALSE; +CREATE UNIQUE INDEX authority_record_unique_tcn ON authority.record_entry (arn_source,arn_value) WHERE deleted = FALSE OR deleted IS FALSE; CREATE TABLE authority.record_note ( id BIGSERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index cdc0354de..70c0e73fe 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -80,7 +80,7 @@ CREATE TABLE asset.copy ( status_changed_time TIMESTAMP WITH TIME ZONE, mint_condition BOOL NOT NULL DEFAULT TRUE ); -CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted IS FALSE; +CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted = FALSE OR deleted IS FALSE; CREATE INDEX cp_cn_idx ON asset.copy (call_number); CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number); CREATE INDEX cp_creator_idx ON asset.copy ( creator ); @@ -194,7 +194,7 @@ CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator); CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor); CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label)); CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (upper(label),id,owning_lib); -CREATE UNIQUE INDEX asset_call_number_label_once_per_lib ON asset.call_number (record, owning_lib, label) WHERE deleted IS FALSE; +CREATE UNIQUE INDEX asset_call_number_label_once_per_lib ON asset.call_number (record, owning_lib, label) WHERE deleted = FALSE OR deleted IS FALSE; CREATE RULE protect_cn_delete AS ON DELETE TO asset.call_number DO INSTEAD UPDATE asset.call_number SET deleted = TRUE WHERE OLD.id = asset.call_number.id; CREATE TABLE asset.uri_call_number_map ( diff --git a/Open-ILS/src/sql/Pg/upgrade/0291.schema.boolean_indexes.sql b/Open-ILS/src/sql/Pg/upgrade/0291.schema.boolean_indexes.sql new file mode 100644 index 000000000..88c619525 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0291.schema.boolean_indexes.sql @@ -0,0 +1,17 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0291'); -- dbs + +DROP INDEX IF EXISTS authority.authority_record_unique_tcn; +CREATE UNIQUE INDEX authority_record_unique_tcn ON authority.record_entry (arn_source,arn_value) WHERE deleted = FALSE OR deleted IS FALSE; + +DROP INDEX IF EXISTS asset.asset_call_number_label_once_per_lib; +CREATE UNIQUE INDEX asset_call_number_label_once_per_lib ON asset.call_number (record, owning_lib, label) WHERE deleted = FALSE OR deleted IS FALSE; + +DROP INDEX IF EXISTS asset.copy_barcode_key; +CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted = FALSE OR deleted IS FALSE; + +DROP INDEX IF EXISTS biblio.biblio_record_unique_tcn; +CREATE UNIQUE INDEX biblio_record_unique_tcn ON biblio.record_entry (tcn_value) WHERE deleted = FALSE OR deleted IS FALSE; + +COMMIT; -- 2.11.0