summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2015-10-24 12:26:36 +0200
committerYorhel <git@yorhel.nl>2015-10-24 12:26:36 +0200
commit173ac8de5d96957dcc93fae5e36f6df2f9ce7118 (patch)
treee60b52d645ac23d9bec02e4107933a1f0fd9cd52
parentd5456b8ed5952b9fa061f0de536fab572c95016a (diff)
Improve several discussion board SQL queries
An index on threads_posts.date 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.
-rw-r--r--lib/VNDB/DB/Discussions.pm22
-rw-r--r--util/sql/func.sql8
-rw-r--r--util/sql/tableattrs.sql4
-rw-r--r--util/updates/update_2.25-sqlsplit.sql3
4 files changed, 27 insertions, 10 deletions
diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm
index 16d33c4a..79cf7b39 100644
--- a/lib/VNDB/DB/Discussions.pm
+++ b/lib/VNDB/DB/Discussions.pm
@@ -23,11 +23,11 @@ sub dbThreadGet {
!$o{id} ? (
't.hidden = FALSE' => 0 ) : (),
$o{type} && !$o{iid} ? (
- 't.id IN(SELECT tid FROM threads_boards WHERE type IN(!l))' => [ ref $o{type} ? $o{type} : [ $o{type} ] ] ) : (),
+ 'EXISTS(SELECT 1 FROM threads_boards WHERE tid = t.id AND type IN(!l))' => [ ref $o{type} ? $o{type} : [ $o{type} ] ] ) : (),
$o{type} && $o{iid} ? (
'tb.type = ?' => $o{type}, 'tb.iid = ?' => $o{iid} ) : (),
$o{notusers} ? (
- 't.id NOT IN(SELECT tid FROM threads_boards WHERE type = \'u\')' => 1) : (),
+ 't.id <> ANY(ARRAY(SELECT tid FROM threads_boards WHERE type = \'u\'))' => 1) : (),
);
if($o{search}) {
@@ -39,15 +39,10 @@ sub dbThreadGet {
my @select = (
qw|t.id t.title t.count t.locked t.hidden|,
- $o{what} =~ /firstpost/ ? ('tpf.uid AS fuid', q|EXTRACT('epoch' from tpf.date) AS fdate|, 'uf.username AS fusername') : (),
$o{what} =~ /lastpost/ ? ('tpl.uid AS luid', q|EXTRACT('epoch' from tpl.date) AS ldate|, 'ul.username AS lusername') : (),
);
my @join = (
- $o{what} =~ /firstpost/ ? (
- 'JOIN threads_posts tpf ON tpf.tid = t.id AND tpf.num = 1',
- 'JOIN users uf ON uf.id = tpf.uid'
- ) : (),
$o{what} =~ /lastpost/ ? (
'JOIN threads_posts tpl ON tpl.tid = t.id AND tpl.num = t.count',
'JOIN users ul ON ul.id = tpl.uid'
@@ -84,6 +79,17 @@ sub dbThreadGet {
[ keys %r ]
)});
}
+
+ if($o{what} =~ /firstpost/) {
+ do { my $x = $r->[$r{$_->{tid}}]; $x->{fuid} = $_->{uid}; $x->{fdate} = $_->{date}; $x->{fusername} = $_->{username} } for (@{$self->dbAll(q|
+ SELECT tpf.tid, tpf.uid, EXTRACT('epoch' from tpf.date) AS date, uf.username
+ FROM threads_posts tpf
+ JOIN users uf ON tpf.uid = uf.id
+ WHERE tpf.num = 1 AND tpf.tid IN(!l)|,
+ [ keys %r ]
+ )});
+ }
+
if($o{what} =~ /boardtitles/) {
push(@{$r->[$r{$_->{tid}}]{boards}}, $_) for (@{$self->dbAll(q|
SELECT tb.tid, tb.type, tb.iid, COALESCE(u.username, v.title, p.name) AS title, COALESCE(u.username, v.original, p.original) AS original
@@ -185,7 +191,7 @@ sub dbPostGet {
$o{hide} && $o{what} =~ /thread/ ? (
't.hidden = FALSE' => 1 ) : (),
$o{search} ? (
- q{to_tsvector('english', strip_bb_tags(msg)) @@ to_tsquery(?)} => $o{search}) : (),
+ 'bb_tsvector(msg) @@ to_tsquery(?)' => $o{search}) : (),
$o{type} ? (
'tp.tid = ANY(ARRAY(SELECT tid FROM threads_boards WHERE type IN(!l)))' => [ ref $o{type} ? $o{type} : [ $o{type} ] ] ) : (),
);
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');
$$ LANGUAGE sql IMMUTABLE;
+-- 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 $$
+BEGIN
+ RETURN to_tsvector('english', strip_bb_tags(t));
+END;
+$$ LANGUAGE plpgsql IMMUTABLE COST 500;
-- 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