pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcolumn max length / max timestamps from system tables
9+ messages / 7 participants
[nested] [flat]
* column max length / max timestamps from system tables
@ 2021-06-02 17:05 sbob <sbob@quadratum-braccas.com>
0 siblings, 2 replies; 9+ messages in thread
From: sbob @ 2021-06-02 17:05 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: column max length / max timestamps from system tables
@ 2021-06-02 17:13 David G. Johnston <david.g.johnston@gmail.com>
parent: sbob <sbob@quadratum-braccas.com>
1 sibling, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2021-06-02 17:13 UTC (permalink / raw)
To: sbob <sbob@quadratum-braccas.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: column max length / max timestamps from system tables
@ 2021-06-02 19:18 sbob <sbob@quadratum-braccas.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: sbob @ 2021-06-02 19:18 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
> <mailto: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.
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: column max length / max timestamps from system tables
@ 2021-06-02 20:00 dave@davebolt.co.uk
parent: sbob <sbob@quadratum-braccas.com>
0 siblings, 2 replies; 9+ messages in thread
From: dave@davebolt.co.uk @ 2021-06-02 20:00 UTC (permalink / raw)
To: 'sbob' <sbob@quadratum-braccas.com>; 'David G. Johnston' <david.g.johnston@gmail.com>; +Cc: 'Pgsql-admin' <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: column max length / max timestamps from system tables
@ 2021-06-02 20:05 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: dave@davebolt.co.uk
1 sibling, 0 replies; 9+ messages in thread
From: Vijaykumar Jain @ 2021-06-02 20:05 UTC (permalink / raw)
To: dave@davebolt.co.uk; +Cc: sbob <sbob@quadratum-braccas.com>; David G. Johnston <david.g.johnston@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
1) you can create an aggregate table, and save the relevant aggregates as
per your requirement.
then you can have triggers, insert/update to check if the new agg is
greater than ones in aggregate table, update that table else keep it
unchanged.
when delete, delete the row from the agg table if the deleted row values
equal to the agg values. the agg table is now empty.
then create a function to query the agg table to return relevant values, if
not found, query the aggregates from the base table, update the agg table
and then return those values.
that way, unless you delete the row containing the max value, you should be
able to get agg values pretty quickly.
but it would slow down DMLs by a small amount of time.
On Thu, 3 Jun 2021 at 01:31, <dave@davebolt.co.uk> wrote:
> 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.
>
>
>
>
>
>
--
Thanks,
Vijay
Mumbai, India
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: column max length / max timestamps from system tables
@ 2021-06-02 20:32 Scott Ribe <scott_ribe@elevated-dev.com>
parent: dave@davebolt.co.uk
1 sibling, 1 reply; 9+ messages in thread
From: Scott Ribe @ 2021-06-02 20:32 UTC (permalink / raw)
To: sbob <sbob@quadratum-braccas.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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 column containing the current length for each row would help?
or a functional index
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: column max length / max timestamps from system tables
@ 2021-06-02 21:33 sbob <sbob@quadratum-braccas.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 9+ messages in thread
From: sbob @ 2021-06-02 21:33 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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 column containing the current length for each row would help?
> or a functional index
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: column max length / max timestamps from system tables
@ 2021-06-02 21:43 Bruce Momjian <bruce@momjian.us>
parent: sbob <sbob@quadratum-braccas.com>
0 siblings, 0 replies; 9+ messages in thread
From: Bruce Momjian @ 2021-06-02 21:43 UTC (permalink / raw)
To: sbob <sbob@quadratum-braccas.com>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: column max length / max timestamps from system tables
@ 2021-06-02 21:44 Michel SALAIS <msalais@msym.fr>
parent: sbob <sbob@quadratum-braccas.com>
1 sibling, 0 replies; 9+ messages in thread
From: Michel SALAIS @ 2021-06-02 21:44 UTC (permalink / raw)
To: 'sbob' <sbob@quadratum-braccas.com>; pgsql-admin@lists.postgresql.org
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 this from system tables? If so, how up to date would it be?
Thanks in advance
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2021-06-02 21:44 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-02 17:05 column max length / max timestamps from system tables sbob <sbob@quadratum-braccas.com>
2021-06-02 17:13 ` David G. Johnston <david.g.johnston@gmail.com>
2021-06-02 19:18 ` sbob <sbob@quadratum-braccas.com>
2021-06-02 20:00 ` dave@davebolt.co.uk
2021-06-02 20:05 ` Vijaykumar Jain <vijaykumarjain.github@gmail.com>
2021-06-02 20:32 ` Scott Ribe <scott_ribe@elevated-dev.com>
2021-06-02 21:33 ` sbob <sbob@quadratum-braccas.com>
2021-06-02 21:43 ` Bruce Momjian <bruce@momjian.us>
2021-06-02 21:44 ` Michel SALAIS <msalais@msym.fr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox