Performance & Scaling

WITH counts AS (
  SELECT 
    COUNT(*) FILTER (WHERE source = 'avatars') as avatar_count,
    COUNT(*) FILTER (WHERE source = 'videos') as video_count,
    COUNT(*) FILTER (WHERE source = 'voices') as voice_count,
    COUNT(*) FILTER (WHERE source = 'likes') as liked_count
  FROM (
    SELECT 'avatars' FROM user_generated_avatars WHERE user_id = $1 AND COALESCE(hidden_from_feed,false)=false
    UNION ALL
    SELECT 'videos' FROM user_video_files WHERE user_id = $1
    UNION ALL
    SELECT 'voices' FROM user_audio_files WHERE user_id = $1
    UNION ALL
    SELECT 'likes' FROM avatar_likes WHERE user_id = $1
  ) combined
)
SELECT * FROM counts;