Incomplete Explain for delete
Hello,
I ran into a slow delete issue.
The reason is explained by Tom Lane in
/messages/by-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 = 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=0.000..4,489.270 rows=57,847 width=6)
-> Index Scan using two on uniform (cost=0.000..4,489.270 rows=57,847
width=6)
Index Cond: ((bravo)::text = 'romeo'::text)
It should display something like this
Delete on uniform (cost=0.000..4,489.270 rows=57,847 width=6)
-> Index Scan using two on uniform (cost=0.000..4,489.270 rows=57,847
width=6)
Index Cond: ((bravo)::text = 'romeo'::text)
-> Foreign Keys check
-> Index Scan using ix_ref1 on referencing1 (cost= rows= width=)
Index Cond: ...
-> ...
-> Index Scan using ix_refN on referencingN (cost= rows= width=)
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ée ISO 9001 version 2008 par Bureau Veritas.
*Retrouvez prochainement SYLOB à l'occasion **du salon du Bourget du 15 au
21 juin - Stand B136 Hall 4 - Pavillon Aerospace Valley.*
*Venez échanger et partager votre expérience lors de la journée clients
Sylob 1, 5 et 9 du 26 juin dans nos locaux de Cambon.*
<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=0.000..4,489.270 rows=57,847 width=6)
-> Index Scan using two on uniform (cost=0.000..4,489.270 rows=57,847
width=6)
Index Cond: ((bravo)::text = 'romeo'::text)
It should display something like this
Delete on uniform (cost=0.000..4,489.270 rows=57,847 width=6)
-> Index Scan using two on uniform (cost=0.000..4,489.270 rows=57,847
width=6)
Index Cond: ((bravo)::text = 'romeo'::text)
-> Foreign Keys check
-> Index Scan using ix_ref1 on referencing1 (cost= rows= width=)
Index Cond: ...
-> ...
-> Index Scan using ix_refN on referencingN (cost= rows= width=)
Index Cond: ...
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.
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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.
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.
Except 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.
David J.
On Mon, 22 Jun 2015 12:41:28 -0400
"David G. Johnston" <david.g.johnston@gmail.com> wrote:
Except 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.
-dg
--
David Gould 510 282 0869 daveg@sonic.net
If simplicity worked, the world would be overrun with insects.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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:Except 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.
Maybe that is the case here as well but the code that is used in the 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. 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.