On Nov 7, 2010, at 5:24 AM, Roberto Mello wrote:
> Yes, but I am wondering whether you should just stick to what would
> come out of a normal explain, for consistency sake. Maybe provide
> another function, or parameter that would cast the results to
> intervals?
I think it's more convenient to have intervals.
So my final list:
CREATE TABLE plans (
planned_at TIMESTAMPTZ,
node_id TEXT PRIMARY KEY,
parent_id TEXT REFERENCES plans(node_id),
node_type TEXT NOT NULL,
total_runtime INTERVAL,
strategy TEXT,
operation TEXT,
startup_cost FLOAT,
total_cost FLOAT,
plan_rows FLOAT,
plan_width INTEGER,
actual_startup_time INTERVAL,
actual_total_time INTERVAL,
actual_rows FLOAT,
actual_loops FLOAT,
parent_relationship TEXT,
sort_key TEXT[],
sort_method TEXT[],
sort_space_used BIGINT,
sort_space_type TEXT,
join_type TEXT,
join_filter TEXT,
hash_cond TEXT,
relation_name TEXT,
alias TEXT,
scan_direction TEXT,
index_name TEXT,
index_cond TEXT,
recheck_cond TEXT,
tid_cond TEXT,
merge_cond TEXT,
subplan_name TEXT,
function_name TEXT,
function_call TEXT,
filter TEXT,
one_time_filter TEXT,
command TEXT,
shared_hit_blocks BIGINT,
shared_read_blocks BIGINT,
shared_written_blocks BIGINT,
local_hit_blocks BIGINT,
local_read_blocks BIGINT,
local_written_blocks BIGINT,
temp_read_blocks BIGINT,
temp_written_blocks BIGINT,
output TEXT[],
hash_buckets BIGINT,
hash_batches BIGINT,
original_hash_batches BIGINT,
peak_memory_usage BIGINT,
schema TEXT,
cte_name TEXT,
triggers trigger_plan[]
);
planned_at is just the current time (from NOW()).
node_id is simply `md5( pg_backend_pid() || clock_timestamp() )`, which is run
just before each node is parsed.
trigger_plan is a composite type:
CREATE TYPE trigger_plan AS (
trigger_name TEXT,
constraint_name TEXT,
relation TEXT,
time INTERVAL,
calls FLOAT
);
I'm wondering if there's any reason why we couldn't have EXPLAIN do something
like this itself in core:
EXPLAIN (format table) SELECT * FROM bar;
It could output a table like the above. FWIW, The function I've written works
like this:
SELECT plan('SELECT * FROM bar');
Which is an okay workaround. Anyone else think that this might be useful?
Best,
David
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
