Re: DELETE performance problem

On Wed, Nov 25, 2009 at 04:22:47PM +0100, marcin mank wrote:
> 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.

Ah, I see.

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

Nope, zero swap activity. Under Linux postgres tops up at about 4.4GB, leaving
3.6GB of page cache (nothing else is running right now).

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

I already tried, the estimation is still way off.

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

Hash IN Join (cost=1879362.27..11080576.17 rows=202376 width=6) (actual time=250281.607..608638.141 rows=26185953 loops=1)
Hash Cond: (t1.annotation_id = t2.annotation_id)
-> Seq Scan on t1 (cost=0.00..661734.12 rows=45874812 width=14) (actual time=0.017..193661.353 rows=45874812 loops=1)
-> Hash (cost=879289.12..879289.12 rows=60956812 width=8) (actual time=250271.012..250271.012 rows=60956812 loops=1)
-> Seq Scan on t2 (cost=0.00..879289.12 rows=60956812 width=8) (actual time=0.023..178297.862 rows=60956812 loops=1)
Total runtime: 900019.033 ms
(6 rows)

This is after an analyze.

The alternative query suggested by Shrirang Chitnis:

DELETE FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.annotation_id = t2.annotation_id)

performs event better:

Seq Scan on t1 (cost=0.00..170388415.89 rows=22937406 width=6) (actual time=272.625..561241.294 rows=26185953 loops=1)
Filter: (subplan)
SubPlan
-> Index Scan using t2_idx on t2 (cost=0.00..1113.63 rows=301 width=0) (actual time=0.008..0.008 rows=1 loops=45874812)
Index Cond: ($0 = annotation_id)
Total runtime: 629426.014 ms
(6 rows)

Will try on the full data set.

thanks,
Luca