Cannot Delete

[Alex]

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

[Tim McAuley]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

Alex <al**@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Alex]

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 <al**@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Dennis Gearon]

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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Ang Chin Han]

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/ikvSNYbTUIgzwfARAkucAJ490ss7DOvus/WKIJHnA/V7vHJMtgCfd1wi
o0yaK7VHCPIeQFyq1iSGtiA=
=vVqN
-----END PGP SIGNATURE-----