Re: [SQL] Concurrent Reindex on Primary Key for large table
Thanks! That worked. Any thoughts about containing index bloat. I thought the autovac would clean it up a bit more. Would any tweaks to my settings improve autovac performance? I am still doing a couple of concurrent reindexes per week otherwise performance degrades over a couple of days. Thanks RV -- View this message in context: http://postgresql.1045698.n5.nabble.com/Concurrent-Reindex-on-Primary-Key-for-large-table-tp5467243p5470216.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] time interval math
> -Original Message-
> From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-
> ow...@postgresql.org] On Behalf Of Jasen Betts
> Sent: Thursday, February 09, 2012 6:37 AM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] time interval math
>
> On 2012-02-08, Edward W. Rouse wrote:
> > I'm still working on getting this to work, but the summary is this:
> >
> > I am getting several (many) intervals of hour, minutes and seconds. I
> need a
> > sum of the absolute value these intervals, similar to the SUM(ABS())
> > function for numbers; and I need to divide this sum by an integer
> (bigint).
> > Getting the intervals is no problem, but I can't find built in
> functions for
> > the rest. Currently on 8.3, want to upgrade to 9.x but I can't until
> this is
> > finished.
>
> the operation abs() is meaninless on the type interval
> eg: what is abs( '1 month - 32 days + 24 hours'::interval )
If you need to add 30 intervals together, then +- is not meaningless.
>
> howevwer since all your intervals are in seconds (postgres pretends
> that all
> hours are 3600 seconds long) converting to seconds is probably the
> best way to go.
>
> > Do these functions exist, or will I be forced to convert to seconds,
> do the
> > math and then convert back to hour-minute-second format (I am
> assuming from
> > current data that, after the divide, the result should be in the
> minute:
> > second range).
>
> Yeah, you need to do that, it's not hard,
>
> select ( sum(abs(extract('epoch' from YOUR_INTERVAL ))) / SOMETHING ) *
> '1s'::interval
> from ...
>
> --
> ⚂⚃ 100% natural
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] time interval math
On 2012-02-08, Edward W. Rouse wrote:
> I'm still working on getting this to work, but the summary is this:
>
> I am getting several (many) intervals of hour, minutes and seconds. I need a
> sum of the absolute value these intervals, similar to the SUM(ABS())
> function for numbers; and I need to divide this sum by an integer (bigint).
> Getting the intervals is no problem, but I can't find built in functions for
> the rest. Currently on 8.3, want to upgrade to 9.x but I can't until this is
> finished.
the operation abs() is meaninless on the type interval
eg: what is abs( '1 month - 32 days + 24 hours'::interval )
howevwer since all your intervals are in seconds (postgres pretends that all
hours are 3600 seconds long) converting to seconds is probably the
best way to go.
> Do these functions exist, or will I be forced to convert to seconds, do the
> math and then convert back to hour-minute-second format (I am assuming from
> current data that, after the divide, the result should be in the minute:
> second range).
Yeah, you need to do that, it's not hard,
select ( sum(abs(extract('epoch' from YOUR_INTERVAL ))) / SOMETHING ) *
'1s'::interval
from ...
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
