Re: [SQL] using explain output within pgsql - Mailing list pgsql-admin

From Gavin Flower
Subject Re: [SQL] using explain output within pgsql
Date
Msg-id 4E1A2C23.3090800@archidevsys.co.nz
Whole thread Raw
In response to Re: [SQL] using explain output within pgsql  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-admin
On 11/07/11 08:18, Pavel Stehule wrote:
2011/7/10 Uwe Bartels <uwe.bartels@gmail.com>:
Hi Pavel,

is it posible to get this running even with dynamic sql?
I didn't write that. I'm using execute to run this create table ....

probably yes

postgres=# do $$
declare x text;
begin
execute e'explain(format yaml)  select * from data where value = \'a\'' into x;
raise notice '%', x;
end;
$$ language plpgsql;
NOTICE:  - Plan:   Node Type: "Seq Scan"   Relation Name: "data"   Alias: "data"   Startup Cost: 0.00   Total Cost: 23.38   Plan Rows: 5   Plan Width: 46   Filter: "((value)::text = 'a'::text)"
DO
[...]

I find that I understand things better if I rephrase things, so I took Pavel's code and converted it to use variables so I could see more clearly what is happening.

I think using variables makes the use of 'execute' more understandable.

I hope this version is of value to to others, I have included all the code required to run it as a working example.

CREATE TABLE data
(
    id      int,
    value   text
);

INSERT INTO
    data (id, value)
VALUES
    (1, 'a'),
    (2, 'b');

do $$
declare
    v_sql_query    text;
    v_sql_explain  text;
    v_result       text;
begin
v_sql_query :=e'SELECT * FROM data d WHERE value = \'a\'';
v_sql_explain :=e'EXPLAIN(FORMAT YAML) ' || v_sql_query;
execute v_sql_explain into v_result;
raise notice 'v_result: %', v_result;
end;
$$ language plpgsql;



Cheers,
Gavin

pgsql-admin by date:

Previous
From: "Smith, Andy V "
Date:
Subject: Re: file permissions for /usr/bin/postgres
Next
From: David Hornsby
Date:
Subject: Re: R: Re: Import image into postgresql database