Thread: debug_print_plan logs table alias used in join, not table name itself
I was just experimenting with debug logging on 8.3 and am finding that I can't get it to log the table names involved in a given query, it will always print the table alias used in your join instead, e.g. explaining a query such as woome=# explain select * from webapp_person p join auth_user a on a.id = p.user_id limit 1; QUERY PLAN ------------------------------------------------------------------------------------------------ Limit (cost=0.00..0.54 rows=1 width=1187) -> Nested Loop (cost=0.00..498070.40 rows=929749 width=1187) -> Seq Scan on webapp_person p (cost=0.00..105530.49 rows=929749 width=1069) -> Index Scan using auth_user_pkey on auth_user a (cost=0.00..0.41 rows=1 width=118) Index Cond: (a.id = p.user_id) (5 rows) yields both table name and alias; but logging with the settings debug_print_parse = on debug_print_rewritten = on debug_print_plan = on debug_pretty_print = on log_min_messages = 'DEBUG5' only gives you ... {ALIAS :aliasname p ... {ALIAS :aliasname a ... ie the actual table name is not in the debug output at all. If you alter the syntax in the above query to use the table names directly rather than aliases like select * from webapp_person join auth_user on auth_user.id = webapp_person.user_id limit 1 you do get the names because alias coincides with table name in that case: ... {ALIAS :aliasname webapp_person ... {ALIAS :aliasname auth_user ... But it's thoroughly infeasible and undesirable for us to alter every query in the codebase now to avoid using table aliases. The objective here is to extract from the logs all combinations of tables that are used in joins together so we know which tables/groups of tables we have to keep together on physical postgres instances while we scale out horizantally. Is that fixable somehow for 8.3? Perhaps we can set a flag that tells the logger to emit the table name instead of the alias, or both, and then recompile? Regards, Frank
frank joerdens <fiskadoro@gmail.com> writes: > I was just experimenting with debug logging on 8.3 and am finding that > I can't get it to log the table names involved in a given query, it > will always print the table alias used in your join instead, e.g. I don't know why you think that debug_print_plan is meant to do any such thing; it isn't meant to be useful to end users at all. You could get the table identity from the relation OID that's in the plan tree, but the name is not there. In 8.4 and up there's an "auto_explain" contrib module that is probably much closer to what you want. regards, tom lane
On Wed, Jan 19, 2011 at 7:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > frank joerdens <fiskadoro@gmail.com> writes: >> I was just experimenting with debug logging on 8.3 and am finding that >> I can't get it to log the table names involved in a given query, it >> will always print the table alias used in your join instead, e.g. > > I don't know why you think that debug_print_plan is meant to do any such > thing; it isn't meant to be useful to end users at all. You could get > the table identity from the relation OID that's in the plan tree, but > the name is not there. Oh, that's great - the OID will do just fine (missed that it was there). Thanks! Regards, Frank