DB Query profiling in Node.js

Question

I am building a json API using express (well, maybe will move to koa). I store my data in PostgreSQL database, and i use pg-promise to fetch data from it (async/await via babel).

I am totally new to node.js and i can't find any information about performance measurement in that environment.

To be specific:

module.exports.get_hierarchy = async function () {
    const rows = await postgres.any('SELECT id, parent, title, permission FROM heading');

    var result = [];
    // some black magic goes here...

    return result;
}

I want to know (programmatically if possible) how much time SELECT consumes. (Not the time promise lives from constructing to resolving, which can be achieved by taking two timestamps, but actual time consumed by the DB server to process query).

Can this be achieved? If so, how?

What you are looking for in PostgreSQL is called EXPLAIN that you need to execute directly, best is via pgAdmin UI ;) — vitaly-t
– vitaly-t, Commented Nov 23, 2016 at 9:28

vitaly-t · Accepted Answer · 2017-06-25 16:20:09Z

3

As you mentioned it yourself, there are two ways to profile the execution time in your case:

the time from you calling a method of pg-promise to getting the result back
the time it takes to execute the query by the server

The first one is the easiest to do by using method result, which resolves with Result object that has property duration.

And the second one requires direct execution of EXPLAIN, typically via the pgAdmin tool that comes with every PostgreSQL installation, or you can use psql terminal.

edited Jun 25, 2017 at 16:20

answered Nov 23, 2016 at 9:35

vitaly-t

26.1k18 gold badges129 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Diligent Key Presser Over a year ago

Thank you for pointing to result method. How could i miss it? The downside is incompatibility with methods like one and oneOrNone, but it's bearable. Also duration property seems to be undocumented (github.com/brianc/node-postgres/wiki/Query#result-object); i hope it isn't going to dissapear in future releases:)

Diligent Key Presser Over a year ago

Hey, and thank you for bringing such a great library to mankind:)

vitaly-t Over a year ago

Property duration is documented in the API: resolves with the original Result object, extended with property duration - query duration in milliseconds. ;) And no, I'm not removing it in the future releases ;)

Collectives™ on Stack Overflow

DB Query profiling in Node.js

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related