debug_print_plan logs table alias used in join, not table name itself

Поиск
Список
Период
Сортировка
От frank joerdens
Тема debug_print_plan logs table alias used in join, not table name itself
Дата
Msg-id AANLkTimMau3-y9+-=SnM6v9Hc6ASjDKk0hUwhXQyfJBu@mail.gmail.com
обсуждение исходный текст
Ответы Re: debug_print_plan logs table alias used in join, not table name itself
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Florian Weimer
Дата:
Сообщение: Re: why sometimes checkpoint is too slow????
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Error during a dump (ts_selectivity, not found)