Thread: planinstr, showing planner time on EXPLAIN
Yesterday on PGXN I just released the first version of planinstr, a plugin module to append planner time to EXPLAIN. I post this here since it is mostly for developers. http://www.pgxn.org/dist/planinstr/ db1=# load '$libdir/planinstr'; LOAD db1=# explain select * from pg_class; QUERY PLAN ---------------------------------------------------------------Seq Scan on pg_class (cost=0.00..141.87 rows=3287 width=194)Plantime: 0.119 ms db1=# explain analyze select count(*) from size_m; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------Aggregate (cost=26272.00..26272.01 rows=1 width=0) (actual time=51.938..51.938 rows=1 loops=1) -> Append (cost=0.00..23147.00 rows=1250000 width=0) (actual time=0.037..45.809 rows=20000 loops=1) -> Seq Scan on size_m (cost=0.00..847.00 rows=20000 width=0) (actual time=0.037..41.863 rows=20000 loops=1) <.. snip ..> -> Seq Scan on myt1000 size_m (cost=0.00..22.30 rows=1230 width=0) (actual time=0.001..0.001 rows=0 loops=1)Total runtime: 75.217 msPlan time: 61.353 ms (1005 rows) This may help to make the planner performance regression visible on some internal logic refactoring, etc. Hope this helps someone. Feel free to tell me if similar mechanism already exists, or you want more rich interfaces. Github is here: https://github.com/umitanuki/planinstr Also free to fork and send pull request! Regards, -- Hitoshi Harada
On Tue, Jun 14, 2011 at 11:18 PM, Hitoshi Harada <umi.tanuki@gmail.com> wrote: > Yesterday on PGXN I just released the first version of planinstr, a > plugin module to append planner time to EXPLAIN. I post this here > since it is mostly for developers. > > http://www.pgxn.org/dist/planinstr/ > > db1=# load '$libdir/planinstr'; > LOAD > db1=# explain select * from pg_class; > QUERY PLAN > --------------------------------------------------------------- > Seq Scan on pg_class (cost=0.00..141.87 rows=3287 width=194) > Plan time: 0.119 ms > > db1=# explain analyze select count(*) from size_m; > QUERY PLAN > > ------------------------------------------------------------------------------------------------------------------------ > Aggregate (cost=26272.00..26272.01 rows=1 width=0) (actual > time=51.938..51.938 rows=1 loops=1) > -> Append (cost=0.00..23147.00 rows=1250000 width=0) (actual > time=0.037..45.809 rows=20000 loops=1) > -> Seq Scan on size_m (cost=0.00..847.00 rows=20000 > width=0) (actual time=0.037..41.863 rows=20000 loops=1) > <.. snip ..> > -> Seq Scan on myt1000 size_m (cost=0.00..22.30 rows=1230 > width=0) (actual time=0.001..0.001 rows=0 loops=1) > Total runtime: 75.217 ms > Plan time: 61.353 ms > (1005 rows) Wow, that is awesome. I sorta wish we had something like that in core -- and I don't even think it should be optional, I think it should just always give you that additional piece of information. I can't immediately count the number of times this would have helped me, but it's significant. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
2011/6/17 Robert Haas <robertmhaas@gmail.com>: > On Tue, Jun 14, 2011 at 11:18 PM, Hitoshi Harada <umi.tanuki@gmail.com> wrote: >> Yesterday on PGXN I just released the first version of planinstr, a >> plugin module to append planner time to EXPLAIN. I post this here >> since it is mostly for developers. > > Wow, that is awesome. I sorta wish we had something like that in core > -- and I don't even think it should be optional, I think it should > just always give you that additional piece of information. Thanks for a positive feedback. Current design passes instrument time as global variable from planner to executor. To get it in core we need to add a field to the planner for that, which I don't think is too difficult. Anyway I still like its way as it doesn't need any change in core; free for anyone to use, even in the older versions. Regards, -- Hitoshi Harada
On Thu, Jun 16, 2011 at 9:10 PM, Hitoshi Harada <umi.tanuki@gmail.com> wrote: > 2011/6/17 Robert Haas <robertmhaas@gmail.com>: >> On Tue, Jun 14, 2011 at 11:18 PM, Hitoshi Harada <umi.tanuki@gmail.com> wrote: >>> Yesterday on PGXN I just released the first version of planinstr, a >>> plugin module to append planner time to EXPLAIN. I post this here >>> since it is mostly for developers. >> >> Wow, that is awesome. I sorta wish we had something like that in core >> -- and I don't even think it should be optional, I think it should >> just always give you that additional piece of information. > > Thanks for a positive feedback. Current design passes instrument time > as global variable from planner to executor. To get it in core we need > to add a field to the planner for that, which I don't think is too > difficult. Anyway I still like its way as it doesn't need any change > in core; free for anyone to use, even in the older versions. Yeah, definitely a nice tool. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company