Slow trace and span search performance when searching by spans.attributes

[oginskis]

We self-host Phoenix on Kubernetes backed by GCP Cloud SQL Postgres and are ingesting ~100k LLM interactions per day.

For our use case, it's important to be able to filter traces and spans by specific metadata attributes. For example, we would like to quickly find interactions belonging to a specific app and/or team.

We noticed that queries similar to the following are particularly slow, especially when searching for the past 24 hours or more:

SELECT
  spans.id,
  spans.start_time AS "startTime_span_sort_column"
FROM
  spans
JOIN
  traces
ON
  traces.id = spans.trace_rowid
WHERE
  traces.project_rowid = $1::INTEGER
  AND spans.start_time >= $2::TIMESTAMP
WITH
  TIME ZONE
  AND CAST((spans.attributes #>> $3) AS VARCHAR) = $4::VARCHAR AND spans.parent_id IS NULL ORDER BY "startTime_span_sort_column" DESC NULLS LAST, spans.id DESC LIMIT $5::INTEGER

Quick AI-assisted research suggests adding a partial index for root span queries:

  CREATE INDEX CONCURRENTLY ix_spans_root_by_time                                                                                
  ON spans (start_time DESC, id DESC)                                                                                            
  WHERE parent_id IS NULL;

On the Phoenix span search interface, we are usually interested in the most recent n matching rows. Without this index, PostgreSQL must find all matching rows, then sort them. With this index, rows are already in the correct order — it can walk the index and stop after LIMIT rows are satisfied.

Additionally, one or more expression indexes matching specific query patterns could help.

For example, for quick metadata.user_api_key_auth_metadata.team == "team_name" matching, we could add:

  CREATE INDEX CONCURRENTLY ix_spans_attr_org                                                                                    
  ON spans (CAST((attributes #>> '{metadata,user_api_key_auth_metadata,team}') AS VARCHAR));       

Can anyone from the Phoenix team advise if we are on the right track?

[dosubot[bot]]

You're on the right track. Phoenix does not create default indexes on the spans.attributes JSONB column or on specific attribute paths, so adding custom indexes is often necessary for good performance at scale.

A partial index like ON spans (start_time DESC, id DESC) WHERE parent_id IS NULL matches Phoenix's query pattern for root span searches and should help speed up queries that filter and sort root spans by time and ID, especially when using LIMIT for pagination source.

For filtering by specific metadata attributes, expression indexes on casted JSONB paths (e.g., CAST((attributes #>> '{metadata,user_api_key_auth_metadata,team}') AS VARCHAR)) are effective for equality and range queries on known keys. Phoenix's filtering mechanism compiles user filters into SQL conditions that cast JSONB values to strings, so these indexes directly support common query patterns source. Recent Phoenix development added a dialect-aware expression index for session-scoped queries, confirming this approach is recommended for frequently filtered attributes source.

If you need to support containment queries (e.g., searching for spans with any matching key/value in attributes), a GIN index on the JSONB column is useful, but for equality filters on specific keys, expression indexes are preferred source.

Best practices: target high-cardinality or frequently queried attributes, monitor index size and write overhead, and periodically review query patterns to adjust indexes as your usage evolves. No Phoenix-specific caveats were found regarding custom indexes, but standard PostgreSQL considerations apply.

After adding indexes, keep an eye on query plans and performance—sometimes the query planner may need a REINDEX or ANALYZE to pick up new indexes.

To reply, just mention @dosu.

---

Share context across your team and agents. Try Dosu.

[RogerHYang]

Those two indices make sense to me. For the metadata index you would need to know the exact key you want to filter for and build the index with that.

[sueun-dev]

You are on the right track.

For your exact pattern (root spans, recent-first ordering, equality on a specific JSON path), I would do both:

CREATE INDEX CONCURRENTLY ix_spans_root_by_time
  ON spans (start_time DESC, id DESC)
  WHERE parent_id IS NULL;

CREATE INDEX CONCURRENTLY ix_spans_root_team_time
  ON spans ((attributes #>> '{metadata,user_api_key_auth_metadata,team}'), start_time DESC, id DESC)
  WHERE parent_id IS NULL;

If you need many dynamic attribute keys (not just a few hot keys), expression indexes do not scale well. In that case add a JSONB GIN index and use containment-style predicates for those queries.

Also run EXPLAIN (ANALYZE, BUFFERS) before/after for your real workload. At your ingest volume, it is worth verifying index-only scan behavior and whether the planner is still spending time on wide join filtering.