Graphical representation of query plans

pgadmin does it pretty nicely:
http://pgadmin.org/images/screenshots/pgadmin3_macosx.png

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:

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

Much better than explain-analyze.info

Many thanks!

Am 23.06.2009 um 11:25 schrieb Dragan Sahpaski:

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

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

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

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

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

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;

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;

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

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

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
------------------------------------------------------------------

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

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