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

Re: Benchmark shows very slow bulk delete

From: Nikolas Everett <nik9000(at)gmail(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: Thom Brown <thombrown(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Benchmark shows very slow bulk delete
Date: 2010-01-27 15:37:22
Message-ID: d4e11e981001270737i1f154f3ame66ab83880df6a83@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Jan 27, 2010 at 9:54 AM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> It is if you don't have an index on the table which has a foreign
> key defined which references the table in which you're doing
> deletes.  The author of the benchmark apparently didn't realize that
> MySQL automatically adds such an index to the dependent table, while
> PostgreSQL leaves it to you to decide whether to add such an index.
> For "insert-only" tables, it isn't always worth the cost of
> maintaining it.
>
>
It really gets to me that I have to not use some foreign keys in MySQL
because I can't afford to maintain the index.  I have to write super fun
"check constraints" that look like

DELIMITER \\
CREATE TRIGGER Location_Pre_Delete BEFORE DELETE ON Locations FOR EACH ROW
BEGIN
  DECLARE _id INT;
  SELECT id INTO _id FROM BigHistoryTable WHERE locationId = OLD.id LIMIT 1;
  IF _id IS NOT NULL THEN
    INSERT INTO BigHistoryTable
(column_that_does_not_exist_but_says_that_you_violated_my_hacked_foreign_key)
VALUES ('fail');
  END IF;
END\\

Sometimes I can't sleep at night for having written that code.

In response to

pgsql-performance by date

Next:From: Dave PageDate: 2010-01-27 15:43:53
Subject: Re: test send (recommended by Dave Page)
Previous:From: Matthew WakelingDate: 2010-01-27 15:36:30
Subject: Re: test send (recommended by Dave Page)

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