pgcli compare to psql?

[jerome-peng]

i use pgcli and psql to access db.

i found pgcli is slow when i post a query, psql is faster.

"\timing off/on" is not support in pgcli, it works better on psql

"\h create table" is not support in pgcli, it works better on psql

"\h XXX XXX " is not support in pgcli?

[amjith]

Not all of the back-slash commands are supported yet. To get a list of supported back-slash commands try ?. That will give you the list of supported back-slash commands.

\timing is interesting, I didn't know about it. But it should be easy to add. So thanks for requesting that.

\h is a much harder command to implement, since it involves copying tons of documentation text into the source code. So that might take a bit longer to implement.

Regarding the speed of execution of a query, they should both be executing the query at the same speed, but the difference might be from building the table of results. It's faster to build a table of data in C compared to Python. So for large tables, it might be noticeably slower.

Can you tell me how large is the result dataset? How many rows and columns?

[jerome-peng]

my query table is 27MB, columns 7 rows 271299 use psql expense 38.530

but pgcli more than 5 seconds

[amjith]

Sorry that's a bit unclear.

Will the results have 271299 rows?

What query did you run? Can you paste the query? (please remove any sensitive information from the query).

Finally you mention psql expense was 38.530, is that in seconds?

I should point out that running the actual query itself will take the same time in both psql and pgcli, but pgcli might take a little bit longer to render the results. I don't know if I made that clear in the previous comment.

[jerome-peng]

select * from table;

sorry ,psql expense 38.53ms

i feel slow in pgcli

[amjith]

Yeah I can believe that.

On a table with 271,299 rows displaying every single row will require reading all the rows into memory and then trying to determine the table cell width. I think that's the problem.

I don't have an easy answer for this one right now. Unless I end up rewriting the table rendering as a C-extension, this will continue being slow.

In an interactive scenario, requesting hundreds of thousands of rows is not a common use case. So I'm tempted to say that this is a low priority item.

[jerome-peng]

With psql, execute the sql "select * from table", no matter the query returns how many rows, it returns the proper rows according to you terminal height, then you can press "enter" key to view the next.
i think this is good .

that;'s the reason why psql is faster than pgcli when query .

[jerome-peng]

just like use page

[jerome-peng]

my english is not very good , i wish you cound unserstand my meaning . :)

[amjith]

Don't worry about english. My native tongue isn't english either. I think I understand what you're saying, correct me if I'm wrong.

Instead of loading all the rows into memory and try to render the table all at once, I should try and load maybe 50 rows at a time and only load more when the next page is requested.

That's an interesting idea. I hadn't thought of that. I'll give it a shot and see if that works.

Thank you for the suggestion.

[amjith]

Version 0.14.0 released to PyPI.

This adds support for \timing and requires you to confirm before rendering a table with more than 1000 rows.

pip install -U pgcli

[jerome-peng]

gets, thanks a lot.

pcsuccess@sina.com

From: Amjith Ramanujam
Date: 2015-02-01 07:56
To: amjith/pgcli
CC: jerome-peng
Subject: Re: [pgcli] pgcli compare to psql? (#110)
Version 0.14.0 released to PyPI.
This adds support for \timing and requires you to confirm before rendering a table with more than 1000 rows.
pip install -U pgcli

—
Reply to this email directly or view it on GitHub.