Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1loX3Y-00069R-GF for pgsql-admin@arkaria.postgresql.org; Wed, 02 Jun 2021 20:01:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1loX31-0002WL-T6 for pgsql-admin@arkaria.postgresql.org; Wed, 02 Jun 2021 20:01:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1loX31-0002WC-FZ for pgsql-admin@lists.postgresql.org; Wed, 02 Jun 2021 20:01:03 +0000 Received: from nativespace-chaldene.ns-chaldene.com ([212.53.86.59]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1loX2u-0003Lw-Sj for pgsql-admin@lists.postgresql.org; Wed, 02 Jun 2021 20:01:03 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=davebolt.co.uk; s=default; h=Content-Type:MIME-Version:Message-ID:Date: Subject:In-Reply-To:References:Cc:To:From:Sender:Reply-To: Content-Transfer-Encoding:Content-ID:Content-Description:Resent-Date: Resent-From:Resent-Sender:Resent-To:Resent-Cc:Resent-Message-ID:List-Id: List-Help:List-Unsubscribe:List-Subscribe:List-Post:List-Owner:List-Archive; bh=24Tlx1o+s/qlUXSOXCKEt9PPa57/3lindlyXdm3wfUQ=; b=Y0sfh2c52Fu0p1cn9U82bAQGj qWq5hvdJgoE0F4k+armsYx4OyDjQbZEaZtubfvqMEbpmnCX4jlVntRBOTNZ6kv4PYwnkWvOGjgalX 96oF5d7jAPk/7EvKyTtpzlj9sRVwtwYIuAwIhriOPL3IRjQ7+r0dtl8/bb0UMC4PZrkjg=; Received: from davebolt.plus.com ([80.229.145.181]:60162 helo=BelongadaveX) by nativespace-chaldene.ns-chaldene.com with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.93) (envelope-from ) id 1loX2q-000Xy7-OY; Wed, 02 Jun 2021 21:00:52 +0100 From: To: "'sbob'" , "'David G. Johnston'" Cc: "'Pgsql-admin'" References: <3cf4cc46-c017-a279-dec0-c68a99836bb2@quadratum-braccas.com> In-Reply-To: <3cf4cc46-c017-a279-dec0-c68a99836bb2@quadratum-braccas.com> Subject: RE: column max length / max timestamps from system tables Date: Wed, 2 Jun 2021 21:00:53 +0100 Message-ID: <06f401d757e9$fe3b13c0$fab13b40$@co.uk> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_06F5_01D757F2.5FFF7BC0" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AddX5YdPp5kYCDyQSM2zn5RpM7gfCAAAyp7w Content-Language: en-gb X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - nativespace-chaldene.ns-chaldene.com X-AntiAbuse: Original Domain - lists.postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - davebolt.co.uk X-Get-Message-Sender-Via: nativespace-chaldene.ns-chaldene.com: authenticated_id: dave@davebolt.co.uk X-Authenticated-Sender: nativespace-chaldene.ns-chaldene.com: dave@davebolt.co.uk List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. ------=_NextPart_000_06F5_01D757F2.5FFF7BC0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable I don=E2=80=99t have suitable data to hand to test, but I would expect = finding the max on a timestamp column would be PDQ, especially if it is = an indexed column. For the other column I=E2=80=99m guessing more work has to be done = finding the length for each row. Maybe maintaining a column containing = the current length for each row would help? Anyway, can you test the max timestamp and the max col length separately = to confirm where the time goes? Re-reading the original question makes me think there may be more = complexity to your querie(s) then I realise. Dave =20 From: sbob [mailto:sbob@quadratum-braccas.com]=20 Sent: 02 June 2021 20:18 To: David G. Johnston Cc: Pgsql-admin Subject: Re: column max length / max timestamps from system tables =20 Unfortunately this takes hours since the table has > 500million rows just wanted to see if there was a shorrtcut / alternate =20 On 6/2/21 11:13 AM, David G. Johnston wrote: =20 On Wed, Jun 2, 2021, 10:05 sbob wrote: Hi all; we need to pull the max timestamp for a set of columns and the max=20 string length for another set of columns. Can I get this from system=20 tables? If so, how up to date would it be? =20 No. Just query the table directly. =20 David J. =20 =20 ------=_NextPart_000_06F5_01D757F2.5FFF7BC0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

I don=E2=80=99t have suitable data to hand to test, but I would = expect finding the max on a timestamp column would be PDQ, especially if = it is an indexed column.

For the other column I=E2=80=99m guessing more work has to be done = finding the length for each row. Maybe maintaining a column containing = the current length for each row would help?

Anyway, can you test the max timestamp and the max col length = separately to confirm where the time goes?

Re-reading the original question makes me think there may be more = complexity to your querie(s) then I realise.

Dave

 

From:= sbob = [mailto:sbob@quadratum-braccas.com]
Sent: 02 June 2021 = 20:18
To: David G. Johnston
Cc: = Pgsql-admin
Subject: Re: column max length / max timestamps = from system tables

 

Unfortunately this takes hours = since the table has > 500million rows

just wanted to = see if there was a shorrtcut / = alternate

 

On 6/2/21 11:13 AM, David G. Johnston = wrote:

 

On = Wed, Jun 2, 2021, 10:05 sbob <sbob@quadratum-braccas.com= > wrote:

Hi = all;


we need to pull the max timestamp for a set of columns = and the max
string length for another set of columns. Can I get this = from system
tables? If so, how up to date would it = be?

 

No.  Just query the table = directly.

 

David J.

 

 

------=_NextPart_000_06F5_01D757F2.5FFF7BC0--