[src/trunk]: src/usr.sbin/makemandb PR misc/54213: Fix performance of whatis(...

[abhinav <abhinav%NetBSD.org@localhost>]

details:   https://anonhg.NetBSD.org/src/rev/2aa9180a28ad
branches:  trunk
changeset: 451363:2aa9180a28ad
user:      abhinav <abhinav%NetBSD.org@localhost>
date:      Sat May 18 07:56:43 2019 +0000

description:
PR misc/54213: Fix performance of whatis(1) when no matches are found

In revision 1.6 of whatis.c the query was modified to return matches for names found
in MLINKS of the man pages as well. However it was slow. The reason probably being that it
required a join. But more importantly the where condition on an FTS virtual table column
is very slow. To avoid the join and the expensive where condition on the virtual table,
add the name_desc column to the mandb_links table as well. This improves the performance
of whatis(1) to the original level at the expense of slight data duplication.

Bump the schema to force database rebuild to take account for the new column addition

diffstat:

 usr.sbin/makemandb/apropos-utils.c |   6 +++---
 usr.sbin/makemandb/apropos-utils.h |   4 ++--
 usr.sbin/makemandb/makemandb.c     |   8 ++++----
 usr.sbin/makemandb/whatis.c        |  30 +++++++++++++-----------------
 4 files changed, 22 insertions(+), 26 deletions(-)

diffs (129 lines):

diff -r 255df2d6ca27 -r 2aa9180a28ad usr.sbin/makemandb/apropos-utils.c
--- a/usr.sbin/makemandb/apropos-utils.c        Sat May 18 07:49:31 2019 +0000
+++ b/usr.sbin/makemandb/apropos-utils.c        Sat May 18 07:56:43 2019 +0000
@@ -1,4 +1,4 @@
-/*     $NetBSD: apropos-utils.c,v 1.43 2019/04/19 20:35:13 abhinav Exp $       */
+/*     $NetBSD: apropos-utils.c,v 1.44 2019/05/18 07:56:43 abhinav Exp $       */
 /*-
  * Copyright (c) 2011 Abhinav Upadhyay <er.abhinav.upadhyay%gmail.com@localhost>
  * All rights reserved.
@@ -31,7 +31,7 @@
  */
 
 #include <sys/cdefs.h>
-__RCSID("$NetBSD: apropos-utils.c,v 1.43 2019/04/19 20:35:13 abhinav Exp $");
+__RCSID("$NetBSD: apropos-utils.c,v 1.44 2019/05/18 07:56:43 abhinav Exp $");
 
 #include <sys/queue.h>
 #include <sys/stat.h>
@@ -213,7 +213,7 @@
                "file UNIQUE, md5_hash UNIQUE, id  INTEGER PRIMARY KEY); "
            //mandb_links
            "CREATE TABLE IF NOT EXISTS mandb_links(link COLLATE NOCASE, target, section, "
-               "machine, md5_hash); ";
+               "machine, md5_hash, name_desc); ";
 
        sqlite3_exec(db, sqlstr, NULL, NULL, &errmsg);
        if (errmsg != NULL)
diff -r 255df2d6ca27 -r 2aa9180a28ad usr.sbin/makemandb/apropos-utils.h
--- a/usr.sbin/makemandb/apropos-utils.h        Sat May 18 07:49:31 2019 +0000
+++ b/usr.sbin/makemandb/apropos-utils.h        Sat May 18 07:56:43 2019 +0000
@@ -1,4 +1,4 @@
-/*     $NetBSD: apropos-utils.h,v 1.14 2017/11/25 14:29:38 abhinav Exp $       */
+/*     $NetBSD: apropos-utils.h,v 1.15 2019/05/18 07:56:43 abhinav Exp $       */
 /*-
  * Copyright (c) 2011 Abhinav Upadhyay <er.abhinav.upadhyay%gmail.com@localhost>
  * All rights reserved.
@@ -45,7 +45,7 @@
 } mandb_access_mode;
 
 
-#define APROPOS_SCHEMA_VERSION 20170618
+#define APROPOS_SCHEMA_VERSION 20190518
 
 /*
  * Used to identify the section of a man(7) page.
diff -r 255df2d6ca27 -r 2aa9180a28ad usr.sbin/makemandb/makemandb.c
--- a/usr.sbin/makemandb/makemandb.c    Sat May 18 07:49:31 2019 +0000
+++ b/usr.sbin/makemandb/makemandb.c    Sat May 18 07:56:43 2019 +0000
@@ -1,4 +1,4 @@
-/*     $NetBSD: makemandb.c,v 1.59 2019/03/11 00:31:36 christos Exp $  */
+/*     $NetBSD: makemandb.c,v 1.60 2019/05/18 07:56:43 abhinav Exp $   */
 /*
  * Copyright (c) 2011 Abhinav Upadhyay <er.abhinav.upadhyay%gmail.com@localhost>
  * Copyright (c) 2011 Kristaps Dzonsons <kristaps%bsd.lv@localhost>
@@ -17,7 +17,7 @@
  */
 
 #include <sys/cdefs.h>
-__RCSID("$NetBSD: makemandb.c,v 1.59 2019/03/11 00:31:36 christos Exp $");
+__RCSID("$NetBSD: makemandb.c,v 1.60 2019/05/18 07:56:43 abhinav Exp $");
 
 #include <sys/stat.h>
 #include <sys/types.h>
@@ -1767,9 +1767,9 @@
                                ln[strlen(ln) - 1] = 0;
 
                        str = sqlite3_mprintf("INSERT INTO mandb_links"
-                                             " VALUES (%Q, %Q, %Q, %Q, %Q)",
+                                             " VALUES (%Q, %Q, %Q, %Q, %Q, %Q)",
                                              ln, rec->name, rec->section,
-                                             rec->machine, rec->md5_hash);
+                                             rec->machine, rec->md5_hash, rec->name_desc);
                        sqlite3_exec(db, str, NULL, NULL, &errmsg);
                        sqlite3_free(str);
                        if (errmsg != NULL) {
diff -r 255df2d6ca27 -r 2aa9180a28ad usr.sbin/makemandb/whatis.c
--- a/usr.sbin/makemandb/whatis.c       Sat May 18 07:49:31 2019 +0000
+++ b/usr.sbin/makemandb/whatis.c       Sat May 18 07:56:43 2019 +0000
@@ -1,4 +1,4 @@
-/*     $NetBSD: whatis.c,v 1.7 2017/05/23 15:27:54 abhinav Exp $       */
+/*     $NetBSD: whatis.c,v 1.8 2019/05/18 07:56:43 abhinav Exp $       */
 /*-
  * Copyright (c) 2012 Joerg Sonnenberger <joerg%NetBSD.org@localhost>
  * All rights reserved.
@@ -29,7 +29,7 @@
  */
 
 #include <sys/cdefs.h>
-__RCSID("$NetBSD: whatis.c,v 1.7 2017/05/23 15:27:54 abhinav Exp $");
+__RCSID("$NetBSD: whatis.c,v 1.8 2019/05/18 07:56:43 abhinav Exp $");
 
 #include <err.h>
 #include <stdio.h>
@@ -48,24 +48,20 @@
 static int
 whatis(sqlite3 *db, const char *cmd)
 {
-       static const char sqlstr[] = "SELECT name, section, name_desc"
-               " FROM mandb WHERE name MATCH ? AND name=? COLLATE NOCASE"
-               " UNION"
-               " SELECT b.link AS name, b.section, a.name_desc FROM mandb a"
-               " JOIN"
-               " mandb_links b ON a.name=b.target AND a.section=b.section WHERE b.link=?"
-               " GROUP BY a.name, a.section, a.name_desc ORDER BY section, name";
+       static const char sqlstr[] = "SELECT link AS name, section, name_desc"
+               " FROM mandb_links WHERE link=? UNION SELECT name, section, name_desc"
+               " FROM mandb WHERE name MATCH ? AND name=? ORDER BY section";
        sqlite3_stmt *stmt = NULL;
        int retval;
-
+       int i;
        if (sqlite3_prepare_v2(db, sqlstr, -1, &stmt, NULL) != SQLITE_OK)
-               errx(EXIT_FAILURE, "Unable to query database");
-       if (sqlite3_bind_text(stmt, 1, cmd, -1, NULL) != SQLITE_OK)
-               errx(EXIT_FAILURE, "Unable to query database");
-       if (sqlite3_bind_text(stmt, 2, cmd, -1, NULL) != SQLITE_OK)
-               errx(EXIT_FAILURE, "Unable to query database");
-       if (sqlite3_bind_text(stmt, 3, cmd, -1, NULL) != SQLITE_OK)
-               errx(EXIT_FAILURE, "Unable to query database");
+               errx(EXIT_FAILURE, "%s", sqlite3_errmsg(db));
+
+       for (i = 0; i < 3; i++) {
+               if (sqlite3_bind_text(stmt, i + 1, cmd, -1, NULL) != SQLITE_OK)
+                       errx(EXIT_FAILURE, "%s", sqlite3_errmsg(db));
+       }
+
        retval = 1;
        while (sqlite3_step(stmt) == SQLITE_ROW) {
                printf("%s(%s) - %s\n", sqlite3_column_text(stmt, 0),