Re: CTE optimization fence

I don’t think the fence analogy works. Try wall (a la Berlin). I count myself amongst those who thought “Ooh this little CTE will garner just what the rest of the query needs”. Only to find the planner didn’t groc that optimization. Is it a matter of deciding to trust the SQLer and runtime ana

Re: CTE optimization fence

Tom Lane schrieb am 27.06.2018 um 05:48: >> I see there was some discussion last year about removing the CTE >> optimization fence (e.g. >> http://www.postgresql-archive.org/CTE-inlining-td5958992.html) but can't >> find anything more recent. Does anyone know if this is still under >> considera

Re: CTE optimization fence

Guy Burgess writes: > I see there was some discussion last year about removing the CTE > optimization fence (e.g. > http://www.postgresql-archive.org/CTE-inlining-td5958992.html) but can't > find anything more recent. Does anyone know if this is still under > consideration? Nothing's actually

CTE optimization fence

Hello, I am running into performance issues with large CTE "WITH" queries (just for selecting, not updating).  I was surprised to find that the queries run much faster if I convert the CTEs to subqueries. From googling, I see that this is due to CTE acting as an optimization fence in PG. Unfor

Re: We find few queries running three times simultaneously with same parameters on postgres db

On Tue, Jun 26, 2018 at 1:26 PM, amandeep singh wrote: > Hi Andreas > > The value for $1 is same in all queries. > > @Edson: number of such running queries are always 3. > > Get Outlook for Android > > > > From: Andreas Kretschmer > Sent: Tuesday, 26 June, 6:04 PM > Subje

Re: We find few queries running three times simultaneously with same parameters on postgres db

Hi Andreas The value for $1 is same in all queries. @Edson: number of such running queries are always 3. Get Outlook for Android From: Andreas Kretschmer Sent: Tuesday, 26 June, 6:04 PM Subject: Re: We find few queries running three times simultaneously with same para

Re: Schema/Data conversion opensource tools from MySQL to PostgreSQL

Thank you for your inputs... I am looking for tool to reduce the manualy effort in Migration. I undestand manual efforts needed to migare 100% to PostgreSQL. On Tue, Jun 26, 2018 at 4:08 AM, Thomas Poty wrote: > Hi Chiranjeevi, > > I know my answer doesn't answer your request but we have just

Re: Using COPY to import large xml file

Thanks a lot everyone. After playing around with small dataset, I could able to make datasets that are easy to go with COPY. Creating datasets of around 50GB took say 2hrs (I can definitely improve on this). 54M records, COPY took around 35 minutes! Awesome.. :) :) Mean time, I understood few thi

Re: Problem Postgres

Hi Emanuele, you can post here in italian :) http://lists.psql.it/mailman/listinfo Regards Enrico Il 26/06/2018 10:45, Fabio Pardi ha scritto: Hi Emanuele, For the next time, is best if you post to an italian mailing list if your log messages are in italian. Else change settings in order

Re: We find few queries running three times simultaneously with same parameters on postgres db

On 26 June 2018 12:32:44 CEST, Andreas Kretschmer wrote: > > >Am 26.06.2018 um 12:19 schrieb amandeep singh: >> We have been observing our postgres database from past few days,We >> found few queries running three times simultaneously with same >> parameters.I would like to back track how a que

Re: We find few queries running three times simultaneously with same parameters on postgres db

Em 26/06/2018 08:49, Saurabh Agrawal escreveu: Am 26.06.2018 um 12:19 schrieb amandeep singh: We have been observing our postgres database from past few days,We found few queries running three times simultaneously with same parameters.I would like to back track how

Re: We find few queries running three times simultaneously with same parameters on postgres db

> > > Am 26.06.2018 um 12:19 schrieb amandeep singh: > >> We have been observing our postgres database from past few days,We found >> few queries running three times simultaneously with same parameters.I would >> like to back track how a query is running multiple times. > > Can you check the reques

Re: Single query uses all system memory

Hi Ivar, I agree with Magnus. As addition, also i would change:  effective_cache_size and  maintenance_work_mem Then disable OOM killer, change logging in order to log files on disk to see if your work_mem is too low and spills on disk, rethink your data structure and, overall, I think that

Re: We find few queries running three times simultaneously with same parameters on postgres db

Am 26.06.2018 um 12:19 schrieb amandeep singh: We have been observing our postgres database from past few days,We found few queries running three times simultaneously with same parameters.I would like to back track how a query is running multiple times. they are independend each other. You

We find few queries running three times simultaneously with same parameters on postgres db

Hi Team We have been observing our postgres database from past few days,We found few queries running three times simultaneously with same parameters.I would like to back track how a query is running multiple times. 2018-06-26 15:22:13.620045+05:30 | 00:00:00.198777 | active | 22234 | select *

Re: Single query uses all system memory

On Tue, Jun 26, 2018 at 11:40 AM, Ivar Fredriksen wrote: > A single large query is able to spend all the system memory (as reported > by top), and the oom_reaper kills postgres. See bottom of email for an > example query and logs. > > > > Expected behavior would be that postgres is not killed and

Re: Problem Postgres

Hi Emanuele, For the next time, is best if you post to an italian mailing list if your log messages are in italian. Else change settings in order to have english errors. Back to your problem: Looks like permissions on your installation are not OK, and have been manipulated from the default on

Re: Problem Postgres

Hello, Can you try with a superuser? Le mar. 26 juin 2018 à 10:06, Emanuele Musella a écrit : > Good morning, > > we have the following error: > > 2018-06-26 09:48:44 CEST ERRORE: non è stato possibile ottenere > informazioni sul file "base/16395/19739338": Permission denied > 2018-06-26 09:48:

Re: Problem Postgres

Am 26.06.2018 um 10:05 schrieb Emanuele Musella: Good morning, we have the following error: 2018-06-26 09:48:44 CEST ERRORE:  non è stato possibile ottenere informazioni sul file "base/16395/19739338": Permission denied 2018-06-26 09:48:44 CEST ISTRUZIONE:  select p.datname,pg_database_siz

Re: Schema/Data conversion opensource tools from MySQL to PostgreSQL

Hi Chiranjeevi, I know my answer doesn't answer your request but we have just converted a database from mysql to postgresql with about 1000 tables. We had to convert some functions present in mysql but not in postgresql Also we had to convert queries. I converted all of that manually, table by tab

Problem Postgres

Good morning, we have the following error: 2018-06-26 09:48:44 CEST ERRORE: non è stato possibile ottenere informazioni sul file "base/16395/19739338": Permission denied 2018-06-26 09:48:44 CEST ISTRUZIONE: select p.datname,pg_database_size(p.datname) from pg_database p This error repetly ever

Re: pg_upgrade and wraparound

On Tue, Jun 26, 2018 at 8:38 AM Alexander Shutyaev wrote: > > Hello again, > > I've performed another test - I've migrated to the new cluster using dump > restore: pg_dumpall | psql. It went well, although it took 6 days while > pg_upgrade usually took a night. > > Is there any hope the issue wi