On 4/22/16, Dimitris Bilidas <d.bilidas at di.uoa.gr> wrote: > > The query is: > SELECT count(qview1."wlbWellboreName") FROM "discovery" qview2 CROSS > JOIN "wellbore_development_all" qview1 WHERE > (qview1."wlbNpdidWellbore" = qview2."wlbNpdidWellbore");
You are aware that SQLite uses the CROSS JOIN syntax as a way of controlling the query planner, and limiting the number of options that the query planner looks at, right? (See https://www.sqlite.org/optoverview.html#crossjoin for more information.) I don't think this is important here, but it might be. > > If I > replace qview1."wlbWellboreName" with * it runs in a couple of seconds > on cold cache. If I create the table "wellbore_development_all" from the > beginning setting wlbNpdidWellbore as primary key, the query runs in > under 3 seconds on cold cache and in a couple of tens of milliseconds on > warm cache. This sounds to me like you have already solved your problem, no? Just use an INTEGER PRIMARY KEY rather than a separate index. > > I would normally expect that > the difference would be very small: one more page fetch for each value > of wlbNpdidWellbore that it is matched in the index of > wellbore_development_all A single page fetch can take a long time when the cache is cold. The appropriate disk sector needs to rotate under the read head, which on a 3600 RPM disk drive takes an average of 8 millisecond. Multiply by the number of pages that need to be fetched (hundreds of thousands according to your ANALYZE stats) and that can be slow. -- D. Richard Hipp drh at sqlite.org
