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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Page | 2010-01-27 15:43:53 | Re: test send (recommended by Dave Page) |
Previous Message | Matthew Wakeling | 2010-01-27 15:36:30 | Re: test send (recommended by Dave Page) |