Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gzLqC-00041v-AR for pgsql-performance@arkaria.postgresql.org; Thu, 28 Feb 2019 13:35:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gzLqA-0002gy-VQ for pgsql-performance@arkaria.postgresql.org; Thu, 28 Feb 2019 13:35:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gzLqA-0002gr-La for pgsql-performance@lists.postgresql.org; Thu, 28 Feb 2019 13:35:10 +0000 Received: from mail-it1-x12e.google.com ([2607:f8b0:4864:20::12e]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gzLpu-0001X2-LI for pgsql-performance@postgresql.org; Thu, 28 Feb 2019 13:35:09 +0000 Received: by mail-it1-x12e.google.com with SMTP id 188so15650059itb.0 for ; Thu, 28 Feb 2019 05:34:54 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=message-id:mime-version:to:cc:from:subject:date:importance :in-reply-to:references; bh=tnq3HafleJ95aE1nto2EzeFXLbkzoHd+yz3GhCcKmrw=; b=np+Sv4Ms/LvdtALbZbMKhwt5ptH1qlBZZCaKIkSIm7LCXiEpPKnVwG6g/aYc/lqUCH 5SVfVYAbtvqcOz88+/lEB463RKsaah1S7RQfQV6FoLSslVHFnD3UNfUmf+oD6szBdpGd V9QEQk2DyamTrPedyILScDb37CcZ8T528cxYkaXYeMJ3IqtyByc1OQp/1BHzCTMZsGo2 udTBh8SYpd4WjyRMvZf3WBqqinNzlf4EMamOTtW2+Do1gAwM9r3jRJnAb/TVwB66S7no gBoF9eihA26vmdSLJNTjyNj7nrvm8R/NJLEH4LE/gqv49HzaQymIhMHplIfQnPf3NmeT NAzg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:message-id:mime-version:to:cc:from:subject:date :importance:in-reply-to:references; bh=tnq3HafleJ95aE1nto2EzeFXLbkzoHd+yz3GhCcKmrw=; b=lrQvoZ0aM1/eroLjutEw+OOHF9XkJ+bIam76G2I/ZG71vA0LDh5mVWvFoLR/FA4ow1 g1VbxfTZ66J6XjH+2MMGJzXPy0WVfQTOmfdnaga4J2FCaTh0OUgLpahZsyV0AQPGhbBX UKUzv+lOfB3Q9RQ1jsz96dgFAVH7Wn7gPW8BBOtqIqGdrIf/cZyAZJ7QfEQDwbFanbQ7 uL/u2jNh1MJT0ougsqfhnvk30rJrPZvH2lg99cA6SSTESyzBUVcJZVielKBtFuJ/+cUO JFRtCXLGZ4uPhhbckuPNJobsElxe8qrH3nLo3HPTfD5PmMtdguYIX/UppCzYR2oawKke h8Dg== X-Gm-Message-State: AHQUAube5LZz2u1Fv9FMpw+9PUC56u7FJUuH9chquVwyhN9kAOb8T7lN fBbXtA2wE7wjdipg4hX4Dtg= X-Google-Smtp-Source: AHgI3IYKWmUuqt74yuryodbTsG3FdWwajGYMPBeP0HsnY6Nvjd+kJcrQZ8Dyq0WXxbmlS3hqDVNFQg== X-Received: by 2002:a02:c60b:: with SMTP id i11mr4684501jan.118.1551360892986; Thu, 28 Feb 2019 05:34:52 -0800 (PST) Received: from ?IPv6:::ffff:172.25.138.67? ([165.215.94.3]) by smtp.gmail.com with ESMTPSA id i2sm7923268iog.3.2019.02.28.05.34.51 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 28 Feb 2019 05:34:52 -0800 (PST) Message-ID: <5c77e37c.1c69fb81.ec2b6.6cef@mx.google.com> MIME-Version: 1.0 To: Justin Pryzby Cc: "pgsql-performance@postgresql.org" From: Fd Habash Subject: RE: What is pg_stat_user_tables Showing NULL for last_autoanalyze &last_autovacuum Date: Thu, 28 Feb 2019 08:34:54 -0500 Importance: normal X-Priority: 3 In-Reply-To: <20190227161556.GD28750@telsasoft.com> References: <5c76a2f0.1c69fb81.a4b54.6182@mx.google.com> <20190227161556.GD28750@telsasoft.com> Content-Type: multipart/alternative; boundary="_200E5C35-3307-45D5-B943-421365AD465D_" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_200E5C35-3307-45D5-B943-421365AD465D_ Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="utf-8" Thank you =E2=80=A6 Are the calculations for triggering autovacuum dependent upon statistics ge= nerated by auto-anaylyze. In other words, if autoanalyze does not run at al= l, will autovac be able to run its math for threshold (updates & deletes) &= scale factor (table rows) to do its thing? My understanding from the documentation is that it does not need autoanalyz= e stats. Thanks=20 ---------------- Thank you From: Justin Pryzby Sent: Wednesday, February 27, 2019 11:15 AM To: Fd Habash Cc: pgsql-performance@postgresql.org Subject: Re: What is pg_stat_user_tables Showing NULL for last_autoanalyze = &last_autovacuum On Wed, Feb 27, 2019 at 09:47:13AM -0500, Fd Habash wrote: > I have been able to locate four google search results with the same inqui= ry. What=E2=80=99ve been able to understand is =E2=80=A6 >=20 > 1. If auto-vaccum is working as expected, stats collector does not nullif= y these values as part of a startup sequence or regular Maitenance. If a re= lation gets auto[vacuumed|analyzed], the timestamps should remain. > 2. A database engine crash or restart with =E2=80=98immediate=E2=80=99 op= tion will cause the timestamps to nullify.=20 > 3. Table never qualified for vacuuming based on auto-vacuum settings.=20 Can you give an example ? If it's an empty inheritence parent (relkind=3Dr), then it won't trigger autovacuum/analyze thresholds (but you should analyze it manually). Note that relkind=3Dp "partitioned" tables don't have entries at all. https://www.postgresql.org/message-id/flat/20180503141430.GA28019%40telsaso= ft.com If it's never DELETEd from, then it won't trigger autovacuum (but may trigg= er autoanalyze). Justin --_200E5C35-3307-45D5-B943-421365AD465D_ Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="utf-8"

Thank you =E2=80=A6

 

Are the calculations for = triggering autovacuum dependent upon statistics generated by auto-anaylyze.= In other words, if autoanalyze does not run at all, will autovac be able t= o run its math for threshold (updates & deletes) & scale factor (ta= ble rows) to do its thing?

 

My understanding from the documentation is that it does n= ot need autoanalyze stats.

 

Thanks

 

----------------
Thank you

 

From: Justin Pryzby
Sent: Wednesday, February 27, 2019 = 11:15 AM
To: Fd Habash<= br>Cc: pgsql-per= formance@postgresql.org
Subject: Re: What is pg_stat_user_tab= les Showing NULL for last_autoanalyze &last_autovacuum

 

On Wed, Feb 27, 201= 9 at 09:47:13AM -0500, Fd Habash wrote:

> I have= been able to locate four google search results with the same inquiry. What= =E2=80=99ve been able to understand is =E2=80=A6

&g= t;

> 1. If auto-vaccum is working as expected, = stats collector does not nullify these values as part of a startup sequence= or regular Maitenance. If a relation gets auto[vacuumed|analyzed], the tim= estamps should remain.

> 2. A database engine cr= ash or restart with =E2=80=98immediate=E2=80=99 option will cause the times= tamps to nullify.

> 3. Table never qualified fo= r vacuuming based on auto-vacuum settings.

&n= bsp;

Can you give an example ?

 

If it's an empty inheri= tence parent (relkind=3Dr), then it won't trigger

a= utovacuum/analyze thresholds (but you should analyze it manually).

 

Note that relkind= =3Dp "partitioned" tables don't have entries at all.

https://www.postgresql.org/message-id/flat/20180503141430.GA28= 019%40telsasoft.com

 

If it's never DELETEd from, then it won't trigger autovacuum (= but may trigger

autoanalyze).

 

Justin

 

= --_200E5C35-3307-45D5-B943-421365AD465D_--