468,761 Members | 1,806 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,761 developers. It's quick & easy.

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

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

Nov 11 '05 #1
5 2012
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)

Nov 11 '05 #2
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

Nov 11 '05 #3
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

Nov 12 '05 #4
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

Nov 12 '05 #5
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-----

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

20 posts views Thread by Brad Eck | last post: by
2 posts views Thread by Natan | last post: by
4 posts views Thread by gerardianlewis | last post: by
8 posts views Thread by Stefano Sabatini | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.