On 02/23/2012 05:07 AM, Marcin Mańk wrote:
DELETE FROM B WHERE r_id IN (SELECT R.id FROM R, B WHERE r.end_date< (NOW() - (interval '1 day' * 30)) AND r.id = b.r_idHow about: DELETE FROM B WHERE r_id IN (SELECT distinct R.id FROM R WHERE r.end_date< (NOW() - (interval '1 day' * 30)) ?
Or possibly without the DISTINCT. But I agree that the original query shouldn't have B in the subquery - that alone could well make it crawl.
What is the distribution of end_dates? It might be worth running this in several steps, deleting records older than, say, 90 days, 60 days, 30 days.
cheers andrew -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
