Optimizing PostgreSQL Query for Phoenix Homepage Performance
Hi friends, we are ingesting about 10k traces per day in our self-hosted Phoenix instance. We've noticed the homepage load of Phoenix is getting painfully slow with the default "Last 30 days" timeframe. In particular, the following query has an avg execution time of 30s, based on our PostgreSQL query insight:
SELECT
traces.project_rowid,
COALESCE(SUM(spans.llm_token_count_prompt), $1::INTEGER) AS prompt,
COALESCE(SUM(spans.llm_token_count_completion), $2::INTEGER) AS completion,
COALESCE(SUM(spans.llm_token_count_prompt), $1::INTEGER) + COALESCE(SUM(spans.llm_token_count_completion), $2::INTEGER) AS total
FROM
traces
JOIN
spans
ON
traces.id = spans.trace_rowid
WHERE
spans.start_time >= $3::TIMESTAMP
WITH
TIME ZONE
AND traces.project_rowid IN ($4::INTEGER,
$5::INTEGER,
$6::INTEGER)
GROUP BY
traces.project_rowidThis looks to be the stats query that populates the counters for each project panel on the homepage. Is there a way to optimize the query index for it to be efficient?
