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:vincent.dephily@xxxxxxxxxxxxxxxxx] Sent: 08 July 2011 10:20 To: French, Martin Cc: pgsql-general@xxxxxxxxxxxxxx; pgsql-performance@xxxxxxxxxxxxxx Subject: Re: 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 __________________________________________________ -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
