Thread: Incomplete Explain for delete
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>
On Mon, Jun 22, 2015 at 11:47 AM, Ghislain ROUVIGNAC <ghr@sylob.com> wrote: > 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: ... > > The underlying limitation here is that the planner does not concern itself with triggers. =E2=80=8BThere is definitely room for improvement here but this complaint b= y itself is not particularly influential to me.=E2=80=8B The black-box nature of fu= nctions makes anything more detailed than "this table has triggers" difficult - though maybe FK check triggers could be special-cased. David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Jun 22, 2015 at 11:47 AM, Ghislain ROUVIGNAC <ghr@sylob.com> wrote:
>> So I think explain needs an improvement as it does not show the real plan
>> used when running the query.
> The underlying limitation here is that the planner does not concern itself
> with triggers.
> âThere is definitely room for improvement here but this complaint by itself
> is not particularly influential to me.â The black-box nature of functions
> makes anything more detailed than "this table has triggers" difficult -
> though maybe FK check triggers could be special-cased.
Well, even if EXPLAIN special-cased FK triggers, it would have a hard time
seeing the plan used for the queries done inside the triggers.
It is true that if you run EXPLAIN ANALYZE, it will show you the amount of
time spent in each trigger, which at least is enough to point the finger
in the right direction. I realize that's not much help if the query takes
so long that you can't EXPLAIN ANALYZE it :-(.
I don't remember at the moment whether contrib/auto_explain is capable of
capturing FK-trigger-fired queries, but if it is, that might be a possible
avenue to seeing what's happening.
regards, tom lane
On Mon, Jun 22, 2015 at 12:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "David G. Johnston" <david.g.johnston@gmail.com> writes: > > On Mon, Jun 22, 2015 at 11:47 AM, Ghislain ROUVIGNAC <ghr@sylob.com> > wrote: > >> So I think explain needs an improvement as it does not show the real > plan > >> used when running the query. > > > The underlying limitation here is that the planner does not concern > itself > > with triggers. > > > =E2=80=8BThere is definitely room for improvement here but this complai= nt by > itself > > is not particularly influential to me.=E2=80=8B The black-box nature o= f > functions > > makes anything more detailed than "this table has triggers" difficult - > > though maybe FK check triggers could be special-cased. > > Well, even if EXPLAIN special-cased FK triggers, it would have a hard tim= e > seeing the plan used for the queries done inside the triggers. > =E2=80=8B > =E2=80=8BExcept that the query inside the trigger is known to system - the = fact it is wrapped in a trigger is an implementation detail that could, in theory, be bypassed in order to facilitate a more meaningful explain output. =E2=80=8BDavid J.=E2=80=8B =E2=80=8B
On Mon, 22 Jun 2015 12:41:28 -0400 "David G. Johnston" <david.g.johnston@gmail.com> wrote: > =E2=80=8BExcept that the query inside the trigger is known to system - th= e fact it > is wrapped in a trigger is an implementation detail that could, in theory, > be bypassed in order to facilitate a more meaningful explain output. Unless triggers are prohibited from using dynamic sql, the query really cannot be known to the system. -dg --=20 David Gould 510 282 0869 daveg@sonic.net If simplicity worked, the world would be overrun with insects.
On Mon, Jun 22, 2015 at 6:32 PM, David Gould <daveg@sonic.net> wrote: > On Mon, 22 Jun 2015 12:41:28 -0400 > "David G. Johnston" <david.g.johnston@gmail.com> wrote: > > > =E2=80=8BExcept that the query inside the trigger is known to system - = the fact > it > > is wrapped in a trigger is an implementation detail that could, in > theory, > > be bypassed in order to facilitate a more meaningful explain output. > > Unless triggers are prohibited from using dynamic sql, the query really > cannot be known to the system. > =E2=80=8B > =E2=80=8BMaybe that is the case here as well but the code that is used in t= he FK trigger is maintained by the core PostgreSQL project and seldom changes. Having explain notice that an FK trigger is present and then applying some discovery to determine the source table and columns of said FK trigger seems theoretically possible.=E2=80=8B It does not look inside the trigger= - instead it is explicitly told what a FK trigger does. I am strictly considering FK triggers here - no other kind and especially not user-defined ones. David J.