summaryrefslogtreecommitdiff
path: root/sql/tableattrs.sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql/tableattrs.sql')
-rw-r--r--sql/tableattrs.sql150
1 files changed, 95 insertions, 55 deletions
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index f984a4e4..a707bf50 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -1,6 +1,68 @@
+-- Indices
+
+CREATE INDEX chars_main ON chars (main) WHERE main IS NOT NULL AND NOT hidden; -- Only used on /c+
+CREATE INDEX chars_vns_vid ON chars_vns (vid);
+CREATE INDEX chars_image ON chars (image);
+CREATE INDEX chars_traits_tid ON chars_traits (tid);
+CREATE UNIQUE INDEX drm_name ON drm (name);
+CREATE UNIQUE INDEX image_votes_pkey ON image_votes (uid, id);
+CREATE INDEX image_votes_id ON image_votes (id);
+CREATE INDEX notifications_uid_iid ON notifications (uid,iid);
+CREATE INDEX quotes_rand ON quotes (rand) WHERE rand IS NOT NULL;
+CREATE INDEX quotes_vid ON quotes (vid);
+CREATE INDEX quotes_addedby ON quotes (addedby);
+CREATE INDEX quotes_log_id ON quotes_log (id);
+CREATE INDEX releases_released ON releases (released) WHERE NOT hidden; -- Mainly for the homepage
+CREATE INDEX releases_producers_pid ON releases_producers (pid);
+CREATE INDEX releases_vn_vid ON releases_vn (vid);
+CREATE INDEX reports_new ON reports (date) WHERE status = 'new';
+CREATE INDEX reports_lastmod ON reports (lastmod);
+CREATE INDEX reports_log_id ON reports_log (id);
+CREATE UNIQUE INDEX reviews_vid_uid ON reviews (vid,uid);
+CREATE INDEX reviews_uid ON reviews (uid);
+CREATE INDEX reviews_ts ON reviews USING gin(bb_tsvector(text));
+CREATE INDEX reviews_posts_uid ON reviews_posts (uid);
+CREATE INDEX reviews_posts_ts ON reviews_posts USING gin(bb_tsvector(msg));
+CREATE UNIQUE INDEX reviews_votes_id_uid ON reviews_votes (id,uid);
+CREATE UNIQUE INDEX reviews_votes_id_ip ON reviews_votes (id,ip);
+CREATE INDEX staff_alias_id ON staff_alias (id);
+CREATE UNIQUE INDEX tags_vn_pkey ON tags_vn (tag,vid,uid);
+CREATE UNIQUE INDEX threads_boards_pkey ON threads_boards (tid,type,iid) NULLS NOT DISTINCT;
+CREATE INDEX tags_vn_date ON tags_vn (date);
+CREATE INDEX tags_vn_direct_vid ON tags_vn_direct (vid);
+CREATE INDEX tags_vn_uid ON tags_vn (uid) WHERE uid IS NOT NULL;
+CREATE INDEX tags_vn_vid ON tags_vn (vid);
+CREATE INDEX search_cache_id ON search_cache (id);
+CREATE INDEX search_cache_label ON search_cache USING GIN (label gin_trgm_ops);
+CREATE INDEX shop_playasia__gtin ON shop_playasia (gtin);
+CREATE INDEX threads_posts_date ON threads_posts (date);
+CREATE INDEX threads_posts_ts ON threads_posts USING gin(bb_tsvector(msg));
+CREATE INDEX threads_posts_uid ON threads_posts (uid); -- Only really used on /u+ pages to get stats
+CREATE INDEX traits_chars_cid ON traits_chars (cid);
+CREATE INDEX vn_image ON vn (image);
+CREATE INDEX vn_screenshots_scr ON vn_screenshots (scr);
+CREATE INDEX vn_seiyuu_aid ON vn_seiyuu (aid); -- Only used on /s+?
+CREATE INDEX vn_seiyuu_cid ON vn_seiyuu (cid); -- Only used on /c+?
+CREATE UNIQUE INDEX vn_staff_pkey ON vn_staff (id, eid, aid, role) NULLS NOT DISTINCT;
+CREATE UNIQUE INDEX vn_staff_hist_pkey ON vn_staff_hist (chid, eid, aid, role) NULLS NOT DISTINCT;
+CREATE INDEX vn_staff_aid ON vn_staff (aid);
+CREATE UNIQUE INDEX vn_length_votes_vid_uid ON vn_length_votes (vid, uid);
+CREATE INDEX vn_length_votes_uid ON vn_length_votes (uid);
+CREATE UNIQUE INDEX changes_itemrev ON changes (itemid, rev);
+CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (id, vid, rid) NULLS NOT DISTINCT;
+CREATE UNIQUE INDEX chars_vns_hist_pkey ON chars_vns_hist (chid, vid, rid) NULLS NOT DISTINCT;
+CREATE INDEX ulist_vns_voted ON ulist_vns (vid, vote_date) WHERE vote IS NOT NULL; -- For VN recent votes & vote graph. INCLUDE(vote) speeds up vote graph even more
+CREATE UNIQUE INDEX users_username_key ON users (lower(username));
+CREATE INDEX users_ign_votes ON users (id) WHERE ign_votes;
+CREATE INDEX users_shadow_mail ON users_shadow (hash_email(mail)); -- Should be UNIQUE, but there was no duplicate check in earlier code
+
+
+
-- Constraints
ALTER TABLE changes ADD CONSTRAINT changes_requester_fkey FOREIGN KEY (requester) REFERENCES users (id) ON DELETE SET DEFAULT;
+ALTER TABLE changes_patrolled ADD CONSTRAINT changes_patrolled_id_fkey FOREIGN KEY (id) REFERENCES changes (id) ON DELETE CASCADE;
+ALTER TABLE changes_patrolled ADD CONSTRAINT changes_patrolled_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE chars ADD CONSTRAINT chars_main_fkey FOREIGN KEY (main) REFERENCES chars (id);
ALTER TABLE chars ADD CONSTRAINT chars_image_fkey FOREIGN KEY (image) REFERENCES images (id);
ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
@@ -16,6 +78,7 @@ ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_rid_fkey
ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE images ADD CONSTRAINT images_uploader_fkey FOREIGN KEY (uploader) REFERENCES users (id) ON DELETE SET DEFAULT;
ALTER TABLE image_votes ADD CONSTRAINT image_votes_id_fkey FOREIGN KEY (id) REFERENCES images (id) ON DELETE CASCADE;
ALTER TABLE image_votes ADD CONSTRAINT image_votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;
ALTER TABLE notification_subs ADD CONSTRAINT notification_subs_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
@@ -27,9 +90,21 @@ ALTER TABLE producers_relations ADD CONSTRAINT producers_relations_pid_fkey
ALTER TABLE producers_relations_hist ADD CONSTRAINT producers_relations_hist_id_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE producers_relations_hist ADD CONSTRAINT producers_relations_hist_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id);
ALTER TABLE quotes ADD CONSTRAINT quotes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE quotes ADD CONSTRAINT quotes_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
+ALTER TABLE quotes ADD CONSTRAINT quotes_addedby_fkey FOREIGN KEY (addedby) REFERENCES users (id) ON DELETE SET DEFAULT;
+ALTER TABLE quotes_log ADD CONSTRAINT quotes_log_id_fkey FOREIGN KEY (id) REFERENCES quotes (id) ON DELETE CASCADE;
+ALTER TABLE quotes_log ADD CONSTRAINT quotes_log_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;
+ALTER TABLE quotes_votes ADD CONSTRAINT quotes_votes_id_fkey FOREIGN KEY (id) REFERENCES quotes (id) ON DELETE CASCADE;
+ALTER TABLE quotes_votes ADD CONSTRAINT quotes_votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE releases ADD CONSTRAINT releases_olang_fkey FOREIGN KEY (id,olang) REFERENCES releases_titles(id,lang) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_hist ADD CONSTRAINT releases_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE releases_lang ADD CONSTRAINT releases_lang_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
-ALTER TABLE releases_lang_hist ADD CONSTRAINT releases_lang_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
+ALTER TABLE releases_hist ADD CONSTRAINT releases_hist_olang_fkey FOREIGN KEY (chid,olang)REFERENCES releases_titles_hist(chid,lang) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_drm ADD CONSTRAINT releases_drm_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
+ALTER TABLE releases_drm ADD CONSTRAINT releases_drm_drm_fkey FOREIGN KEY (drm) REFERENCES drm (id);
+ALTER TABLE releases_drm_hist ADD CONSTRAINT releases_drm_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
+ALTER TABLE releases_drm_hist ADD CONSTRAINT releases_drm_hist_drm_fkey FOREIGN KEY (drm) REFERENCES drm (id);
+ALTER TABLE releases_titles ADD CONSTRAINT releases_titles_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
+ALTER TABLE releases_titles_hist ADD CONSTRAINT releases_titles_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE releases_media ADD CONSTRAINT releases_media_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
ALTER TABLE releases_media_hist ADD CONSTRAINT releases_media_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE releases_platforms ADD CONSTRAINT releases_platforms_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
@@ -42,6 +117,8 @@ ALTER TABLE releases_vn ADD CONSTRAINT releases_vn_id_fkey
ALTER TABLE releases_vn ADD CONSTRAINT releases_vn_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE releases_vn_hist ADD CONSTRAINT releases_vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE releases_vn_hist ADD CONSTRAINT releases_vn_hist_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE reports_log ADD CONSTRAINT reports_log_id_fkey FOREIGN KEY (id) REFERENCES reports (id);
+ALTER TABLE reports_log ADD CONSTRAINT reports_log_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;
ALTER TABLE reviews ADD CONSTRAINT reviews_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id) ON DELETE CASCADE;
ALTER TABLE reviews ADD CONSTRAINT reviews_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;
ALTER TABLE reviews ADD CONSTRAINT reviews_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) ON DELETE SET DEFAULT;
@@ -53,7 +130,7 @@ ALTER TABLE rlists ADD CONSTRAINT rlists_uid_fkey
ALTER TABLE rlists ADD CONSTRAINT rlists_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
ALTER TABLE saved_queries ADD CONSTRAINT saved_queries_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE sessions ADD CONSTRAINT sessions_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE staff ADD CONSTRAINT staff_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE staff ADD CONSTRAINT staff_main_fkey FOREIGN KEY (main) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE staff ADD CONSTRAINT staff_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id);
ALTER TABLE staff_hist ADD CONSTRAINT staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE staff_hist ADD CONSTRAINT staff_hist_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id);
@@ -73,7 +150,7 @@ ALTER TABLE threads_poll_votes ADD CONSTRAINT threads_poll_votes_optid_fke
ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE;
ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;
ALTER TABLE threads_boards ADD CONSTRAINT threads_boards_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE;
-ALTER TABLE traits ADD CONSTRAINT traits_group_fkey FOREIGN KEY ("group") REFERENCES traits (id);
+ALTER TABLE traits ADD CONSTRAINT traits_gid_fkey FOREIGN KEY (gid) REFERENCES traits (id);
ALTER TABLE traits_hist ADD CONSTRAINT traits_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_id_fkey FOREIGN KEY (id) REFERENCES traits (id);
ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_parent_fkey FOREIGN KEY (parent) REFERENCES traits (id);
@@ -82,16 +159,22 @@ ALTER TABLE traits_parents_hist ADD CONSTRAINT traits_parents_hist_parent_f
ALTER TABLE ulist_labels ADD CONSTRAINT ulist_labels_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE ulist_vns ADD CONSTRAINT ulist_vns_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE ulist_vns ADD CONSTRAINT ulist_vns_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_lbl_fkey FOREIGN KEY (uid,lbl) REFERENCES ulist_labels (uid,id) ON DELETE CASCADE;
-ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_vid_fkey FOREIGN KEY (uid,vid) REFERENCES ulist_vns (uid,vid) ON DELETE CASCADE;
+ALTER TABLE users_prefs ADD CONSTRAINT users_prefs_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE users_prefs_tags ADD CONSTRAINT users_prefs_tags_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE users_prefs_tags ADD CONSTRAINT users_prefs_tags_tid_fkey FOREIGN KEY (tid) REFERENCES tags (id) ON DELETE CASCADE;
+ALTER TABLE users_prefs_traits ADD CONSTRAINT users_prefs_traits_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE users_prefs_traits ADD CONSTRAINT users_prefs_traits_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id) ON DELETE CASCADE;
ALTER TABLE users_shadow ADD CONSTRAINT users_shadow_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE users_traits ADD CONSTRAINT users_traits_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE users_traits ADD CONSTRAINT users_traits_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id);
+ALTER TABLE users_username_hist ADD CONSTRAINT users_username_hist_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE vn ADD CONSTRAINT vn_image_fkey FOREIGN KEY (image) REFERENCES images (id);
ALTER TABLE vn ADD CONSTRAINT vn_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id);
+ALTER TABLE vn ADD CONSTRAINT vn_olang_fkey FOREIGN KEY (id,olang) REFERENCES vn_titles (id,lang) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_image_fkey FOREIGN KEY (image) REFERENCES images (id);
ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id);
+ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_olang_fkey FOREIGN KEY (chid,olang)REFERENCES vn_titles_hist(chid,lang) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_aid_fkey FOREIGN KEY (aid) REFERENCES anime (id);
ALTER TABLE vn_anime_hist ADD CONSTRAINT vn_anime_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
@@ -111,53 +194,10 @@ ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_aid_fkey
ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
ALTER TABLE vn_seiyuu_hist ADD CONSTRAINT vn_seiyuu_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE vn_seiyuu_hist ADD CONSTRAINT vn_seiyuu_hist_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
-ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
+ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_id_eid_fkey FOREIGN KEY (id,eid) REFERENCES vn_editions (id,eid) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE vn_staff_hist ADD CONSTRAINT vn_staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
+ALTER TABLE vn_staff_hist ADD CONSTRAINT vn_staff_hist_chid_eid_fkey FOREIGN KEY (chid,eid) REFERENCES vn_editions_hist (chid,eid) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_titles ADD CONSTRAINT vn_titles_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
+ALTER TABLE vn_titles_hist ADD CONSTRAINT vn_titles_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE vn_length_votes ADD CONSTRAINT vn_length_votes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE vn_length_votes ADD CONSTRAINT vn_length_votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;
-
-
-
--- Indices
-
-CREATE INDEX chars_main ON chars (main) WHERE main IS NOT NULL AND NOT hidden; -- Only used on /c+
-CREATE INDEX chars_vns_vid ON chars_vns (vid);
-CREATE INDEX chars_image ON chars (image);
-CREATE UNIQUE INDEX image_votes_pkey ON image_votes (uid, id);
-CREATE INDEX image_votes_id ON image_votes (id);
-CREATE INDEX notifications_uid_iid ON notifications (uid,iid);
-CREATE INDEX releases_released ON releases (released) WHERE NOT hidden; -- Mainly for the homepage
-CREATE INDEX releases_producers_pid ON releases_producers (pid);
-CREATE INDEX releases_vn_vid ON releases_vn (vid);
-CREATE INDEX reports_new ON reports (date) WHERE status = 'new';
-CREATE INDEX reports_lastmod ON reports (lastmod);
-CREATE UNIQUE INDEX reviews_vid_uid ON reviews (vid,uid);
-CREATE INDEX reviews_uid ON reviews (uid);
-CREATE INDEX reviews_posts_uid ON reviews_posts (uid);
-CREATE UNIQUE INDEX reviews_votes_id_uid ON reviews_votes (id,uid);
-CREATE UNIQUE INDEX reviews_votes_id_ip ON reviews_votes (id,ip);
-CREATE INDEX staff_alias_id ON staff_alias (id);
-CREATE UNIQUE INDEX tags_vn_pkey ON tags_vn (tag,vid,uid);
-CREATE UNIQUE INDEX threads_boards_pkey ON threads_boards (tid,type,COALESCE(iid, 'r1')); -- 'r1' is an invalid board id
-CREATE INDEX tags_vn_date ON tags_vn (date);
-CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid);
-CREATE INDEX tags_vn_uid ON tags_vn (uid) WHERE uid IS NOT NULL;
-CREATE INDEX tags_vn_vid ON tags_vn (vid);
-CREATE INDEX shop_playasia__gtin ON shop_playasia (gtin);
-CREATE INDEX threads_posts_date ON threads_posts (date);
-CREATE INDEX threads_posts_ts ON threads_posts USING gin(bb_tsvector(msg));
-CREATE INDEX threads_posts_uid ON threads_posts (uid); -- Only really used on /u+ pages to get stats
-CREATE INDEX traits_chars_tid ON traits_chars (tid);
-CREATE INDEX vn_image ON vn (image);
-CREATE INDEX vn_screenshots_scr ON vn_screenshots (scr);
-CREATE INDEX vn_seiyuu_aid ON vn_seiyuu (aid); -- Only used on /s+?
-CREATE INDEX vn_seiyuu_cid ON vn_seiyuu (cid); -- Only used on /c+?
-CREATE INDEX vn_staff_aid ON vn_staff (aid);
-CREATE UNIQUE INDEX vn_length_votes_vid_uid ON vn_length_votes (vid, uid);
-CREATE INDEX vn_length_votes_uid ON vn_length_votes (uid);
-CREATE UNIQUE INDEX changes_itemrev ON changes (itemid, rev);
-CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (id, vid, COALESCE(rid, 'v1')); -- 'v1' is an invalid release id, but works as a 'no release specified' value in the UNIQUE qualifier.
-CREATE UNIQUE INDEX chars_vns_hist_pkey ON chars_vns_hist (chid, vid, COALESCE(rid, 'v1'));
-CREATE INDEX ulist_vns_voted ON ulist_vns (vid, vote_date) WHERE vote IS NOT NULL; -- For VN recent votes & vote graph. INCLUDE(vote) speeds up vote graph even more
-CREATE INDEX users_ign_votes ON users (id) WHERE ign_votes;