Re: Query Plan Columns - Mailing list pgsql-hackers

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