path: root/util
diff options
authorYorhel <>2015-10-24 12:26:36 +0200
committerYorhel <>2015-10-24 12:26:36 +0200
commit173ac8de5d96957dcc93fae5e36f6df2f9ce7118 (patch)
treee60b52d645ac23d9bec02e4107933a1f0fd9cd52 /util
parentd5456b8ed5952b9fa061f0de536fab572c95016a (diff)
Improve several discussion board SQL queries
An index on was necessary to speed up some very common "recent posts" queries on both the homepage and the thread index. Postgres thought that the same index could be used to speed up the full-text search (because it's ordered by date, after all), but that completely killed performance. That was solved with a bb_tsvector() wrapper to tell the query planner that not using the full-text index is incredibly show, which in turn improved the search performance beyond what it was. Many thread-related queries are still somewhat slow, but that seems to be a limitation in the schema. I'll just keep monitoring to see if that's worth fixing in the future. Interestingly, dbThreadCount() needs to use a sequential scan, but it's still remarkably fast.
Diffstat (limited to 'util')
3 files changed, 13 insertions, 2 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql
index e5e12eab..125455a2 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -15,6 +15,14 @@ CREATE OR REPLACE FUNCTION strip_bb_tags(t text) RETURNS text AS $$
SELECT regexp_replace(t, '\[(?:url=[^\]]+|/?(?:spoiler|quote|raw|code|url))\]', ' ', 'gi');
+-- Wrapper around to_tsvector() and strip_bb_tags(), implemented in plpgsql and
+-- with an associated cost function to make it opaque to the query planner and
+-- ensure the query planner realizes that this function is _slow_.
+CREATE OR REPLACE FUNCTION bb_tsvector(t text) RETURNS tsvector AS $$
+ RETURN to_tsvector('english', strip_bb_tags(t));
-- BUG: Since this isn't a full bbcode parser, [spoiler] tags inside [raw] or [code] are still considered spoilers.
CREATE OR REPLACE FUNCTION strip_spoilers(t text) RETURNS text AS $$
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index d9b0f4c5..5b9ca1ae 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -101,7 +101,9 @@ CREATE INDEX staff_alias_id ON staff_alias (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_vid ON tags_vn (vid);
-CREATE INDEX threads_posts_ts ON threads_posts USING gin(to_tsvector('english', strip_bb_tags(msg)));
+CREATE INDEX threads_posts_ts ON threads_posts USING gin(bb_tsvector(msg));
+CREATE INDEX threads_posts_date ON threads_posts (date);
+CREATE INDEX threads_posts_uid ON threads_posts (uid); -- Only really used on /u+ pages to get stats
CREATE INDEX vn_staff_aid ON vn_staff (aid);
CREATE INDEX vn_seiyuu_aid ON vn_seiyuu (aid);
CREATE UNIQUE INDEX changes_itemrev ON changes (type, itemid, rev);
diff --git a/util/updates/update_2.25-sqlsplit.sql b/util/updates/update_2.25-sqlsplit.sql
index 9104972a..b7ffa91f 100644
--- a/util/updates/update_2.25-sqlsplit.sql
+++ b/util/updates/update_2.25-sqlsplit.sql
@@ -230,7 +230,7 @@ DROP TABLE vn_screenshots_old CASCADE;
DROP TABLE vn_seiyuu_old CASCADE;
DROP TABLE vn_staff_old CASCADE;
-\i util/sql/tableattrs.sql
+DROP INDEX threads_posts_ts;
DROP FUNCTION edit_revtable(dbentry_type, integer);
DROP FUNCTION edit_vn_init(integer);
@@ -254,4 +254,5 @@ CREATE TYPE edit_rettype AS (itemid integer, chid integer, rev integer);
\i util/sql/func.sql
\i util/sql/editfunc.sql
+\i util/sql/tableattrs.sql
\i util/sql/triggers.sql