
The SQLite Query Planner - otoolep
https://www.sqlite.org/optoverview.html
======
rockdoe
The brilliance of the "SQLite Query Planner Stability Guarantee", or more
succinctly put, its deterministic query planning, cannot be overstated.

With a "real" database, if your data has an unfortunate pattern that confuses
ANALYZE statistics, you can suddenly get thousandfold slowdowns because the
query planners assume that their statistical information is perfect and do not
use any kind conservativeness in their planning. So you need a babysitter for
them: a DBA.

I've missed the predictability of SQLite when moving stuff to PostgreSQL. If
the query plan in SQLite looks good, it'll stay good. In a real database,
you're sitting on a timebomb waiting to go off when autoanalyze ends up
running at a bad time.

~~~
thrownaway2424
I always wondered why databases don't allow the direct specification of the
query plan, instead of forcing SQL developers and DBAs to try to guess the
right words that produces the right query plan, usually. It seems like it
would be a lot easier to express the query plan in, say, a protobuf, rather
than parsing a DSL on every query.

~~~
bch
This is what collecting and managing statistics[0][1] is about.

I know Oracle and Postgres implements this -- don't know about SQLServer,
MySQL, etc...

[0]
[http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats....](http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats.htm#g49431)

[1] [http://www.postgresql.org/docs/current/static/runtime-
config...](http://www.postgresql.org/docs/current/static/runtime-config-
query.html)

edit: found/added info for postgres

~~~
thrownaway2424
Sure but I don't want to influence the query plan, I want to dictate it.

------
igravious
The "The Next Generation Query Planner" (NGQP) document
[https://www.sqlite.org/queryplanner-
ng.html](https://www.sqlite.org/queryplanner-ng.html) linked to from this
document makes for very interesting reading.

In some cases upgrading from the pre-3.8.0 legacy query planner and to the
NGQP in 3.8.0 may cause a serious performance regression. Running ANALYSE on
your DB should fix that.

Apart from that the Stability Guarantee sounds, I don't know, obvious?
Repeatability and predictability under more or less the same conditions should
be a given I would have thought. In the DB world this must not necessarily be
the case?

------
Jgrubb
Does anyone know how can I contribute to this documentation site? This entire
site is so much more readable if you just put a max-width of about 800px on
the container div.

~~~
btbuildem
[https://readability.com/](https://readability.com/)

~~~
a3n
I run my browser at half my laptop's screen width. It's ironic that I have to
turn off styles (Firefox: View/Page Style/No Style) so that I can read the
page without horizontal scrolling back and forth.

But more fundamentally, as a free service, I wonder how they can afford the
server and storage resources needed for this (it's not in their FAQ).

I'm not a member, so I don't know what their logged in pages look like, nor
their readability-ized pages. But I doubt if advertising is paying the bills.

The next obvious thing is some kind of analytics. "People who read this also
read that." Sold to whoever can use such information.

Or maybe it's a charitable effort. Or maybe they expect to be sold, I don't
know.

I'm not bashing them at all, but I am becoming much more critical, privacy-
wise, of everything I touch on the web. I'm always asking, subconsciously at
least, "what's in it for them?"

Similarly, I'm looking up at my toolbar, and wondering what's in it for the
people who made my addons for Ghostery, Disconnect and UBo. They would seem to
be sitting on a goldmine of analytics.

Dunno. But my web experience feels much more adversarial today than it did in
the early nineties.

------
jholman
1 through 5 all essentially amount to this: if the WHERE constraint indicates
a contiguous subsequence of the index... or a small list of such
subsequences... and btw we're mildly clever about reparsing your constraint to
try to find a way to make it into a small list of contiguous subsequences. It
helps to imagine the rows (or rather the index entries sitting in order in a
file, and considering where the wanted rows would sit in that file.

------
jgalt212
I like SQLite a lot, but for me it really seems to break down when the db
grows to about 10 GB in size when running 64 bit Ubuntu. ext4 fs.

Does this mesh with others experiences, or can SQLite databases be performant
above 10 GB?

------
dafrankenstein2
when learning sqlite3, at first i thought it was a toy-database. but than
after some exploring i found that its a real big thing! :)

------
coleifer
Anybody know how the query planner may change in 4.0?

