Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

client.query() slowness #1103

Closed
giulianozorzi opened this issue Aug 3, 2016 · 8 comments
Closed

client.query() slowness #1103

giulianozorzi opened this issue Aug 3, 2016 · 8 comments

Comments

@giulianozorzi
Copy link

giulianozorzi commented Aug 3, 2016

Hi,
I'm quite new to postgresql, sorry if the question is a very basic one.
I'm using node 4.4.7, pg 6.0.3 (same happens with 5.x) with postgresql 9.5.3 on centos 7.
I'm using a default.poolsize of 50 and the following function to execute my queries

function execute(sql,params) {
    return new Promise(function(success,fail) {
        var config={...}
        pg.connect(config,function(err,client,done) {
            if (err) {
                fail('Connection error');
            } else {
                var queryConfig = {
                    text: sql,
                    values: params
                };
                var start= new Date().getTime();
                client.query(queryConfig, function(err, results) {
                    done(); /* libera la connessione del pool */
                    var stop= new Date().getTime();
                    console.log('execution time',stop-start);
                    if (err || results.rows == undefined) {
                        fail(err});
                    } else {
                        success(results.rows);
                    }
                });
            }
        });
    });
};

I actually run a quite long query that tooks about 550ms to run on the psql cli (\timing value), but when I run inside node it tooks about 22 seconds.
The amount of data retrieved is about 6MB and consists of a single row with a single field containing a json object. Running the same query from bash (time psql etc etc) returns about 670ms (I've tried connecting to tcp port and to socket, same results)

other "light" queries that return few kbytes of date runs in milliseconds.

Is there something I can investigate on ? or something I can try to improve the performances ?

thanks

g

@brianc
Copy link
Owner

brianc commented Aug 3, 2016

I appreciate you filing an issue here & would like to try and help; however, I receive multiple issues & request for help on a nearly daily basis and having properly formatted code is something that really makes triaging things easier - could you update your issue text by applying some proper formatting to your code? thanks!

@giulianozorzi
Copy link
Author

Thanks, I've updated the post. I didn't know how to insert code properly :)

@brianc
Copy link
Owner

brianc commented Aug 4, 2016

My guess would be parsing 6 megabytes of JSON is taking the node process a bit of time, and initially creating & resizing the buffer within node-postgres that needs to hold 6 megabytes is also take a bit of time. Do subsequent queries on the same client run faster? Also, try selecting the json out as text to bypass the JSON.parse step & see if that makes a difference?

For example:

select my_json_column::text from my_table

That will select the value out as text instead of json.

If you could create a single file that makes a temp table, inserts 6 megs into a json structure, and then queries it I could probably dive in more on my end.

@vitaly-t
Copy link
Contributor

vitaly-t commented Aug 4, 2016

Nobody should ever store 6MB worth of data as a JSON column, to begin with (IMO). Even for JSONB it is too big. It should probably be stored as a binary object, using type BYTEA, or JSONB as the last resort for sure, but it must be justifiable.

If you opt for JSONB, you should use PostgreSQL 9.5 or later.

@vitaly-t
Copy link
Contributor

vitaly-t commented Aug 4, 2016

Just tried to insert a 180MB file citylots.json from here.

The insert took 5 seconds for JSON and 9 seconds for JSONB, the select however never came back - the Node.js (6.3.1) process reaches 2GB in memory usage and then chokes.

I think even with 6MB json something similar is happening.

UPDATE

I was able to get it back as a JSONB object, took 188 seconds for select to return the data.

Use of the ::text cast does no improvement.

@giulianozorzi
Copy link
Author

giulianozorzi commented Aug 5, 2016

thanks to all for the replies.
Subsequent queries runs very fast as long the results are small in size.
Probably the best thing to do is rethink the whole thing to avoid returning massive jsons.

g

@vitaly-t
Copy link
Contributor

vitaly-t commented Aug 5, 2016

@brianc it still will make a worthwhile research to find out how node-postgres handles large JSON objects.

@charmander
Copy link
Collaborator

See brianc/node-packet-reader#3.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants