Re: Query Plan Columns

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