summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorBryan Newbold <bnewbold@robocracy.org>2018-09-10 16:46:49 -0700
committerBryan Newbold <bnewbold@robocracy.org>2018-09-10 16:46:52 -0700
commit2e335344e59de0ff42f1c2797a4e4ffbf3f4b30b (patch)
tree0e385559a4d19fc99d8553ad7b1f29ab06031bb6
parentda10d0e0cebc1142852b959cea93c8b80801d565 (diff)
downloadfatcat-2e335344e59de0ff42f1c2797a4e4ffbf3f4b30b.tar.gz
fatcat-2e335344e59de0ff42f1c2797a4e4ffbf3f4b30b.zip
remove 'IS NOT NULL' identifier index constraints
These seemed to be resulting in table scans on 404s in QA with postgres 10, despite the adding "IS NOT NULL" WHERE clauses earlier. Query time was very significant, even for the creator table (1.2 seconds or so on SSD). I looked at using hash indices (which have improved in postgres 10), which could save index size (disk and RAM) and potentially be faster for these trivial exact lookups, but didn't go for it at this time.
-rw-r--r--rust/migrations/2018-05-12-001226_init/up.sql28
1 files changed, 14 insertions, 14 deletions
diff --git a/rust/migrations/2018-05-12-001226_init/up.sql b/rust/migrations/2018-05-12-001226_init/up.sql
index b0733576..c21e5b5e 100644
--- a/rust/migrations/2018-05-12-001226_init/up.sql
+++ b/rust/migrations/2018-05-12-001226_init/up.sql
@@ -59,8 +59,8 @@ CREATE TABLE creator_rev (
-- Could denormalize a "is_live" flag into revision tables, to make indices
-- more efficient
-CREATE INDEX creator_rev_orcid_idx ON creator_rev(orcid) WHERE orcid IS NOT NULL;
-CREATE INDEX creator_rev_wikidata_idx ON creator_rev(wikidata_qid) WHERE wikidata_qid IS NOT NULL;
+CREATE INDEX creator_rev_orcid_idx ON creator_rev(orcid);
+CREATE INDEX creator_rev_wikidata_idx ON creator_rev(wikidata_qid);
CREATE TABLE creator_ident (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
@@ -97,8 +97,8 @@ CREATE TABLE container_rev (
coden TEXT
);
-CREATE INDEX container_rev_issnl_idx ON container_rev(issnl) WHERE issnl IS NOT NULL;
-CREATE INDEX container_rev_wikidata_idx ON container_rev(wikidata_qid) WHERE wikidata_qid IS NOT NULL;
+CREATE INDEX container_rev_issnl_idx ON container_rev(issnl);
+CREATE INDEX container_rev_wikidata_idx ON container_rev(wikidata_qid);
CREATE TABLE container_ident (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
@@ -134,9 +134,9 @@ CREATE TABLE file_rev (
mimetype TEXT
);
-CREATE INDEX file_rev_sha1_idx ON file_rev(sha1) WHERE sha1 IS NOT NULL;
-CREATE INDEX file_rev_md5_idx ON file_rev(md5) WHERE md5 IS NOT NULL;
-CREATE INDEX file_rev_sha256_idx ON file_rev(sha256) WHERE sha256 IS NOT NULL;
+CREATE INDEX file_rev_sha1_idx ON file_rev(sha1);
+CREATE INDEX file_rev_md5_idx ON file_rev(md5);
+CREATE INDEX file_rev_sha256_idx ON file_rev(sha256);
CREATE TABLE file_rev_url (
id BIGSERIAL PRIMARY KEY,
@@ -195,13 +195,13 @@ CREATE TABLE release_rev (
-- TODO: identifier table?
);
-CREATE INDEX release_rev_doi_idx ON release_rev(doi) WHERE doi IS NOT NULL;
-CREATE INDEX release_rev_pmid_idx ON release_rev(pmid) WHERE pmid IS NOT NULL;
-CREATE INDEX release_rev_pmcid_idx ON release_rev(pmcid) WHERE pmcid IS NOT NULL;
-CREATE INDEX release_rev_wikidata_idx ON release_rev(wikidata_qid) WHERE wikidata_qid IS NOT NULL;
-CREATE INDEX release_rev_isbn13_idx ON release_rev(isbn13) WHERE isbn13 IS NOT NULL;
-CREATE INDEX release_rev_core_idx ON release_rev(core_id) WHERE core_id IS NOT NULL;
-CREATE INDEX release_rev_work_idx ON release_rev(work_ident_id) WHERE work_ident_id IS NOT NULL;
+CREATE INDEX release_rev_doi_idx ON release_rev(doi);
+CREATE INDEX release_rev_pmid_idx ON release_rev(pmid);
+CREATE INDEX release_rev_pmcid_idx ON release_rev(pmcid);
+CREATE INDEX release_rev_wikidata_idx ON release_rev(wikidata_qid);
+CREATE INDEX release_rev_isbn13_idx ON release_rev(isbn13);
+CREATE INDEX release_rev_core_idx ON release_rev(core_id);
+CREATE INDEX release_rev_work_idx ON release_rev(work_ident_id);
CREATE TABLE release_rev_abstract (
id BIGSERIAL PRIMARY KEY,