Re: Deletes and large tables - Mailing list pgsql-general

Just did a sanity check. I dumped the DB schema, and there is indeed a
foreign key reference into the table. Now interestingly, the table
pointing in has no index on the column, but is a relatively small table
with only entries near the end of the large table.

So looks like I was getting CPU bound because of a sequental scan of the
smaller table per delete.

Dropped the constraint, and deletes are now much faster.

Regards!
Ed

On Fri, 10 Jun 2005, David Gagnon wrote:

>
> >This table has 3 foreign keys, but that should not matter during deletes.
> >In addition, the tables being referred to are small, and should be in
> >cache.
> >
> >
> I'm talking about FK that point this table... Not FK defined for this
> table that point to other table.  If Table A is referenced by 10 other
> tables 10 referencial check are needed.
>
>
> >There are no tables depending on it for references, so no dependent
> >triggers should be running.
> >
> >Also, if this was a foreign key issue, I would expect I/O issues/bounds
> >and not CPU.
> >
> >
> Maybe... I'm honetly not sure.
>
> Like I said in my previous mail... I got a similar problem (maybe not
> the same).  It was taking 10 minutes to delete 10k line in a table.  I
> turn on some log info in postgresql.conf and I saw that for each row
> deleted 4 selects were issued to check FK.  I drop those FK and the
> after the delete was taking less than a second.
>
>  Hope it help
> /David
>
>
>
> >Regards!
> >Ed
> >
> >
> >On Fri, 10 Jun 2005, Richard Huxton wrote:
> >
> >
> >
> >>Edmund Dengler wrote:
> >>
> >>
> >>>Greetings!
> >>>
> >>>We have a table with more than 250 million rows. I am trying to delete the
> >>>first 100,000 rows (based on a bigint primary key), and I had to cancel
> >>>after 4 hours of the system not actually finishing the delete. I wrote a
> >>>script to delete individual rows 10,000 at a time using transactions, and
> >>>am finding each individual delete takes on the order of 0.1 seconds to 2-3
> >>>seconds. There are 4 indexes on the table, one of which is very "hashlike"
> >>>(ie, distribution is throught the index for sequential rows).
> >>>
> >>>
> >>I don't suppose it's off checking foreign-keys in a lot of tables is it?
> >>
> >>--
> >>   Richard Huxton
> >>   Archonet Ltd
> >>
> >>
> >>
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 3: if posting/reading through Usenet, please send an appropriate
> >      subscribe-nomail command to majordomo@postgresql.org so that your
> >      message can get through to the mailing list cleanly
> >
> >
> >
>
>