Title
#users-public
w

WCKD

09/23/2022, 5:17 PM
Hello, everyone! Just wanted to ask a quick question. Is it normal for the postgresql wire protocol to be amazingly slow? With the psycopg2 package, even the connection takes 3 seconds, not to mention the query taking more than 40 seconds, whereas in the web console it takes 1ms.
5:30 PM
Fixed the connection part by specifying '127.0.0.1' instead of 'localhost' 😶
5:36 PM
So, what I'm trying to achieve is query 39 000 000 rows from the database, which can be achieved in less than 2ms within the web console, and with
cursor.execute(query)
it takes 15 seconds.
Miguel Arregui

Miguel Arregui

09/23/2022, 5:38 PM
hi, what query is it?
w

WCKD

09/23/2022, 5:39 PM
SELECT * FROM table_name;
Miguel Arregui

Miguel Arregui

09/23/2022, 5:53 PM
I am observing the same behaviour, I have created this table:
CREATE TABLE x AS(
  SELECT
    rnd_int() a,
    rnd_double() b,
    rnd_symbol('ABB', 'CDD') c
  FROM
    long_sequence(40000000)
);
5:54 PM
and I have a java client.... pgwire is not built for humongous transfers. What is the use case?, why this query?, are you trying to migrate the data?
w

WCKD

09/23/2022, 5:54 PM
Ok, at least I know the slowness is not only on my side, haha
Miguel Arregui

Miguel Arregui

09/23/2022, 5:55 PM
hahaha, nope
w

WCKD

09/23/2022, 5:55 PM
No, I'm just trying to use the cursor functionality while iterating over 40 million stock prices
Miguel Arregui

Miguel Arregui

09/23/2022, 5:55 PM
maybe add a limit 20000 at the end
w

WCKD

09/23/2022, 5:56 PM
The REST functionality provided by questdb was returning the entire json and that would've filled the ram in a second haha if I were to load it in a variable inside python
5:57 PM
That's why I wanted to fetch it little by little with postgres cursor
Miguel Arregui

Miguel Arregui

09/23/2022, 5:58 PM
you can use LIMIT at the end of the select with "frames" https://questdb.io/docs/reference/sql/select/#limit
6:00 PM
SELECT * FROM x LIMIT 0, 100;
SELECT * FROM x LIMIT 100, 200;
...
6:00 PM
with a more decent lower/upper bound, say incrementing it by 1000000
w

WCKD

09/23/2022, 6:01 PM
Oh, I get it, so you're suggesting splitting the select into multiple selects with limit intervals
6:01 PM
Will try it out and report back, thank you so much
6:25 PM
Unfortunately splitting it into limits has the same result, still 15-16 seconds
Jaromir Hamala

Jaromir Hamala

09/23/2022, 9:25 PM
the webconsole is seemingly fast, because it uses implicit limit set 1000. as there is no practical reason to display more rows in a web console.