Re: [PERFORM] anti-join chosen even when slower than old plan

you're joining on more than one key. That always hurts performance. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Huge overestimation in rows expected results in bad plan

On Tue, Nov 9, 2010 at 3:55 PM, Tom Lane wrote: > bricklen writes: >> On Tue, Nov 9, 2010 at 3:29 PM, Tom Lane wrote: >>> The query doesn't seem to match the plan.  Where is that OR (c.id = >>> 38441828354::bigint) condition coming from? > >> Ah sorry, I was testing it with and without that part

Re: [PERFORM] anti-join chosen even when slower than old plan

"Kevin Grittner" writes: > The semi-join and anti-join have helped us quite a bit, but we have > seen a situation where anti-join is chosen even though it is slower > than the "old fashioned" plan. I know there have been other reports > of this, but I just wanted to go on record with my details.

Re: [PERFORM] Huge overestimation in rows expected results in bad plan

bricklen writes: > On Tue, Nov 9, 2010 at 3:29 PM, Tom Lane wrote: >> The query doesn't seem to match the plan.  Where is that OR (c.id = >> 38441828354::bigint) condition coming from? > Ah sorry, I was testing it with and without that part. Here is the > corrected query, with that as part of t

Re: [PERFORM] Huge overestimation in rows expected results in bad plan

On Tue, Nov 9, 2010 at 3:29 PM, Tom Lane wrote: > bricklen writes: >> I have a query that is getting a pretty bad plan due to a massively >> incorrect count of expected rows. > > The query doesn't seem to match the plan.  Where is that OR (c.id = > 38441828354::bigint) condition coming from? > >

Re: [PERFORM] Huge overestimation in rows expected results in bad plan

bricklen writes: > I have a query that is getting a pretty bad plan due to a massively > incorrect count of expected rows. The query doesn't seem to match the plan. Where is that OR (c.id = 38441828354::bigint) condition coming from? regards, tom lane -- Sent via pgsql

Re: [PERFORM] anti-join chosen even when slower than old plan

Tom Lane wrote: > However, you'd have to be spending a lot of time chasing through > long HOT chains before that would happen enough to make this a > hotspot... That makes it all the more mysterious, then. These tables are insert-only except for a weekly delete of one week of the oldest data.

Re: [PERFORM] anti-join chosen even when slower than old plan

"Kevin Grittner" writes: > "Kevin Grittner" wrote: >> samples %symbol name >> 2320174 33.7617 index_getnext > I couldn't resist seeing where the time went within this function. > Over 13.7% of the opannotate run time was on this bit of code: > /* >* The xmin should match the p

Re: [PERFORM] anti-join chosen even when slower than old plan

"Kevin Grittner" wrote: > samples %symbol name > 2320174 33.7617 index_getnext I couldn't resist seeing where the time went within this function. Over 13.7% of the opannotate run time was on this bit of code: /* * The xmin should match the previous xmax value, else chain is

Re: [PERFORM] Huge overestimation in rows expected results in bad plan

On Tue, Nov 9, 2010 at 2:48 PM, Andy Colson wrote: > On 11/9/2010 3:26 PM, bricklen wrote: > >>          ->   Seq Scan on conversionrejected cr  (cost=0.00..191921.82 >> rows=11012682 width=31) (actual time=0.003..1515.816 rows=11012682 >> loops=72) >>  Total runtime: 292668.992 ms >> > > Looks li

Re: [PERFORM] Huge overestimation in rows expected results in bad plan

On 11/9/2010 3:26 PM, bricklen wrote: Hi, I have a query that is getting a pretty bad plan due to a massively incorrect count of expected rows. All tables in the query were vacuum analyzed right before the query was tested. Disabling nested loops gives a significantly faster result (4s vs 292s).

[PERFORM] Huge overestimation in rows expected results in bad plan

Hi, I have a query that is getting a pretty bad plan due to a massively incorrect count of expected rows. All tables in the query were vacuum analyzed right before the query was tested. Disabling nested loops gives a significantly faster result (4s vs 292s). Any thoughts on what I can change to ma

[PERFORM] anti-join chosen even when slower than old plan

The semi-join and anti-join have helped us quite a bit, but we have seen a situation where anti-join is chosen even though it is slower than the "old fashioned" plan. I know there have been other reports of this, but I just wanted to go on record with my details. The query: delete from "DbTranLo

Re: [PERFORM] out of memory problem

Be sure that you are starting PostgreSQL using an account with sufficient memory limits: ulimit -m If the account has memory limit below the server's configuration you may get the out of memory error. Bob Lunney --- On Tue, 11/9/10, Till Kirchner wrote: > From: Till Kirchner > Subject: [

Re: [PERFORM] out of memory problem

Till Kirchner writes: > I get an out of memory problem I don't understand. It's pretty clear that something is leaking memory in the per-query context: >ExecutorState: 1833967692 total in 230 blocks; 9008 free (3 > chunks); 1833958684 used There doesn't seem to be anything in your quer

[PERFORM] out of memory problem

Hello together, I get an out of memory problem I don't understand. The installed Postgres-Version is: PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.3-5) 4.3.3 It is running on a 32bit Debian machine with 4GB RAM. Thanks for any help in advance Till -- --