From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: X-Spam-Checker-Version: SpamAssassin 3.4.1 (2015-04-28) on dcvr.yhbt.net X-Spam-Level: X-Spam-ASN: AS3215 2.6.0.0/16 X-Spam-Status: No, score=-3.2 required=3.0 tests=AWL,BAYES_00,DKIM_SIGNED, DKIM_VALID,DKIM_VALID_AU,RCVD_IN_DNSWL_NONE,SPF_PASS shortcircuit=no autolearn=ham autolearn_force=no version=3.4.1 Received: from mail-qk0-x242.google.com (mail-qk0-x242.google.com [IPv6:2607:f8b0:400d:c09::242]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by dcvr.yhbt.net (Postfix) with ESMTPS id AA68F1F403 for ; Mon, 18 Jun 2018 17:51:01 +0000 (UTC) Received: by mail-qk0-x242.google.com with SMTP id c131-v6so9854743qkb.0 for ; Mon, 18 Jun 2018 10:51:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=linuxfoundation.org; s=google; h=date:from:to:subject:message-id:mime-version:content-disposition :user-agent; bh=EfuWETKTVsBa7e+seCOIT77s7CLI/O5vbtyZdUz3R40=; b=fYi13z4K7Smqp9fZZ0DjgYOb2/npI/wt/byETMPfb1lv68p/CBwYFNPDCpguOzrFjd ovfwnXLXFsEBF85cWqV7b7w8QxE8fL9nZrh2OiakwMy5KLNhhG+nbKqv7euMPKo4VPMn LPKEji7nNXA3XkirKQTLj+kKaCqK6bAo+I3mM= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:subject:message-id:mime-version :content-disposition:user-agent; bh=EfuWETKTVsBa7e+seCOIT77s7CLI/O5vbtyZdUz3R40=; b=CCv9LcX5XxkKbdR24YThmLkMk3D9xJhSkrZ0aRLtRdqK4t6Gl1DsQuEU7urn5HCQqC LQzhjy/1qmyLOEXOO3eto2IXxXhyUpg758YVPge3+iLk6C9tI15hxI3hT2m6b5MbB56V yDzE0CPNKx/C9XTn9fnvS2upAQFZPpjbvjSJEWG4rzNAZax9Mxq8wbdO0rWgvhMGqUdR 6kaVVhKahivXcAiefjHULVWiTF6HpONL0aKmJ7zCh94VwS8l5I9VaCwmesCyMsieyqHd jMJ8Cuuxxcyw2gC4Nbq5sUoQA7N6Ionad4hlh9DKIrkyo8+3GVAF5BnrFeTALWj8JBWe uQRg== X-Gm-Message-State: APt69E3zmvjFp8eOaLekcatdjict6SXZbjkD/2UX28ITaOIqMgMhRGJ+ HEWo4t1FLgMmqxMZX5YZ9SnH4BupsZw= X-Google-Smtp-Source: ADUXVKLtZ0L5kex1x4oBMa3PHCopUaOelczaNIS1VBm6zD5UYs2hUBqdpXPVXKoct3e/agaAyxjmzQ== X-Received: by 2002:a37:5a43:: with SMTP id o64-v6mr11409674qkb.211.1529344259453; Mon, 18 Jun 2018 10:50:59 -0700 (PDT) Received: from work (modemcable221.121-21-96.mc.videotron.ca. [96.21.121.221]) by smtp.gmail.com with ESMTPSA id h126-v6sm10047223qke.50.2018.06.18.10.50.57 for (version=TLS1_2 cipher=ECDHE-RSA-CHACHA20-POLY1305 bits=256/256); Mon, 18 Jun 2018 10:50:58 -0700 (PDT) Date: Mon, 18 Jun 2018 13:51:02 -0400 From: Konstantin Ryabitsev To: meta@public-inbox.org Subject: [PATCH] Tweak over.sqlite3 queries for sqlite < 3.8 Message-ID: <20180618175102.GA20610@work> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="fdj2RfSjLxBAspz7" Content-Disposition: inline User-Agent: Mutt/1.10.0 (2018-05-17) List-Id: --fdj2RfSjLxBAspz7 Content-Type: text/plain; charset=utf-8; format=flowed Content-Disposition: inline Content-Transfer-Encoding: quoted-printable The query planner in sqlite3 < 3.8 is not very clever, so when it sees num mentioned in the query filter, it decides not to use the fast idx_ts index and goes for the much slower autoindex. CentOS-7 still has=20 sqlite-3.7, so loading the http landing page of a very large archive=20 (LKML) was taking over 18 seconds, as oppposed to milliseconds on a=20 system with sqlite-3.8 and above: $ time sqlite3 -line over.sqlite3 'SELECT ts,ds,ddd FROM over \ WHERE num > 0 ORDER BY ts DESC LIMIT 1000;' > /dev/null real 0m19.610s user 0m17.805s sys 0m1.805s $ sqlite3 -line over.sqlite3 'EXPLAIN QUERY PLAN SELECT ts,ds,ddd \ FROM over WHERE num > 0 ORDER BY ts DESC LIMIT 1000;' selectid =3D 0 order =3D 0 =66rom =3D 0 detail =3D SEARCH TABLE over USING INDEX sqlite_autoindex_over_1 (num>?) (~= 250000 rows) However, if we slightly tweak the query per SQlite recommendations [1] by adding + to the num filter, we force it to use the correct index and see much faster performance: $ time sqlite3 -line over.sqlite3 'SELECT ts,ds,ddd FROM over \ WHERE +num > 0 ORDER BY ts DESC LIMIT 1000;' > /dev/null real 0m0.007s user 0m0.005s sys 0m0.002s $ sqlite3 -line over.sqlite3 'EXPLAIN QUERY PLAN SELECT ts,ds,ddd \ FROM over WHERE +num > 0 ORDER BY ts DESC LIMIT 1000;' selectid =3D 0 order =3D 0 =66rom =3D 0 detail =3D SCAN TABLE over USING INDEX idx_ts (~1464303 rows) This appears to be the only place where this is needed in order to avoid running into this issue.=20 As far as I can tell, this change has no impact on systems running newer=20 sqlite3 (>=3D 3.8). =2E. [1] https://sqlite.org/optoverview.html#disqualifying_where_clause_ter= ms_using_unary_ Signed-off-by: Konstantin Ryabitsev --- lib/PublicInbox/Over.pm | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) diff --git a/lib/PublicInbox/Over.pm b/lib/PublicInbox/Over.pm index 30f2603..b2f6883 100644 --- a/lib/PublicInbox/Over.pm +++ b/lib/PublicInbox/Over.pm @@ -120,18 +120,18 @@ sub recent { my ($s, @v); if (defined($before)) { if (defined($after)) { - $s =3D 'num > 0 AND ts >=3D ? AND ts <=3D ? ORDER BY ts DESC'; + $s =3D '+num > 0 AND ts >=3D ? AND ts <=3D ? ORDER BY ts DESC'; @v =3D ($after, $before); } else { - $s =3D 'num > 0 AND ts <=3D ? ORDER BY ts DESC'; + $s =3D '+num > 0 AND ts <=3D ? ORDER BY ts DESC'; @v =3D ($before); } } else { if (defined($after)) { - $s =3D 'num > 0 AND ts >=3D ? ORDER BY ts ASC'; + $s =3D '+num > 0 AND ts >=3D ? ORDER BY ts ASC'; @v =3D ($after); } else { - $s =3D 'num > 0 ORDER BY ts DESC'; + $s =3D '+num > 0 ORDER BY ts DESC'; } } my $msgs =3D do_get($self, <<"", $opts, @v); --=20 2.17.1 --fdj2RfSjLxBAspz7 Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iHUEABYIAB0WIQR2vl2yUnHhSB5njDW2xBzjVmSZbAUCWyfxBAAKCRC2xBzjVmSZ bAVxAP4p6L368DqsxrMd6oTXhZOZLD8aKz3Ja7jmYcCuktA+XAD/dwzZaSqIIS3e lKh3fDV7Ezl3JeSjVhazgOUN80ayJQk= =4nV+ -----END PGP SIGNATURE----- --fdj2RfSjLxBAspz7--