Thread: Graphical representation of query plans

Graphical representation of query plans

From
Viktor Rosenfeld
Date:
Hi everybody,

is there a (stand-alone, command line) tool that converts the output
of EXPLAIN ANALYZE into a tree-like representation of the plan?

Cheers,
Viktor

Re: Graphical representation of query plans

From
Grzegorz Jaśkiewicz
Date:

Re: Graphical representation of query plans

From
Viktor Rosenfeld
Date:
Thank you, this does indeed look very nice.

I would still be interested in alternatives, though.  Specifically, I
want to be able to quickly see the cost of query subplans á la http://explain-analyze.inf
o.  A tool that outputs a dot file or something which I can further
edit would also work.

Cheers,
Viktor

Am 22.06.2009 um 14:04 schrieb Grzegorz Jaśkiewicz:

> pgadmin does it pretty nicely:
> http://pgadmin.org/images/screenshots/pgadmin3_macosx.png
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


Re: Graphical representation of query plans

From
Dragan Sahpaski
Date:
Viktor Rosenfeld wrote:
> Thank you, this does indeed look very nice.
>
> I would still be interested in alternatives, though.  Specifically, I
> want to be able to quickly see the cost of query subplans á la
> http://explain-analyze.info.  A tool that outputs a dot file or
> something which I can further edit would also work.
>
> Cheers,
> Viktor
>
> Am 22.06.2009 um 14:04 schrieb Grzegorz Jaśkiewicz:
>
>> pgadmin does it pretty nicely:
>> http://pgadmin.org/images/screenshots/pgadmin3_macosx.png
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
http://explain.depesz.com/

Regards

Re: Graphical representation of query plans

From
Viktor Rosenfeld
Date:
Much better than explain-analyze.info

Many thanks!

Am 23.06.2009 um 11:25 schrieb Dragan Sahpaski:

> Viktor Rosenfeld wrote:
>> Thank you, this does indeed look very nice.
>>
>> I would still be interested in alternatives, though.  Specifically,
>> I want to be able to quickly see the cost of query subplans á la http://explain-analyze.inf
>> o.  A tool that outputs a dot file or something which I can further
>> edit would also work.
>>
>> Cheers,
>> Viktor
>>
>> Am 22.06.2009 um 14:04 schrieb Grzegorz Jaśkiewicz:
>>
>>> pgadmin does it pretty nicely:
>>> http://pgadmin.org/images/screenshots/pgadmin3_macosx.png
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>
> http://explain.depesz.com/
>
> Regards


Explaining functions.

From
"Hartman, Matthew"
Date:
Is there a recommended approach when trying to use EXPLAIN on a
function? Specifically, a function that is more than the typical SELECT
statement or tiny loop. The one in question that I'm hoping to optimize
is around 250 lines.

Thanks,

Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
(613) 549-6666 x4294


Re: Explaining functions.

From
Merlin Moncure
Date:
On Tue, Jun 23, 2009 at 8:03 AM, Hartman,
Matthew<Matthew.Hartman@krcc.on.ca> wrote:
> Is there a recommended approach when trying to use EXPLAIN on a
> function? Specifically, a function that is more than the typical SELECT
> statement or tiny loop. The one in question that I'm hoping to optimize
> is around 250 lines.

What I normally do for benchmarking of complex functions is to
sprinkle the source with "raise notice '%', timeofday();" to figure
out where the bottlenecks are.  Following that, I micro-optimize
problem queries or expressions outside of the function body in psql.

merlin

Re: Explaining functions.

From
"Hartman, Matthew"
Date:
Heeeey the raise notice is a good idea, thanks. I use raise notice
already for other uses, may as well go with it. Thanks.


Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
(613) 549-6666 x4294


-----Original Message-----
From: Merlin Moncure [mailto:mmoncure@gmail.com]
Sent: Tuesday, June 23, 2009 9:20 AM
To: Hartman, Matthew
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Explaining functions.

On Tue, Jun 23, 2009 at 8:03 AM, Hartman,
Matthew<Matthew.Hartman@krcc.on.ca> wrote:
> Is there a recommended approach when trying to use EXPLAIN on a
> function? Specifically, a function that is more than the typical
SELECT
> statement or tiny loop. The one in question that I'm hoping to
optimize
> is around 250 lines.

What I normally do for benchmarking of complex functions is to
sprinkle the source with "raise notice '%', timeofday();" to figure
out where the bottlenecks are.  Following that, I micro-optimize
problem queries or expressions outside of the function body in psql.

merlin


Re: Explaining functions.

From
Tom Lane
Date:
Merlin Moncure <mmoncure@gmail.com> writes:
> On Tue, Jun 23, 2009 at 8:03 AM, Hartman,
> Matthew<Matthew.Hartman@krcc.on.ca> wrote:
>> Is there a recommended approach when trying to use EXPLAIN on a
>> function? Specifically, a function that is more than the typical SELECT
>> statement or tiny loop. The one in question that I'm hoping to optimize
>> is around 250 lines.

> What I normally do for benchmarking of complex functions is to
> sprinkle the source with "raise notice '%', timeofday();" to figure
> out where the bottlenecks are.  Following that, I micro-optimize
> problem queries or expressions outside of the function body in psql.

There was some discussion once of using the same infrastructure the
plpgsql debugger uses to build a plpgsql profiler.  That would help
automate the first part of this, at least.  Anybody know the status
of that project?

            regards, tom lane

Re: Explaining functions.

From
Dave Page
Date:
On Tue, Jun 23, 2009 at 3:04 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> On Tue, Jun 23, 2009 at 8:03 AM, Hartman,
>> Matthew<Matthew.Hartman@krcc.on.ca> wrote:
>>> Is there a recommended approach when trying to use EXPLAIN on a
>>> function? Specifically, a function that is more than the typical SELECT
>>> statement or tiny loop. The one in question that I'm hoping to optimize
>>> is around 250 lines.
>
>> What I normally do for benchmarking of complex functions is to
>> sprinkle the source with "raise notice '%', timeofday();" to figure
>> out where the bottlenecks are.  Following that, I micro-optimize
>> problem queries or expressions outside of the function body in psql.
>
> There was some discussion once of using the same infrastructure the
> plpgsql debugger uses to build a plpgsql profiler.  That would help
> automate the first part of this, at least.  Anybody know the status
> of that project?

There is a profiler in the debugger source tree. Iirc, it dumps it's
data out to an XML in a not-so-friendly manner. I haven't ever tested
it, so it may have been broken since Korry handed it over.

http://pgfoundry.org/scm/?group_id=1000175

--
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Explaining functions.

From
"Chris Spotts"
Date:
> > is around 250 lines.
>
> What I normally do for benchmarking of complex functions is to
> sprinkle the source with "raise notice '%', timeofday();" to figure
> out where the bottlenecks are.  Following that, I micro-optimize
> problem queries or expressions outside of the function body in psql.
>
[Spotts, Christopher]
I use this set of functions towards this end, sprinkled about...
I'm sure there are better ways to write it,but it works.

CREATE OR REPLACE FUNCTION set_var(name text, val text) RETURNS text AS $$
    if ($_SHARED{$_[0]} = $_[1]) {
        return 'ok';
    } else {
        return "cannot set shared variable $_[0] to $_[1]";
    }
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION get_var(name text) RETURNS text AS $$
    return $_SHARED{$_[0]};
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION time_between_calls() RETURNS interval AS $$
    DECLARE
        ot text;
    BEGIN
    ot := get_var('calltime');
    PERFORM set_var('calltime',timeofday());
    RETURN timeofday():: timestamp - ot :: timestamp;
    END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION start_time_between_calls() RETURNS void AS $$
    BEGIN
    PERFORM set_var('calltime',timeofday());
    END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION test() RETURNS void AS $$
    BEGIN
    PERFORM start_time_between_calls();
    raise notice '%',time_between_calls();
    PERFORM pg_sleep(3);
    raise notice '%',time_between_calls();
    END
$$ LANGUAGE plpgsql;


Re: Explaining functions.

From
"Hartman, Matthew"
Date:
Thanks! That'll reduce the amount of copy/pasting I have to do to figure
out the differences in times.


Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
(613) 549-6666 x4294

-----Original Message-----
From: Chris Spotts [mailto:rfusca@gmail.com]
Sent: Tuesday, June 23, 2009 10:48 AM
To: Hartman, Matthew; 'Merlin Moncure'
Cc: pgsql-general@postgresql.org
Subject: RE: [GENERAL] Explaining functions.

> > is around 250 lines.
>
> What I normally do for benchmarking of complex functions is to
> sprinkle the source with "raise notice '%', timeofday();" to figure
> out where the bottlenecks are.  Following that, I micro-optimize
> problem queries or expressions outside of the function body in psql.
>
[Spotts, Christopher]
I use this set of functions towards this end, sprinkled about...
I'm sure there are better ways to write it,but it works.

CREATE OR REPLACE FUNCTION set_var(name text, val text) RETURNS text AS
$$
    if ($_SHARED{$_[0]} = $_[1]) {
        return 'ok';
    } else {
        return "cannot set shared variable $_[0] to $_[1]";
    }
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION get_var(name text) RETURNS text AS $$
    return $_SHARED{$_[0]};
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION time_between_calls() RETURNS interval AS $$
    DECLARE
        ot text;
    BEGIN
    ot := get_var('calltime');
    PERFORM set_var('calltime',timeofday());
    RETURN timeofday():: timestamp - ot :: timestamp;
    END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION start_time_between_calls() RETURNS void AS $$

    BEGIN
    PERFORM set_var('calltime',timeofday());
    END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION test() RETURNS void AS $$
    BEGIN
    PERFORM start_time_between_calls();
    raise notice '%',time_between_calls();
    PERFORM pg_sleep(3);
    raise notice '%',time_between_calls();
    END
$$ LANGUAGE plpgsql;



Re: Explaining functions.

From
Greg Smith
Date:
On Tue, 23 Jun 2009, Hartman, Matthew wrote:

> Is there a recommended approach when trying to use EXPLAIN on a
> function? Specifically, a function that is more than the typical SELECT
> statement or tiny loop. The one in question that I'm hoping to optimize
> is around 250 lines.

Take a look at
http://www.justatheory.com/computers/databases/postgresql/benchmarking_functions.html
which can help you run stuff multiple times even.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Re: Graphical representation of query plans

From
Emi Lu
Date:
Grzegorz Jaśkiewicz wrote:
> pgadmin does it pretty nicely:
> http://pgadmin.org/images/screenshots/pgadmin3_macosx.png


As shown in the mackintosh version, it is a very nice and helpful feature!


I have pgadmin 1.2.0 for PostgreSQL 8.0.15 on i686-pc-linux-gnu,
compiled by GCC gcc (GCC) 3.3.2.

I did not see this "explain" in my version. I only have "Data Output,
Message, and History".

May I know in which pgadmin version the "query plan visualization" was
added please?

Did not see it from pgadmin online doc either?


Thanks a lot!

--
Ying Lu





Re: Graphical representation of query plans

From
Raymond O'Donnell
Date:
On 22/09/2009 21:48, Emi Lu wrote:
> Grzegorz Jaśkiewicz wrote:
>> pgadmin does it pretty nicely:
>> http://pgadmin.org/images/screenshots/pgadmin3_macosx.png
>
>
> As shown in the mackintosh version, it is a very nice and helpful feature!
>
>
> I have pgadmin 1.2.0 for PostgreSQL 8.0.15 on i686-pc-linux-gnu,
> compiled by GCC gcc (GCC) 3.3.2.

That's a *really* old version - pgAdmin is up to 1.10 now, and it's well
worth your while upgrading. 8.0 is a really old version of PG too....

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Re: Graphical representation of query plans

From
Guillaume Lelarge
Date:
Le mardi 22 septembre 2009 à 22:48:57, Emi Lu a écrit :
> Grzegorz Jaśkiewicz wrote:
> > pgadmin does it pretty nicely:
> > http://pgadmin.org/images/screenshots/pgadmin3_macosx.png
>
> As shown in the mackintosh version, it is a very nice and helpful feature!
>
>
> I have pgadmin 1.2.0 for PostgreSQL 8.0.15 on i686-pc-linux-gnu,
> compiled by GCC gcc (GCC) 3.3.2.
>
> I did not see this "explain" in my version. I only have "Data Output,
> Message, and History".
>
> May I know in which pgadmin version the "query plan visualization" was
> added please?
>

At least, 1.4 has it.

> Did not see it from pgadmin online doc either?
>

http://www.pgadmin.org/docs/dev/query.html


--
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

Re: Graphical representation of query plans

From
Emi Lu
Date:
>>> pgadmin does it pretty nicely:
>>> http://pgadmin.org/images/screenshots/pgadmin3_macosx.png
>> As shown in the mackintosh version, it is a very nice and helpful feature!
>>
>>
>> I have pgadmin 1.2.0 for PostgreSQL 8.0.15 on i686-pc-linux-gnu,
>> compiled by GCC gcc (GCC) 3.3.2.
>>
>> I did not see this "explain" in my version. I only have "Data Output,
>> Message, and History".
>>
>> May I know in which pgadmin version the "query plan visualization" was
>> added please?
>>
> At least, 1.4 has it.


>> Did not see it from pgadmin online doc either?
>
> http://www.pgadmin.org/docs/dev/query.html


That's great! It is exactly what I am looking for!

Thanks a lot!

--
Lu Ying