summaryrefslogtreecommitdiff
path: root/lib/VNDB
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2020-06-07 10:40:26 +0200
committerYorhel <git@yorhel.nl>2020-06-07 10:40:28 +0200
commite75a6195e05b4d8b71e25280101d8af4b0d7fb6c (patch)
tree32b0cfbe690d2cda666a1dae4272e4819d5e0a84 /lib/VNDB
parent946bed68c9376e4593bd6599000cab4d3d30a5cf (diff)
DB::VN: Fetch random homepage screenshots using TABLESAMPLE
For some reason the old query has gotten really slow (>300ms) in production after 50b9a5b7a59e896ed4a1f65141a31b8e64c33b55. It still ran in like 30ms on my dev setup, Postgres somehow found it necessary to switch to a different query plan. This new query also uses the new image flagging votes to determine which screenshots should be visible. Just happened to be easier and faster to implement.
Diffstat (limited to 'lib/VNDB')
-rw-r--r--lib/VNDB/DB/VN.pm34
1 files changed, 20 insertions, 14 deletions
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index d0c9caa5..1f12d0c8 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -3,6 +3,7 @@ package VNDB::DB::VN;
use strict;
use warnings;
+use v5.10;
use TUWF 'sqlprint';
use POSIX 'strftime';
use Exporter 'import';
@@ -325,20 +326,25 @@ sub dbScreenshotGet {
# if any arguments are given, it will return one random screenshot for each VN
sub dbScreenshotRandom {
my($self, @vids) = @_;
- # Assumption: vndbid_num(id) for screenshots doesn't have ~too~ many gaps (less than, say, 80%)
- return $self->dbAll(q|
- SELECT vndbid_num(s.id) AS scr, s.width, s.height, v.id AS vid, v.title
- FROM images s
- JOIN vn_screenshots vs ON vs.scr = s.id
- JOIN vn v ON v.id = vs.id
- WHERE NOT v.hidden AND NOT vs.nsfw
- AND s.id IN(
- SELECT vndbid('sf', floor(1 + random() * (select last_value from screenshots_seq))::int)
- FROM generate_series(1,20)
- LIMIT 20
- )
- LIMIT 4|
- ) if !@vids;
+ if(!@vids) {
+ my $where = q{c_weight > 0 and vndbid_type(id) = 'sf' and c_sexual_avg < 0.4 and c_violence_avg < 0.4};
+ state $stats ||= $self->dbRow("SELECT count(*) as total, count(*) filter(where $where) as subset from images");
+ my $sample = 100*List::Util::min(1, (1000 / $stats->{subset}) * ($stats->{total} / $stats->{subset}));
+ return $self->dbAll(q{
+ SELECT vndbid_num(i.id) AS scr, i.width, i.height, v.id AS vid, v.title
+ FROM (
+ SELECT id, width, height
+ FROM images TABLESAMPLE SYSTEM (?)
+ WHERE c_weight > 0 and vndbid_type(id) = 'sf' and c_sexual_avg < 0.4 and c_violence_avg < 0.4
+ ORDER BY random()
+ LIMIT 4
+ ) i(id)
+ JOIN vn_screenshots vs ON vs.scr = i.id
+ JOIN vn v ON v.id = vs.id
+ ORDER BY random()
+ LIMIT 4
+ }, $sample);
+ }
# this query is faster than it looks
return $self->dbAll(join(' UNION ALL ', map