Does VACUUM imply REINDEX?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Does VACUUM imply REINDEX?

Olivier Mascia
Hello,

As part of a rare database "maintenance"...
Does VACUUM, in essence, implies whatever actions REINDEX would do?
Would REINDEX before VACUUM make any more sense?

And what about ANALYZE? Would it be wise or useless, to run it after VACUUM?

So... Would "VACUUM; REINDEX; ANALYZE;" make any sense as a polishing step on a heavily modified database which will then become an archived mostly read-only body?

⏤  
Olivier


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Does VACUUM imply REINDEX?

Richard Hipp-3
On 2/19/20, Olivier Mascia <[hidden email]> wrote:
> Hello,
>
> As part of a rare database "maintenance"...
> Does VACUUM, in essence, implies whatever actions REINDEX would do?
> Would REINDEX before VACUUM make any more sense?
>
> And what about ANALYZE? Would it be wise or useless, to run it after VACUUM?

VACUUM does not imply REINDEX.  Those are separate actions.  VACUUM
repacks all the content in to minimal space, and thus (probably) makes
the database file a little smaller.  It also reorganizes the pages so
that tables are grouped together and are in search order, which might
make table scans faster, depending on the underlying filesystem.

REINDEX rebuilds an index from scratch based on the original table
data.  This amounts to a big sort.  REINDEX was invented so that if
you change the definition of a collating function, you can run REINDEX
on all indexes that use that collating function and it will reorder
the indexes into the new correct order.  REINDEX will also fix any
index corruption.  But if you have index corruption, that is a symptom
of deeper problems which should be addressed. You should not
paper-over such problems by running REINDEX.

VACUUM is independent of ANALYZE.  The information computed by ANALYZE
is the same before and after VACUUM.  There is no benefit to running
ANALYZE after running VACUUM if the ANALYZE data (the content of the
sqlite_stat1 table) is still up-to-date.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Does VACUUM imply REINDEX?

Simon Slavin-3
On 19 Feb 2020, at 4:18pm, Richard Hipp <[hidden email]> wrote:

> There is no benefit to running
> ANALYZE after running VACUUM if the ANALYZE data (the content of the
> sqlite_stat1 table) is still up-to-date.

However, if you have a yearly maintenance procedure, it might make sense to include ANALYZE in it.  This would take care of circumstances where your business has changed character, for instance changing from selling a lot to a few customers, to selling a little to many customers.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Does VACUUM imply REINDEX?

Olivier Mascia
> Le 19 févr. 2020 à 17:26, Simon Slavin <[hidden email]> a écrit :
>
> On 19 Feb 2020, at 4:18pm, Richard Hipp <[hidden email]> wrote:
>
>> There is no benefit to running
>> ANALYZE after running VACUUM if the ANALYZE data (the content of the
>> sqlite_stat1 table) is still up-to-date.
>
> However, if you have a yearly maintenance procedure, it might make sense to include ANALYZE in it.  This would take care of circumstances where your business has changed character, for instance changing from selling a lot to a few customers, to selling a little to many customers.

So, reading the precise and complete description of Richard, and this idea from Simon, having an apparently sane database (pragma integrity_check and pragma foreign_key_check successful at least), it would make sense to do : REINDEX, ANALYZE and then VACUUM as part of final preparation step for archive (the DB would be seldom queried later).

In this plan, the REINDEX step would seem probably useless if there have been no collation changes, but I have seen cases where some indexes are incredibly more compact after REINDEX (which is logical).

⏤  
Olivier


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Does VACUUM imply REINDEX?

Simon Slavin-3
On 19 Feb 2020, at 4:36pm, Olivier Mascia <[hidden email]> wrote:

> having an apparently sane database (pragma integrity_check and pragma foreign_key_check successful at least), it would make sense to do : REINDEX, ANALYZE and then VACUUM as part of final preparation step for archive (the DB would be seldom queried later).

That would seem to the a good order in which to do those five things.

If I was setting up a new system for customer use, I would give them a maintenance procedure which did those things, let them run it whenever they wanted, but tell them to run it at least once a year.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users