Someday I'd like to set up a script that takes the query-plan returned by PostgreSQL's EXPLAIN statement and turns it into a diagram with a node for each operation and arrows for dependencies, where the arrow width is proportional to the number of rows involved (again, much like SQL Server's query plan results). This would make it easier to spot the hotspots in a complex plan.
My favourite GraphViz tool would probably be XDot:
http://code.google.com/p/jrfonseca/wiki/XDot
I will say that the trickiest bits were extracting the exact information from the database, and figuring out the how to make arrows point to and from individual fields, instead of from the table in general.
Getting tables and fields from PostgreSQL:
select
table_schema,
table_schema || '.' || table_name as qualified_table,
column_name,
data_type
from information_schema.columns
order by table_schema, table_name, ordinal_position;
select
kcu.table_schema,
kcu.table_schema || '.' || kcu.table_name as qualified_table,
kcu.column_name,
ccu.table_schema as referenced_table_schema,
ccu.table_schema || '.' || ccu.table_name
as referenced_qualified_table,
ccu.column_name as referenced_column_name
from information_schema.constraint_column_usage as ccu
join information_schema.key_column_usage as kcu
using (constraint_catalog, constraint_schema, constraint_name)
where ccu.table_schema != kcu.table_schema
or ccu.table_name != kcu.table_name;
Thanks for the hints.
One idea I have is to use "dot" to give auto-layout capabilities to a GUI that has a simple canvas. Right now, any data set that wasn't created by a GUI user (e.g. automatically generated) looks ugly, because it has no useful layout information. Since "dot" knows how to do layout, it would seem possible to run it in the background, parse the results, and use the node positions to make the default canvas look much more intelligent.