2.5 KiB
LiteLLM Spend & Cost Analysis Queries
When investigating high model costs or checking the effective price of models routed through admin-ai, you can query the LiteLLM_SpendLogs table directly on the AI server (178.156.167.181).
Note: Column names in this table use camelCase (e.g., "startTime"), which require double quotes in Postgres.
1. Effective Cost per 1 Million Tokens (Last 72 Hours)
This query groups by model and provider, calculates the actual effective cost per 1M tokens based on recorded spend, and filters out trivial usage (<10k tokens). This is the best way to compare real-world pricing for models like deepseek-v4-pro vs deepseek-chat to make cost-effective primary model recommendations.
ssh -i /root/.ssh/itpp-infra root@178.156.167.181 \
"docker exec litellm_postgres psql -U litellm -d litellm_db -At -F '|' -c \"
SELECT
COALESCE(model_group,'') as model,
COALESCE(custom_llm_provider,'') as provider,
COUNT(*) as calls,
ROUND(SUM(spend)::numeric,6) as total_spend_usd,
SUM(total_tokens) as tokens,
ROUND((SUM(spend)/NULLIF(SUM(total_tokens),0)*1000000)::numeric,6) AS effective_usd_per_1M_tokens
FROM \\\"LiteLLM_SpendLogs\\\"
WHERE \\\"startTime\\\" >= NOW() - INTERVAL '72 hours' AND model_group <> ''
GROUP BY model_group, custom_llm_provider
HAVING SUM(total_tokens) > 10000
ORDER BY effective_usd_per_1M_tokens ASC NULLS LAST;\""
2. Daily Spend by User & Session (Context Size Investigation)
High API costs are often driven by enormous prompt contexts sent repeatedly. This query finds the exact sessions and models burning the most spend today.
ssh -i /root/.ssh/itpp-infra root@178.156.167.181 \
"docker exec litellm_postgres psql -U litellm -d litellm_db -At -F '|' -c \"
SELECT
COALESCE(session_id,'') as session,
COALESCE(\\\"user\\\",'') as \\\"user\\\",
model_group,
COUNT(*) as calls,
ROUND(SUM(spend)::numeric,4) as spend,
SUM(prompt_tokens) as prompt_tokens,
SUM(completion_tokens) as completion_tokens
FROM \\\"LiteLLM_SpendLogs\\\"
WHERE \\\"startTime\\\" >= DATE_TRUNC('day', NOW())
GROUP BY session_id, \\\"user\\\", model_group
ORDER BY SUM(spend) DESC NULLS LAST LIMIT 20;\""
If you see a session averaging 250,000+ prompt_tokens per call, the agent's conversation history has grown too large for expensive models like gpt-5.5 or claude-sonnet-4-6. The immediate fix is to start a /new session to drop context, or swap the primary model to a cheaper alternative.