https://supabase.com/ logo
Join Discord
Powered by
This query takes a long time to run (~15 seconds):...
# sql
b
This query takes a long time to run (~15 seconds):
Copy code
SELECT u.id FROM "User" u
JOIN "Typebot" t ON u.id = t."ownerId"
WHERE (SELECT count(*) FROM "Result" r WHERE r."typebotId" = t.id) >= 30
Result table has 66,000 records. I'm wondering, is there a way to optimize this?
t
Its easy to optimise the positive case when there is more then 30 results.
Copy code
sql
SELECT u.id FROM "User" u
JOIN "Typebot" t ON u.id = t."ownerId"
WHERE (SELECT count(*) FROM "Result" r WHERE r."typebotId" = t.id LIMIT 30) >= 30
your filter with a count is going to be slow as its also a join and will be executed every row.
Perhaps a cte will fix it
I think this will work and should be faster as its calc the count in 1 sweep.
Copy code
sql
WITH result_count AS (
SELECT r."typebotId", count(*) FROM "Result" r GROUP BY r."typebotId"
)
SELECT u.id FROM "User" u
JOIN "Typebot" t ON u.id = t."ownerId"
JOIN result_count ON r."typebotId" = t.id
b
Hey @tourdownunder thank you so much for the help!!
That's greatly appreciated and it works perfectly 🥰
t
Awesome I’m glad I could help.
Did you choose the cte ? As I forgot to add the > 30 constraint.
Probably best to use the having with the group by in the first part of the cte
Copy code
sql
WITH result_count AS (
SELECT r."typebotId", count(*) FROM "Result" r GROUP BY r."typebotId" HAVING count(*) > 30
)
b
Yes, this is what I use:
Copy code
sql
CREATE VIEW active_users AS
WITH result_count AS (
SELECT r."typebotId", count(*) FROM "Result" r GROUP BY r."typebotId" HAVING count(*) > 30
)
SELECT u.id FROM "User" u
JOIN "Typebot" t ON u.id = t."ownerId"
JOIN result_count r ON r."typebotId" = t.id
That's perfect
PreviousNext
Powered by