On 2016/04/12 3:56 AM, Jose I. Cabrera wrote: > > > Maybe this should be something to think about, and perhaps add it as part of > the results or reported items of .schema. Also, only update the date if > completion successful. The reason why it's important is that I have a script > that runs every week, but lately, it appears as if it is failing, but the log > files say it is running. However, the searches are getting slower and > slower. It should not be too hard to add it as part of the .schema repor, > **I think**. Thanks.
It's probably not hard to add, but I don't have an opinion on that. In the meantime, while it isn't added yet, you should not have any difficulty establishing the truth. Do the queries you use suddenly speed up after you manually run analyze? Does the output of EXPLAIN QUERY change, especially with regard to the used indices? ANALYZE only really influence which Index gets used according to the current shape of the data. Your query plans should only change once the data shape changed significantly, and even then the plan might simply latch onto a better index /IF/ such an index exists. Perhaps the query can be optimized or better indices added? We'd have to see the schemata to assist there. In short - I very much doubt failing to complete ANALYZE would cause queries to gradually get slower over time. Further to that, I doubt very much that running ANALYZE frequently would gradually improve speed for a growing database. You may at best realize one or two abrupt speed-ups once a better index is selected for use. ANALYZE does no other magic that keeps the query process athletic. Simon already mentioned the need for ANALYZE is infrequent - I would go so far as to say I only ever use it twice, once during design (which is mostly superfluous) and then once later when the data has become a bit more bulky and the shape-to-be is clearly defined. Perhaps once more when a long-running DB has gone flabby and slow, but it's very rare in these cases that a simple ANALYZE fixes things, it usually requires more optimization. I believe running ANALYZE on a cron job or weekly script is doing no good at all. Perhaps a VACUUM might be of more use in these cases, but it comes with its own set of considerations. Cheers, Ryan
