Skip site navigation (1) Skip section navigation (2)

Re: DELETE performance problem

From: marcin mank <marcin(dot)mank(at)gmail(dot)com>
To: Luca Tettamanti <kronos(dot)it(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: DELETE performance problem
Date: 2009-11-25 15:22:47
Message-ID: b1b9fac60911250722n3a55c9a1v8a61657f112e9f2a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Nov 24, 2009 at 2:37 PM, Luca Tettamanti <kronos(dot)it(at)gmail(dot)com> wrote:
>         ->  HashAggregate  (cost=1031681.15..1033497.20 rows=181605 width=8) (a
> ctual time=571807.575..610178.552 rows=26185953 loops=1)


This is Your problem. The system`s estimate for the number of distinct
annotation_ids in t2 is wildly off.

The disk activity is almost certainly swapping (You can check it
iostat on the linux machine).

Can You try "analyze t2" just before the delete quety? maybe try
raising statistics target for the annotation_id column.

If all else fails, You may try "set enable_hashagg to false" just
before the query.

Greetings
Marcin Mańk


Greetings
Marcin Mańk

In response to

Responses

pgsql-performance by date

Next:From: Luca TettamantiDate: 2009-11-25 16:13:09
Subject: Re: DELETE performance problem
Previous:From: Tom LaneDate: 2009-11-25 15:22:03
Subject: Re: How exactly does Analyze work?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group