Thread: column max length / max timestamps from system tables
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? Thanks in advance
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?
Unfortunately this takes hours since the table has > 500million rows
just wanted to see if there was a shorrtcut / alternate
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.
I don’t 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’m 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.
I don’t 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’m 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.
> On Jun 2, 2021, at 2:00 PM, <dave@davebolt.co.uk> <dave@davebolt.co.uk> wrote: > > For the other column I’m guessing more work has to be done finding the length for each row. Maybe maintaining a columncontaining the current length for each row would help? or a functional index
Good point... I'll try this On 6/2/21 2:32 PM, Scott Ribe wrote: >> On Jun 2, 2021, at 2:00 PM, <dave@davebolt.co.uk> <dave@davebolt.co.uk> wrote: >> >> For the other column I’m guessing more work has to be done finding the length for each row. Maybe maintaining a columncontaining the current length for each row would help? > or a functional index >
On Wed, Jun 2, 2021 at 03:33:32PM -0600, sbob wrote: > Good point... I'll try this You could technically try to look at the pg_statistics values, but those are only populated by random page sampling, and only current as of the last analyze or autovacuum auto-analyze. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Hi, I am not sure that I understand well what you want... Have you several columns in the same table of type TIMESTAMP and you want the max of all of them? Same thing for the length of string columns? Michel SALAIS -----Message d'origine----- De : sbob <sbob@quadratum-braccas.com> Envoyé : mercredi 2 juin 2021 19:05 À : pgsql-admin@lists.postgresql.org Objet : column max length / max timestamps from system tables 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 thisfrom system tables? If so, how up to date would it be? Thanks in advance