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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
