Re: Faster distinct query?

> > It’s only when I add in the AND data.channels=channels.channel that the > query time blows up to 5+ minutes. I personally don’t understand why there > would be such a large difference between the two queries - something wrong > with my indexes? > Did you do the manual vacuum as suggested by To

Re: Postgres incremental backups per db (not per cluster)

Thanks ! From: Adrian Klaver Sent: Thursday, September 23, 2021 03:56 PM To: Jaime Solorzano ; pgsql-general@lists.postgresql.org Subject: Re: Postgres incremental backups per db (not per cluster) On 9/23/21 12:57 PM, Jaime Solorzano wrote: > According to the W

Postgres incremental backups per db (not per cluster)

Thank you ! From: Ron Sent: Thursday, September 23, 2021 02:37 PM To: pgsql-general@lists.postgresql.org Subject: Re: Postgres incremental backups per db (not per cluster) On 9/23/21 2:57 PM, Jaime Solorzano wrote: According to the WAL Point-in-time recovery an

Re: Postgres incremental backups per db (not per cluster)

On 9/23/21 12:57 PM, Jaime Solorzano wrote: According to the WAL Point-in-time recovery and incremental backup documentation, pg_basebackup and wals are used to take backups and incremental backups of a running PostgreSQL cluster. I wonder if there is a way to take incremental backups per dat

Re: Postgres incremental backups per db (not per cluster)

On 9/23/21 2:57 PM, Jaime Solorzano wrote: According to the WAL Point-in-time recovery and incremental backup documentation, pg_basebackup and wals are used to take backups and incremental backups of a running PostgreSQL cluster. I wonder if there is a way to take incremental backups per da

Re: Faster distinct query?

> On Sep 23, 2021, at 10:36 AM, Geoff Winkless wrote: > > On Wed, 22 Sept 2021 at 21:05, Israel Brewster > wrote: > I was wondering if there was any way to improve the performance of this query: > > SELECT station,array_agg(distinct(channel)) as channels FROM data

Postgres incremental backups per db (not per cluster)

According to the WAL Point-in-time recovery and incremental backup documentation, pg_basebackup and wals are used to take backups and incremental backups of a running PostgreSQL cluster. I wonder if there is a way to take incremental backups per database instead of the entire cluster. Thanks

Re: Faster distinct query?

On Wed, 22 Sept 2021 at 21:05, Israel Brewster wrote: > I was wondering if there was any way to improve the performance of this > query: > > > SELECT station,array_agg(distinct(channel)) as channels FROM data GROUP BY > station; > > If you have tables of possible stations and channels (and if no

Re: Faster distinct query?

I would look into pre-loading the lookup table (and pre-emptive maintenance).  Add the foreign key, but not the trigger. That makes sense. Thanks! Yeah, then I got to wondering: Do you care?  Are these stations likely to be spoofed?  You have the station id and type in you data table and esse

Re: Currently running queries with actual arguments?

On 9/23/21 7:58 PM, Garfield Lewis wrote: The way I normally get this info is by setting the following: log_statement = 'all' then the arguments will be printed in the postgres.log file. There could be some other way but that is what I know. Thanks, those queries are logged after their comp

Re: Faster distinct query?

> On Sep 23, 2021, at 8:33 AM, Rob Sargent wrote: > > On 9/23/21 10:16 AM, Israel Brewster wrote: >>> On Sep 23, 2021, at 4:34 AM, Ryan Booz >> > wrote: >>> >>> Heh, I honestly forgot about the recursive CTE. Certainly worth a try and >>> wouldn't require installing o

Re: Get COUNT results from two different columns

On Thu, Sep 23, 2021 at 6:37 AM Clive Swan wrote: > Greetings, > > I have two separate queries that work individually, returning a count from > each column. > > > > I want to subtract New(COUNT) from Old(Count) > > > I get an error when trying to run UNION? > While you finally did provide this

Re: Faster distinct query?

On 9/23/21 10:16 AM, Israel Brewster wrote: On Sep 23, 2021, at 4:34 AM, Ryan Booz > wrote: Heh, I honestly forgot about the recursive CTE. Certainly worth a try and wouldn't require installing other extensions. This is what depesz is referring to: https://wiki.pos

Re: Faster distinct query?

> On Sep 23, 2021, at 4:34 AM, Ryan Booz wrote: > > Heh, I honestly forgot about the recursive CTE. Certainly worth a try and > wouldn't require installing other extensions. > > This is what depesz is referring to: > https://wiki.postgresql.org/wiki/Loose_indexscan >

Re: Faster distinct query?

> On Sep 22, 2021, at 11:04 PM, hubert depesz lubaczewski > wrote: > > On Wed, Sep 22, 2021 at 12:05:22PM -0800, Israel Brewster wrote: >> I was wondering if there was any way to improve the performance of this >> query: >> >> SELECT station,array_agg(distinct(channel)) as channels FROM data G

Re: Currently running queries with actual arguments?

The way I normally get this info is by setting the following: log_statement = 'all' then the arguments will be printed in the postgres.log file. There could be some other way but that is what I know. -- Regards, Garfield On 2021-09-23, 11:33 AM, "rihad" wrote: Hi, is it possible to vie

Currently running queries with actual arguments?

Hi, is it possible to view the list of currently running queries with $1, $2 etc replaced with the actual arguments?

RE: Get COUNT results from two different columns

Greetings, Thanks for the message. The error message that I get is. ERROR: subquery in FROM must have an alias Hint: For example, FROM (SELECT ...) [AS] foo. Position: 100 Trying to make sense of it.. Clive -Original Message- From: SQL Padawan [mailto:sql_pada...@protonmail.com]

Re: Get COUNT results from two different columns

Seems to me that's not an UNION, but a JOIN: SELECT o.old_count - n.new_count, o.old_sup, n.new_sup FROM ( SELECT new_sup, COUNT(new_sup) FROM public."Data" GROUP BY new_sup ) n JOIN ( SELECT old_sup, COUNT(old_sup) FROM public."Data" GROUP BY old_sup ) o ON o.old_su

RE: Get COUNT results from two different columns

Hi, > I have two separate queries that work individually, returning a count from > each column. > I would appreciate any pointers. > new_sup,  COUNT(new_sup) AS new_sup_count >     old_sup,  COUNT(old_sup) AS old_sup_count At least some of your problem is here - in your UNION-ed table,

Re: Get COUNT results from two different columns

On 23/09/2021 14:37, Clive Swan wrote: Greetings, I have two separate queries that work individually, returning a count from each column. I want to subtract New(COUNT) from Old(Count) I get an error when trying to run UNION? At a quick guess, you'll need to move the subtraction outside the

RE: Get COUNT results from two different columns

Greetings, I have two separate queries that work individually, returning a count from each column. I want to subtract New(COUNT) from Old(Count) I get an error when trying to run UNION? I would appreciate any pointers. -- COUNT NEW SUPPLIER -- SELECT new_sup, COUNT(new_sup) FRO

Re: Faster distinct query?

Heh, I honestly forgot about the recursive CTE. Certainly worth a try and wouldn't require installing other extensions. This is what depesz is referring to: https://wiki.postgresql.org/wiki/Loose_indexscan On Thu, Sep 23, 2021 at 3:04 AM hubert depesz lubaczewski wrote: > On Wed, Sep 22, 2021 a

Re: Remove duplicated row in pg_largeobject_metadata

> > > Interesting. Can you nail down the software versions that were in > use here? That'd be the old PG server version upgraded from, the > new server version upgraded to, the versions of pg_upgrade and > pg_dump (these probably should match the new server version, but > I'm not certain we enfor

Re: Faster distinct query?

On Wed, Sep 22, 2021 at 12:05:22PM -0800, Israel Brewster wrote: > I was wondering if there was any way to improve the performance of this query: > > SELECT station,array_agg(distinct(channel)) as channels FROM data GROUP BY > station; > > The explain execution plan can be found here: > https://