Skip site navigation (1) Skip section navigation (2)

Re: [SQL] using explain output within pgsql

From: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: Uwe Bartels <uwe(dot)bartels(at)gmail(dot)com>, pgsql-admin(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] using explain output within pgsql
Date: 2011-07-10 22:48:03
Message-ID: 4E1A2C23.3090800@archidevsys.co.nz (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-sql
On 11/07/11 08:18, Pavel Stehule wrote:
> 2011/7/10 Uwe Bartels<uwe(dot)bartels(at)gmail(dot)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

In response to

pgsql-admin by date

Next:From: Lukasz BrodziakDate: 2011-07-11 07:00:08
Subject: Re: row is too big during cluster
Previous:From: Pavel StehuleDate: 2011-07-10 20:18:19
Subject: Re: [SQL] using explain output within pgsql

pgsql-sql by date

Next:From: Viktor BojovićDate: 2011-07-11 14:42:32
Subject: Re: overload
Previous:From: Scott MarloweDate: 2011-07-10 21:11:10
Subject: Re: what is similar to like operator in mysql for postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group