Re: [PERFORM] IS NOT NULL and LEFT JOIN

Le Mardi 21 Octobre 2014 10:44 CEST, David Rowley a écrit: > For what it's worth I'd say they are identical, at least, if you discount > deferring foreign key constraints or also executing the query from within > a volatile function which was called by a query which just updated the > user_inf

Re: [PERFORM] ERROR: out of memory | with 23GB cached 7GB reserved on 30GB machine

increasing overcommit_ratio to 95 solved the problem, the box is now using it's memory as expected without needing to resort to swap. On Tue, Oct 21, 2014 at 3:55 PM, Montana Low wrote: > I didn't realize that about overcommit_ratio. It was at 50, I've changed > it to 95. I'll see if that clears

Re: [PERFORM] extremly bad select performance on huge table

Hi Igor, that was also my assumption, but unfortunately this isn't true. I am using the explain analyze. Example which is fast "explain analyze select value from smallertable inner join myhugetable ON smallertable.mycolumn = myhugetable.mycolumn" 130 - 140 sec Example which is fast "explain a

Re: [PERFORM] ERROR: out of memory | with 23GB cached 7GB reserved on 30GB machine

I didn't realize that about overcommit_ratio. It was at 50, I've changed it to 95. I'll see if that clears up the problem moving forward. # cat /proc/meminfo MemTotal: 30827220 kB MemFree: 153524 kB MemAvailable: 17941864 kB Buffers:6188 kB Cached: 24560208 kB

Re: [PERFORM] ERROR: out of memory | with 23GB cached 7GB reserved on 30GB machine

Dne 22 Říjen 2014, 0:25, Montana Low napsal(a): > I'm running postgres-9.3 on a 30GB ec2 xen instance w/ linux kernel > 3.16.3. > I receive numerous Error: out of memory messages in the log, which are > aborting client requests, even though there appears to be 23GB available > in > the OS cache. >

Re: [PERFORM] ERROR: out of memory | with 23GB cached 7GB reserved on 30GB machine

Montana Low writes: > I'm running postgres-9.3 on a 30GB ec2 xen instance w/ linux kernel 3.16.3. > I receive numerous Error: out of memory messages in the log, which are > aborting client requests, even though there appears to be 23GB available in > the OS cache. Perhaps the postmaster is being

[PERFORM] ERROR: out of memory | with 23GB cached 7GB reserved on 30GB machine

I'm running postgres-9.3 on a 30GB ec2 xen instance w/ linux kernel 3.16.3. I receive numerous Error: out of memory messages in the log, which are aborting client requests, even though there appears to be 23GB available in the OS cache. There is no swap on the box. Postgres is behind pgbouncer to

Re: [PERFORM] Query with large number of joins

-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Marco Di Cesare Sent: Tuesday, October 21, 2014 4:03 PM To: Andrew Dunstan; Merlin Moncure Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Query with l

Re: [PERFORM] Query with large number of joins

Andrew Dunstan writes: > Have you tried a) either turning off geqo or setting geqo_threshold > fairly high b) setting join_collapse_limit fairly high (assuming all > the above join targets are tables and not views, setting it to > something like 25 should do the trick. Tom Lane < t...@sss.pgh

Re: [PERFORM] Query with large number of joins

On 10/21/2014 12:31 PM, Andrew Dunstan wrote: > Please don't top-post on the PostgreSQL lists. See > Oops, sorry. >Have you tried a) either turning off geqo or setting geqo_threshold fairly >high b) setting join_collapse_limit fairly high (assuming >all th

Re: [PERFORM] extremly bad select performance on huge table

-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Björn Wittich Sent: Tuesday, October 21, 2014 3:32 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] extremly bad select performance on huge table Hi

Re: [PERFORM] extremly bad select performance on huge table

Hi Tom and Igor, thanks for your help. With the reindex the select query running time was reduced from 5200 sec to 130 sec. Impressive! Even a join on this table is now fast. Unfortunately, there is now another problem: The table in my example has 500 columns which I want to retrieve with my

Re: [PERFORM] extremly bad select performance on huge table

=?ISO-8859-15?Q?Bj=F6rn_Wittich?= writes: > Here is the explain (analyze,buffers) select mycolumn from myhugetable > "Index Only Scan using myprimkey on myhugetable (cost=0.00..822.82 > rows=71768080 width=33) (actual time=16.722..2456300.778 rows=71825999 > loops=1)" > " Heap Fetches: 356

Re: [PERFORM] extremly bad select performance on huge table

-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Björn Wittich Sent: Tuesday, October 21, 2014 1:35 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] extremly bad select performance on huge table So

Re: [PERFORM] extremly bad select performance on huge table

Sorry forget to copy the buffer information: " Heap Fetches: 356861" " Buffers: shared hit=71799472 read=613813" Hi newsgroup, I have a very huge table (70 mio rows ) with a key (text length about 30 characters each key). A select on this indexed column "myprimkey" (index on column myc

[PERFORM] extremly bad select performance on huge table

Hi newsgroup, I have a very huge table (70 mio rows ) with a key (text length about 30 characters each key). A select on this indexed column "myprimkey" (index on column mycolumn) took more than 30 mins. Here is the explain (analyze,buffers) select mycolumn from myhugetable "Index Only Scan

Re: [PERFORM] Query with large number of joins

Marco Di Cesare writes: > COUNT(DISTINCT "foxtrot_india"."bravo_romeo") Ah. That explains why the planner doesn't want to use a hash aggregation step --- DISTINCT aggregates aren't supported with those. regards, tom lane -- Sent via pgsql-performance

Re: [PERFORM] Query with large number of joins

Andrew Dunstan writes: > Have you tried a) either turning off geqo or setting geqo_threshold > fairly high b) setting join_collapse_limit fairly high (assuming all the > above join targets are tables and not views, setting it to something > like 25 should do the trick. You'd have to do both, I

Re: [PERFORM] Query with large number of joins

On 10/21/2014 12:09 PM, Marco Di Cesare wrote: I did not mean to imply this works any better on SQL Server. We never tried. I just meant to say this is the first time we are using Postgres so we don't have much experience with it. We tried with work_mem set to 1GB (even as high as 3GB) but it

Re: [PERFORM] Query with large number of joins

I did not mean to imply this works any better on SQL Server. We never tried. I just meant to say this is the first time we are using Postgres so we don't have much experience with it. We tried with work_mem set to 1GB (even as high as 3GB) but it didn't change the GroupAggregate and Sort or que

Re: [PERFORM] Query with large number of joins

On Mon, Oct 20, 2014 at 3:32 PM, Marco Di Cesare wrote: > We are using Postgres for the first time after being SQLServer users for a > long time so forgive for being noobs. > > > > We are using a BI tool that generates a query with an unusually large number > of joins. My understanding is that wit

Re: [PERFORM] Query Performance Problem

2014-10-21 10:57 GMT-02:00 : > > > Hi all, > > I'm experimenting with table partitioning though inheritance. I'm testing > a query as follows: > > explain (analyze, buffers) > select response.id > from claim.response > where response.account_id = 4766 > and response.expire_timestamp is null > and

[PERFORM] Query Performance Problem

Hi all,I'm experimenting with table partitioning though inheritance. I'm testing a query as follows:explain (analyze, buffers)select response.idfrom claim.responsewhere response.account_id = 4766and response.expire_timestamp is nulland response.create_timestamp >= DATE '2014-08-01'order by create_t

Re: [PERFORM] Query with large number of joins

2014-10-20 21:59 GMT-02:00 Tom Lane : > Marco Di Cesare writes: > > We are using a BI tool that generates a query with an unusually large > number of joins. My understanding is that with this many joins Postgres > query planner can't possibly use an exhaustive search so it drops into a > heuristi

Re: [PERFORM] IS NOT NULL and LEFT JOIN

On Tue, Oct 21, 2014 at 2:58 AM, Tom Lane wrote: > Laurent Martelli writes: > > Do we agree that both queries are identical ? > > No, they *aren't* identical. Go consult any SQL reference. Left join > conditions don't work the way you seem to be thinking: after the join, > the RHS column might