On Thu, Sep 18, 2014 at 8:54 AM, Merike <gas...@smail.ee> wrote: > Hi everyone, > > Since upgrading to Kubuntu 14.04 I've had an issue with Quassel irc > client being slow on startup when it retrieves backlog from database. > I've tracked it down to different sqlite version being installed. > Previously I had 3.7.17 and now have 3.8.2. I've tried various versions > from https://launchpad.net/ubuntu/trusty/+source/sqlite3/+builds and can > only pinpoint it to between 3.7.17 and 3.8.0.2 because there doesn't > appear to be intermediate builds. >
Thanks for the performance regression report. A simple script to reproduce the problem in the latest SQLite is as follows: CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER, d INTEGER); CREATE INDEX t1b ON t1(b); CREATE TABLE t2(x INTEGER PRIMARY KEY, y); explain query plan SELECT * FROM t1, t2 WHERE x=c AND b=?1 ORDER BY a; .print ---------------------- CREATE INDEX t1bd ON t1(b,d); explain query plan SELECT * FROM t1, t2 WHERE x=c AND b=?1 ORDER BY a; If you copy/paste the above script into an sqlite3 command-line shell, you'll see that, for some reason, the query planner decides to sort manually rather than using an index for sorting after the t1bd index is added. We are still investigating to try understand why that is. Please note that if you run ANALYZE on your sample database, the query is fast again. On my (Ubuntu) desktop, the query takes 648 milliseconds before being analyzed and 600 microseconds afterwards - a 1000-fold speedup. > > I don't know what exact queries Quassel runs but most likely (after > looking at some Quassel source on github) it's something like the > following for a single channel: > SELECT messageid, time, type, flags, sender, message > FROM backlog > JOIN sender ON backlog.senderid = sender.senderid > WHERE bufferid = 102 > ORDER BY messageid DESC > LIMIT 100; > > When I run this query on 3.7.17 it returns nearly instantly. When I do > it on 3.8.0.2 or 3.8.6 which I also tried then it takes about 4 seconds > on my not so fast spinning drive laptop. I've posted example database > (minimized from original) at > https://docs.google.com/file/d/0Bzx3gCDqfzVdcDNhdzlfVlh4ZTA/. Could > someone either confirm or prove false that this query has become slower > with newer versions? > > Merike > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
