Обсуждение: Cannot Delete

Hi,
hi have a table with 2.5 million records which i try do delete. i have
several constraints on it too.
i tried to delete the records using delete but it does not seem to work.
the delete runs forever. hrs...
i cannot truncate it as it complains about foreign keys.

What is the problem ?

Thanks
Alex

Not sure about 2.5 million records but try running "VACUUM ANALYSE"
before the delete and during (every now and then).

Had the same problem with 100,000 records and it did the trick nicely.

> Hi,
> hi have a table with 2.5 million records which i try do delete. i have
> several constraints on it too.
> i tried to delete the records using delete but it does not seem to
> work. the delete runs forever. hrs...
> i cannot truncate it as it complains about foreign keys.
>
> What is the problem ?
>
> Thanks
> Alex
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>

Alex <alex@meerkatsoft.com> writes:
> hi have a table with 2.5 million records which i try do delete. i have
> several constraints on it too.
> i tried to delete the records using delete but it does not seem to work.
> the delete runs forever. hrs...
> i cannot truncate it as it complains about foreign keys.

It's a good bet that you need to create indexes on the columns that
reference this table via foreign keys.  Without such indexes, updates
and deletes on the referenced table will be really slow.

            regards, tom lane

I still have this problem...
my table currently only has 60k records. it has two foreign keys . one
to a table with
15 records the other to a table with 250 records both with a primary key
as suggested.
the table itself is referenced by another table.

although I have about 10mio records in other tables , this one is rather
small... still i manage to delete 2-3 records / second.
I tried vacuum, the db and tables and did not gain any performance.

I experience this problem on different machines and yet, when i reload
the date it did speed up things considerably.

Any suggestions what could be wrong?  Maybe a configuration issue ?

Thanks
Alex


Tom Lane wrote:

>Alex <alex@meerkatsoft.com> writes:
>
>>hi have a table with 2.5 million records which i try do delete. i have
>>several constraints on it too.
>>i tried to delete the records using delete but it does not seem to work.
>>the delete runs forever. hrs...
>>i cannot truncate it as it complains about foreign keys.
>>
>
>It's a good bet that you need to create indexes on the columns that
>reference this table via foreign keys.  Without such indexes, updates
>and deletes on the referenced table will be really slow.
>
>            regards, tom lane
>
>
>

Alex wrote:

> I still have this problem...
> my table currently only has 60k records. it has two foreign keys . one
> to a table with
> 15 records the other to a table with 250 records both with a primary
> key as suggested.
> the table itself is referenced by another table.
>
> although I have about 10mio records in other tables , this one is
> rather small... still i manage to delete 2-3 records / second.
> I tried vacuum, the db and tables and did not gain any performance.
>
> I experience this problem on different machines and yet, when i reload
> the date it did speed up things considerably.
>
> Any suggestions what could be wrong?  Maybe a configuration issue ?

HOW is this table referenced in another table?
    By both of the foreign keys,
    a separate integer substitute key in this table?

BTW, what kind of keys are  you using, string, integer, float,what?

Please (re)post the table defintions to the 4 tables.

--
"You are behaving like a man",
is an insult from some women,
a compliment from an good woman.

Alex wrote:

> I tried vacuum, the db and tables and did not gain any performance.

Did you try VACUUM only or VACUUM ANALYZE (or even just ANALYZE)?

> I experience this problem on different machines and yet, when i reload
> the date it did speed up things considerably.

It does sound like you haven't ANALYZE'd your data. Try that.

--
Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386
GNU/Linux
   2:30pm  up 291 days,  6:04,  9 users,  load average: 6.08, 6.02, 6.01