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


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

Search Discussions

  • Robert Haas at Jun 16, 2011 at 6:00 pm

    On Tue, Jun 14, 2011 at 11:18 PM, Hitoshi Harada 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
  • Hitoshi Harada at Jun 17, 2011 at 1:10 am

    2011/6/17 Robert Haas <robertmhaas@gmail.com>:
    On Tue, Jun 14, 2011 at 11:18 PM, Hitoshi Harada 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
  • Robert Haas at Jun 17, 2011 at 1:59 am

    On Thu, Jun 16, 2011 at 9:10 PM, Hitoshi Harada wrote:
    2011/6/17 Robert Haas <robertmhaas@gmail.com>:
    On Tue, Jun 14, 2011 at 11:18 PM, Hitoshi Harada 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJun 15, '11 at 3:18a
activeJun 17, '11 at 1:59a
posts4
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Robert Haas: 2 posts Hitoshi Harada: 2 posts

People

Translate

site design / logo © 2021 Grokbase