Обсуждение: Q: explain on delete
Hi, today I was trying to figure out, what's going on during a delete on a table with several foreign key triggers (and "on delete cascade"). But, as found in the achives, "EXPLAIN on a delete isn't very interesting.." because it doesn tell me, what going on behind the scenes, where the triggers are running. So my question is: Is there another way besides "explain" to get information out of postgresql (7.3.1) what's going on during a "delete" on a table with many foreign key triggers . The only other option what comes to my mind is to inspect the foreign key dependencies and check manually, if some index is missing (But I was hoping, the maching could tell me that...). Best regards and thanks a lot, Tilo
Tilo Schwarz <mail@tilo-schwarz.de> writes: > So my question is: Is there another way besides "explain" to get information > out of postgresql (7.3.1) what's going on during a "delete" on a table with > many foreign key triggers . IIRC, you can set log_statement=on before the first time you run the DELETE in a given session, and then look in the postmaster log for copies of the SQL statements that are generated by the foreign key mechanism. It's not great, particularly because you can't conveniently see the associated plans, but it's better than nothing ... regards, tom lane
On Mon, Feb 03, 2003 at 05:01:17PM -0500, Tom Lane wrote: > mechanism. It's not great, particularly because you can't conveniently > see the associated plans, but it's better than nothing ... But you can take the thereby-discovered queries and pass them to EXPLAIN manually. It's a bit of a pain, but this is how I have found some real performance problems. (Sometimes it just leaps out at you, actually, when you see the query.) A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110
Andrew Sullivan <andrew@libertyrms.info> writes: > On Mon, Feb 03, 2003 at 05:01:17PM -0500, Tom Lane wrote: >> mechanism. It's not great, particularly because you can't conveniently >> see the associated plans, but it's better than nothing ... > But you can take the thereby-discovered queries and pass them to > EXPLAIN manually. It's a bit of a pain, but this is how I have found > some real performance problems. Actually, the problem with looking at the plan for an FK query is that the query will involve parameters ($n), and so you can't just stick it into EXPLAIN as-is. (Substituting constants for the $n symbols does *not* necessarily give the same plan, although it may be close enough to give you a clue about major problems like needing to add an index.) As of CVS tip there's finally a real solution: you can PREPARE the parameterized query and then EXPLAIN EXECUTE it. For example, given something like SELECT * FROM foo WHERE col = $1 you could do PREPARE q1(int) AS SELECT * FROM foo WHERE col = $1 EXPLAIN EXECUTE q1(42) regards, tom lane
On Tue, Feb 04, 2003 at 11:24:03AM -0500, Tom Lane wrote: > *not* necessarily give the same plan, although it may be close enough > to give you a clue about major problems like needing to add an index.) Yes, this was what I meant. Sorry, I should have been clearer. What I find frequently, actually, is that it more often makes you notice something about the distribution of data. The cases which really kill you with FKs are (a) there is contention on one of the tables, so that you end up with everything waiting for their turn at the lock and (b) referenced tables which are really big but have a small number of values. Case (b) is interesting, because it's possible to (mis)design a system which never encounters the symptom directly, and only runs into it with a trigger. > As of CVS tip there's finally a real solution: you can PREPARE the > parameterized query and then EXPLAIN EXECUTE it. For example, Oh, _nice_. I look forward to that. A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110
Tom Lane writes: > As of CVS tip there's finally a real solution: you can PREPARE the > parameterized query and then EXPLAIN EXECUTE it. For example, > given something like > > SELECT * FROM foo WHERE col = $1 > > you could do > > PREPARE q1(int) AS SELECT * FROM foo WHERE col = $1 > EXPLAIN EXECUTE q1(42) Nice, I'll try that... Regards, Tilo