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

Re: DELETE vs TRUNCATE explanation

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: Harold A(dot) Giménez <harold(dot)gimenez(at)gmail(dot)com>
Cc: Daniel Farina <daniel(at)heroku(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: DELETE vs TRUNCATE explanation
Date: 2012-08-09 18:06:54
Message-ID: CAMkU=1x36-xLdUORNZCXZh4yt+qC0z+Qqcyj7Y-gfPgFt2w2FA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
On Thu, Jul 12, 2012 at 4:21 PM, Harold A. Giménez
<harold(dot)gimenez(at)gmail(dot)com> wrote:
> Hi,
>
> I work with Daniel Farina and was the other engineer who "discovered" this,
> once again. That is, I got bit by it and have been running TRUNCATE on my
> test suites for years.

Hi Daniel and Harold,

I don't know if you followed this thread over into the -hacker mailing list.

There was some bookkeeping code that was N^2 in the number of
truncations performed during any given checkpoint cycle.  That has
been fixed in 9.2Beta3.

I suspect that this was the root cause of the problem you encountered.

If you are in a position to retest using 9.2Beta3
(http://www.postgresql.org/about/news/1405/), I'd be interested to
know if it does make truncations comparable in speed to unqualified
deletes.

Thanks,

Jeff

In response to

pgsql-performance by date

Next:From: Joseph MarlinDate: 2012-08-10 17:35:16
Subject: Increasing WAL usage followed by sudden drop
Previous:From: Jeff JanesDate: 2012-08-09 16:17:20
Subject: Re: Slow query: Select all buildings that have >1 pharmacies and >1 schools within 1000m

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2012-08-09 18:21:25
Subject: Re: proposal - assign result of query to psql variable
Previous:From: Robert HaasDate: 2012-08-09 17:48:03
Subject: Re: [WIP] Performance Improvement by reducing WAL for Update Operation

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