Re: [PATCH] cuirass: Perform some database "optimization" at startup.

[Christopher Baines <mail@cbaines.net>]

[-- Attachment #1: Type: text/plain, Size: 1724 bytes --]


Danny Milosavljevic <dannym@scratchpost.org> writes:

> the docs at https://www.sqlite.org/pragma.html#pragma_optimize suggest to run
> "PRAGMA optimize" at the end of the connection, or periodically--not at the
> beginning.
>
> That makes sense since it has to be able to see which queries are emitted
> in order to know what to optimize.
>
> Also, docs say:
>
>> The query planner used sqlite_stat1-style statistics for one or more indexes
>> of the table at some point during the lifetime of the current connection.
>
> That probably means one would have had to run ANALYZE at some point in the past.

Thanks Danny, this is interesting.

From my reading of the docs, I think the only thing the optimize pragma
is going to do is run ANALYZE on some tables. There's something about
the current connection referenced in "Determination Of When To Run
Analyze", but it's not the only thing that triggers this.

There is some stuff mentioned about recent queries, but it seems to be
prefixed with "(Not yet implemented)".

I'm not sure where this would fit in the Cuirass code when connections
are closed, as I'm not sure where the connections are closed! In terms
of running this regularly, I'm up for trying to work that in, maybe it
could happen after new data has been added, or something like that.

Although I don't have any evidence to support this, I'm hoping running
the optimize pragma at startup will help in some cases, like when
migrations add new indexes, as I think the docs say SQLite will analyze
a table if an index hasn't been analyzed yet.

> Replaying the WAL sounds like a good idea at the beginning, though.  Most
> journalling filesystems do that too.

Cool :)

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 962 bytes --]