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@mobile-devices.fr]
Sent: 08 July 2011 10:20
To: French, Martin
Cc: pgsql-general@postgresql.org; pgsql-performance@postgresql.org
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
__________________________________________________
