Imagine the following sequence of events: 1. a "writer" session begins a transaction, growing the number of live tuples in several tables (e.g. via COPY) from mere tens (or hundreds) to tens of thousands of tuples, then COMMITs 2. one or more "reader" sessions perform a SELECT ... JOIN on the very tables grown by the "writer" session Currently, unless the autovacuum daemon happens to run after the "writer" session COMMITs and complete before the "reader" session(s) SELECT, then the "reader" session(s) may complete execution in minutes instead of milliseconds because of an incorrect execution plan based on stale statistics. Have seen this happen in reality. In my specific case, all write operations are serialized via an application-level mutex, i.e. there will only be one "writer" session and multiple "reader" sessions at any given time. In such a setup, the most obvious workaround is to explicitly ANALYZE the affected tables, prior to COMMITting, in the "writer" session. New data would thus be committed along with up-to-date statistics. However, let's tweak the above scenario and assume that, later on, the "writer" session only INSERTs one row in a table which now counts several million tuples.-- indiscriminately running ANALYZE after such a measly INSERTion is overkill. The logical, amended solution would then be to have the "writer" session perform, after INSERTion but before COMMITTing, the same calculation that the autovacuum daemon currently performs inside relation_needs_vacanalyze, based on the same configuration parameters that the autovacuum daemon uses, before deciding whether to explicitly ANALYZE or not the affected application tables. Ideally one would not want to duplicate the relation_needs_vacanalyze logic (not to mention having to guess the value of last_anl_tuples, which is not exposed via any storedproc -- one would have to assume that it is more or less equivalent to pg_class.reltuples.) So my question is: does it sound reasonable to (and/or are there any existing plans to) expose either relation_needs_vacanalyze, or a per-table flavour of do_autoanalyze, to the public via a stored proc for those in my situation or who need more granular control over autovacuuming than the autovacuum daemon does? Thank you, Vlad. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
