Cannot Delete

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.

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 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