Обсуждение: Slow delete
Dear All, I have four tables linked together with FK. An example is, deleting an id in one table, deletes 1,000,000 rows in all theothers. However, this operation takes 2-3mins (currently this more or less empties the database.) Is this slow? I have checked that the FK have been indexed and that they have been declared intergers. A full vacuum has also been performed. Does anyone have any suggestions on how to speed this up? Thanks Colin
Colin, I'm not sure, but if the larger table could be clustered by the foreign key index, would that speed things up? This only works if clustering makes any sense for the table. See this link for clustering (http://www.postgresql.org/docs/7.4/static/sql-cluster.html). Perhaps someone else will add some more detailed comments. Sean On Dec 7, 2004, at 5:54 AM, Colin Gillespie wrote: > Dear All, > > I have four tables linked together with FK. An example is, deleting an > id in one table, deletes 1,000,000 rows in all the others. However, > this operation takes 2-3mins (currently this more or less empties the > database.) Is this slow? > > I have checked that the FK have been indexed and that they have been > declared intergers. A full vacuum has also been performed. > > Does anyone have any suggestions on how to speed this up? > > Thanks > > Colin > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Colin Gillespie wrote: | Dear All, | | I have four tables linked together with FK. An example is, deleting an id | in one table, deletes 1,000,000 rows in all the others. However, this | operation takes 2-3mins (currently this more or less empties the database.) | Is this slow? Depends on what hardware you're using. It sounds like you've done the right thing so far. I assume you've run explain on the stuff involved to make sure it's actually using those indexes. | | I have checked that the FK have been indexed and that they have been declared intergers. A full vacuum has also been performed. | | Does anyone have any suggestions on how to speed this up? | | Thanks | | Colin | | ---------------------------(end of broadcast)--------------------------- | TIP 4: Don't 'kill -9' the postmaster - -- Andrew Hammond 416-673-4138 ahammond@ca.afilias.info Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQFBteT6gfzn5SevSpoRAkCCAKCV4Zojyw52c9POrjoHsnK0zjQDEgCgoQZw hdDUThg/ojIojRWrLtiKBZg= =Xs0l -----END PGP SIGNATURE-----
> > >-----BEGIN PGP SIGNED MESSAGE----- >Hash: SHA1 > >Colin Gillespie wrote: >| Dear All, >| >| I have four tables linked together with FK. An example is, >deleting an >| id in one table, deletes 1,000,000 rows in all the others. However, >| this operation takes 2-3mins (currently this more or less empties the >database.) >| Is this slow? > >Depends on what hardware you're using. It sounds like you've >done the right thing so far. I assume you've run explain on >the stuff involved to make sure it's actually using those indexes. We're have a 2.4Ghz Xeon with 2GB RAM. I've ran explain on SELECT queries and they use the indexes. So I presume that the delete would also use them. When I use cluster it takes the time down to a few seconds . Is this the best way then? Thanks again Colin