w

    WCKD

    1 day ago
    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.
    Fixed the connection part by specifying '127.0.0.1' instead of 'localhost' 😶
    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

    1 day ago
    hi, what query is it?
    w

    WCKD

    1 day ago
    SELECT * FROM table_name;
    Miguel Arregui

    Miguel Arregui

    1 day ago
    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)
    );
    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

    1 day ago
    Ok, at least I know the slowness is not only on my side, haha
    Miguel Arregui

    Miguel Arregui

    1 day ago
    hahaha, nope
    w

    WCKD

    1 day ago
    No, I'm just trying to use the cursor functionality while iterating over 40 million stock prices
    Miguel Arregui

    Miguel Arregui

    1 day ago
    maybe add a limit 20000 at the end
    w

    WCKD

    1 day ago
    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
    That's why I wanted to fetch it little by little with postgres cursor
    Miguel Arregui

    Miguel Arregui

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

    WCKD

    1 day ago
    Oh, I get it, so you're suggesting splitting the select into multiple selects with limit intervals
    Will try it out and report back, thank you so much
    Unfortunately splitting it into limits has the same result, still 15-16 seconds
    Jaromir Hamala

    Jaromir Hamala

    1 day ago
    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.