From e75a6195e05b4d8b71e25280101d8af4b0d7fb6c Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Sun, 7 Jun 2020 10:40:26 +0200
Subject: 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.
---
 lib/VNDB/DB/VN.pm | 34 ++++++++++++++++++++--------------
 1 file changed, 20 insertions(+), 14 deletions(-)

(limited to 'lib/VNDB')

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
-- 
cgit v1.2.3