[Bug]: Public brains endpoint takes 12 seconds

[StanGirard]

What happened?

CREATE OR REPLACE FUNCTION get_public_brain()
RETURNS TABLE
(
id INT,
name VARCHAR(255),
description TEXT,
last_update TIMESTAMP,
brain_type VARCHAR(255),
brain_count INT
)
AS $$
BEGIN
RETURN QUERY
SELECT
b.brain_id as id,
b.name,
b.description,
b.last_update,
b.brain_type,
a.,
(SELECT COUNT() FROM brains_users bu WHERE bu.brain_id = b.brain_id) as brain_count
FROM
brains b
LEFT JOIN api_brain_definition a ON b.brain_id = a.brain_id
WHERE
b.status = 'public'
ORDER BY
brain_count DESC;
END; $$
LANGUAGE plpgsql;

Relevant log output

https://quivr-brain.sentry.io/performance/python-fastapi:432c0d930d454146aad90bb0ca85de25/?project=4505455607808000&query=http.method:GET&statsPeriod=24h&transaction=/brains/public&unselectedSeries=p100()&unselectedSeries=avg()

Twitter / LinkedIn details

No response

[dosubot]

🤖

Hello @StanGirard!

Good to see you here again. I hope you're doing well.

Thank you for bringing this to our attention. It seems like the get_public_brain() function is taking longer than expected to execute. This could be due to a number of reasons, such as the size of the data being queried, the complexity of the query, or the load on the database server.

Looking at the function, it appears that the query is performing a count operation on the brains_users table for each row in the brains table. This could potentially be a performance bottleneck if the brains_users table is large, as the count operation would need to scan the entire table for each row in the brains table.

One potential optimization could be to calculate the brain_count in a separate query and join it with the main query. This would avoid the need to perform a count operation for each row in the brains table.

Here's an example of how you could modify the function:

CREATE OR REPLACE FUNCTION get_public_brain()
RETURNS TABLE
(
    id INT,
    name VARCHAR(255),
    description TEXT,
    last_update TIMESTAMP,
    brain_type VARCHAR(255),
    brain_count INT
)
AS $$
BEGIN
    RETURN QUERY 
    WITH brain_counts AS (
        SELECT
            bu.brain_id,
            COUNT(*) as brain_count
        FROM
            brains_users bu
        GROUP BY
            bu.brain_id
    )
    SELECT
        b.brain_id as id,
        b.name,
        b.description,
        b.last_update,
        b.brain_type,
        a.*,
        bc.brain_count
    FROM
        brains b
    LEFT JOIN api_brain_definition a ON b.brain_id = a.brain_id
    LEFT JOIN brain_counts bc ON b.brain_id = bc.brain_id
    WHERE
        b.status = 'public'
    ORDER BY
        bc.brain_count DESC;
END; $$
LANGUAGE plpgsql;

This modification calculates the brain_count in the brain_counts CTE (Common Table Expression) and then joins it with the main query. This should reduce the number of count operations and potentially improve the performance of the function.

Please note that this is just a suggestion and the actual performance improvement may vary depending on the specific characteristics of your data and database server. It would be beneficial to test this modification in a development environment before deploying it to production.

I hope this helps! If you have any further questions or need additional assistance, please don't hesitate to ask.

Sources

• scripts/20231106071000_add_api_brain_definition_table.sql