Hello,
I ran into a slow delete issue.
The reason is explained by Tom Lane in
http://www.postgresql.org/message-id/16186.1150464632@sss.pgh.pa.us:
> I was about to ask if you had any. Usually the reason for DELETE being
> slow is that you have foreign key references to (not from) the table and
> the referencing columns aren't indexed. This forces a seqscan search
> of the referencing table for each row deleted :-(
> regards, tom lane
Before adding an index on the referencing column, delete ran for more than
12 hours with no result. Someone finally cancelled it.
After adding the index on the referencing column, delete ran in 6 seconds.
Explain gives me the same plan and same expected cost for both cases:
Explain for slow delete > 12 hours
http://explain.depesz.com/s/v5GH
Explain for Quick delete =3D 6 seconds
http://explain.depesz.com/s/lN2U
So I think explain needs an improvement as it does not show the real plan
used when running the query.
Instead of displaying:
Delete on uniform (cost=3D0.000..4,489.270 rows=3D57,847 width=3D6)
-> Index Scan using two on uniform (cost=3D0.000..4,489.270 rows=3D57,847
width=3D6)
Index Cond: ((bravo)::text =3D 'romeo'::text)
It should display something like this
Delete on uniform (cost=3D0.000..4,489.270 rows=3D57,847 width=3D6)
-> Index Scan using two on uniform (cost=3D0.000..4,489.270 rows=3D57,847
width=3D6)
Index Cond: ((bravo)::text =3D 'romeo'::text)
-> Foreign Keys check
-> Index Scan using ix_ref1 on referencing1 (cost=3D rows=3D width=3D)
Index Cond: ...
-> ...
-> Index Scan using ix_refN on referencingN (cost=3D rows=3D width=3D)
Index Cond: ...
Test environnement:
- Windows 7
- PostgreSQL 9.2
Cordialement,
*Ghislain ROUVIGNAC*
ghr@sylob.com
<http://www.sylob.com/>
7 rue Marcel Dassault - Z.A. La Mouline - 81990 Cambon d'Albi - FRANCE
Tel : 05 63 53 08 18 - Fax : 05 63 53 07 42 - www.sylob.com
Entreprise certifi=C3=A9e ISO 9001 version 2008 par Bureau Veritas.
*Retrouvez prochainement SYLOB =C3=A0 l'occasion **du salon du Bourget du 1=
5 au
21 juin - Stand B136 Hall 4 - Pavillon Aerospace Valley.*
*Venez =C3=A9changer et partager votre exp=C3=A9rience lors de la journ=C3=
=A9e clients
Sylob 1, 5 et 9 du 26 juin dans nos locaux de Cambon=E2=80=8B=E2=80=8B.*
<http://twitter.com/SylobErp> <http://www.google.com/+sylob>
<http://www.viadeo.com/fr/company/sylob-sas>
<http://www.linkedin.com/company/sylob>
