Postgres Pro
Downloads
Home   >   mailing lists

Thread: Slow delete

Slow delete

From
"Colin Gillespie"
Date:
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

Re: Slow delete

From
Sean Davis
Date:
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

Re: Slow delete

From
Andrew Hammond
Date:
-----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-----

Re: Slow delete

From
"Colin Gillespie"
Date:
>
>
>-----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