Thread: Graphical representation of query plans
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
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: > 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
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: > 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
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