Thread: excution time for plpgsql function and subfunction
Hey List,
kind of a simple question :
I'm using the postgis_topology extension,
and I'm trying to figure where the slowness comes from when importing data.
It involves plpgsql function calling other plpgsql functions, insert, update, etc etc.
I know I can use explain analyze for one querry, but I don't know how to get details of how much time takes each sub-function called by a main function.
Thus it is very difficult to guess where is the bottleneck.
Thanks ,cheers,
Rémi-C
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rémi Cura Sent: Friday, January 10, 2014 4:10 AM To: PostgreSQL General Subject: [GENERAL] excution time for plpgsql function and subfunction Hey List, kind of a simple question : I'm using the postgis_topology extension, and I'm trying to figure where the slowness comes from when importing data. It involves plpgsql function calling other plpgsql functions, insert, update, etc etc. I know I can use explain analyze for one querry, but I don't know how to get details of how much time takes each sub-functioncalled by a main function. Thus it is very difficult to guess where is the bottleneck. Thanks ,cheers, Rémi-C You could use auto_explain module to get info you are looking for. It'll be logged in pg_log. Read about it in the docs: http://www.postgresql.org/docs/9.2/static/auto-explain.html Regards, Igor Neyman
On Fri, 2014-01-10 at 10:10 +0100, Rémi Cura wrote: > Hey List, > kind of a simple question : > > I'm using the postgis_topology extension, > and I'm trying to figure where the slowness comes from when importing data. > > It involves plpgsql function calling other plpgsql functions, insert, > update, etc etc. > > I know I can use explain analyze for one querry, but I don't know how to > get details of how much time takes each sub-function called by a main > function. > > Thus it is very difficult to guess where is the bottleneck. > Shameless plug, but here is how I do it: http://blog.guillaume.lelarge.info/index.php/post/2012/03/31/Profiling-PL/pgsql-functions -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
Thanks all =)
Cheers,
Rémi-C
2014/1/13 Guillaume Lelarge <guillaume@lelarge.info>
Shameless plug, but here is how I do it:On Fri, 2014-01-10 at 10:10 +0100, Rémi Cura wrote:
> Hey List,
> kind of a simple question :
>
> I'm using the postgis_topology extension,
> and I'm trying to figure where the slowness comes from when importing data.
>
> It involves plpgsql function calling other plpgsql functions, insert,
> update, etc etc.
>
> I know I can use explain analyze for one querry, but I don't know how to
> get details of how much time takes each sub-function called by a main
> function.
>
> Thus it is very difficult to guess where is the bottleneck.
>
http://blog.guillaume.lelarge.info/index.php/post/2012/03/31/Profiling-PL/pgsql-functions
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com