On Wed, Jun 8, 2011 at 10:45 PM, Tom Lane
<tgl@sss.pgh.pa.us> wrote:
Pavan Deolasee <
pavan.deolasee@gmail.com> writes:
> I first thought that analyze and vacuum can not run concurrently on the same
> table since they take a conflicting lock on the table. So even if we ignore
> the analyze process while calculating the OldestXmin for vacuum, we should
> be fine since we know they are working on different tables. But I see
> analyze also acquires sample rows from the inherited tables with a
> non-conflicting lock. I probably do not understand the analyze code well,
> but is that the reason why we can't ignore analyze snapshot while
> determining OldestXmin for vacuum ?
the use of user-defined functions, which might do practically anything.
They definitely could access tables other than the one under analysis.
(I believe that PostGIS does such things, for example --- it wants to
look at its auxiliary tables for metadata.)
Also keep in mind that we allow ANALYZE to be run inside a transaction
block, which might contain other operations sharing the same snapshot.
Ah, I see. Would there will be benefits if we can do some special handling for cases where we know that ANALYZE is running outside a transaction block and that its not going to invoke any user-defined functions ? If user is running ANALYZE inside a transaction block, he is probably already aware and ready to handle long-running transaction. But running them under the covers as part of auto-analyze does not see quite right. The pgbench test already shows the severe bloat that a long running analyze may cause for small tables and many wasteful vacuum runs on those tables.
Another idea would be to split the ANALYZE into multiple small transactions, each taking a new snapshot. That might result in bad statistics if the table is undergoing huge change, but in that case, the stats will be outdated soon anyways if we run with a old snapshot. I understand there could be issues like counting the same tuple twice or more, but would that be a common case to worry about ?
Thanks,