Re: DELETE taking too much memory

From: French, Martin
Subject: Re: DELETE taking too much memory
Date: ,
Msg-id: 81976671721DF04B9DCA6ECD87941A402B40FBD5@roundway.Cromwell-tools.co.uk
(view: Whole thread, Raw)
In response to: DELETE taking too much memory  (vincent dephily)
Responses: Re: DELETE taking too much memory  (Vincent de Phily)
List: pgsql-performance

Tree view

DELETE taking too much memory  (vincent dephily, )
 Re: [GENERAL] DELETE taking too much memory  (Guillaume Lelarge, )
  Re: [GENERAL] DELETE taking too much memory  (Dean Rasheed, )
   Re: [GENERAL] DELETE taking too much memory  (Jose Ildefonso Camargo Tolosa, )
   Re: [GENERAL] DELETE taking too much memory  (Vincent de Phily, )
  Re: [GENERAL] DELETE taking too much memory  (Vincent de Phily, )
 Re: [GENERAL] DELETE taking too much memory  (Dean Rasheed, )
  Re: [GENERAL] DELETE taking too much memory  (Claudio Freire, )
 Re: DELETE taking too much memory  ("French, Martin", )
  Re: DELETE taking too much memory  (Vincent de Phily, )
 Re: DELETE taking too much memory  ("French, Martin", )
  Re: DELETE taking too much memory  (Vincent de Phily, )

If the query planner thinks it has the default amount of memory (128MB)
and the stats are out of date, then it will by no means be able to plan
proper execution.

I would recommend setting the effective_cache_size to an appropriate
value, running "analyze" on both tables with an appropriate stats
target, and then explaining the query again to see if it's more
accurate.

Cheers

-----Original Message-----
From: Vincent de Phily [mailto:]
Sent: 08 July 2011 10:20
To: French, Martin
Cc: ; 
Subject: Re: [PERFORM] DELETE taking too much memory

On Thursday 07 July 2011 19:54:08 French, Martin wrote:
> How up to date are the statistics for the tables in question?
>
> What value do you have for effective cache size?
>
> My guess would be that planner thinks the method it is using is right
> either for its current row number estimations, or the amount of memory
> it thinks it has to play with.

Not very up to date I'm afraid (as shown by the low estimate of deleted
rows).
Table t2 has been insert-only since its re-creation (that's another
story),
while t1 is your classic insert-many, update-recent.

We haven't tweaked effective cache size yet, it's on the TODO... like
many
other things :/
--
Vincent de Phily

___________________________________________________

This email is intended for the named recipient. The information contained
in it is confidential.  You should not copy it for any purposes, nor
disclose its contents to any other party.  If you received this email
in error, please notify the sender immediately via email, and delete it from
your computer.

Any views or opinions presented are solely those of the author and do not
necessarily represent those of the company.

PCI Compliancy: Please note, we do not send or wish to receive banking, credit
or debit card information by email or any other form of communication.

Please try our new on-line ordering system at http://www.cromwell.co.uk/ice

Cromwell Tools Limited, PO Box 14, 65 Chartwell Drive
Wigston, Leicester LE18 1AT. Tel 0116 2888000
Registered in England and Wales, Reg No 00986161
VAT GB 115 5713 87 900
__________________________________________________



pgsql-performance by date:

From: Pavel Stehule
Date:
Subject: Re: INSERT query times
From: Anish Kejariwal
Date:
Subject: Re: issue with query optimizer when joining two partitioned tables