summaryrefslogtreecommitdiff
path: root/lib/VNDB
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 /lib/VNDB
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.
Diffstat (limited to 'lib/VNDB')
-rw-r--r--lib/VNDB/DB/Discussions.pm22
1 files changed, 14 insertions, 8 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} ] ] ) : (),
);