Обсуждение: 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