E-commerce product listings with subqueries for ratings, reviews, and inventory - optimize from 15 seconds to under 1 second. Analytics dashboards with correlated subqueries - reduce query time by 100-1000x.

Airbnb - Listing Search with Subqueries

Airbnb listing search page showed property details with average rating, review count, and availability. Original query used correlated subqueries for each metric. With 5M listings, search results took 25 seconds to load, causing timeouts and poor user experience.

Converted all correlated subqueries to JOINs with pre-aggregated CTEs. Created materialized view for review statistics (updated hourly). Added composite indexes on listing_id for all related tables. Used window functions for ranking within search results. Implemented query result caching for popular searches.

-- Before: 25 seconds

SELECT

l.listing_id,

l.title,

(SELECT AVG(rating) FROM reviews WHERE listing_id = l.id) as avg_rating,

(SELECT COUNT(*) FROM reviews WHERE listing_id = l.id) as review_count,

(SELECT COUNT(*) FROM bookings WHERE listing_id = l.id AND status = 'available') as available_days

FROM listings l

WHERE l.city = 'San Francisco';

-- After: 0.8 seconds (31x faster)

WITH review_stats AS (

SELECT listing_id, AVG(rating) as avg_rating, COUNT(*) as review_count

FROM reviews

GROUP BY listing_id

),

availability AS (

SELECT listing_id, COUNT(*) as available_days

FROM bookings

WHERE status = 'available'

GROUP BY listing_id

)

SELECT

l.listing_id,

l.title,

COALESCE(rs.avg_rating, 0) as avg_rating,

COALESCE(rs.review_count, 0) as review_count,

COALESCE(a.available_days, 0) as available_days

FROM listings l

LEFT JOIN review_stats rs ON l.id = rs.listing_id

LEFT JOIN availability a ON l.id = a.listing_id

WHERE l.city = 'San Francisco';

Query time: 25s to 0.8s (31x faster)

Search page load: 30s to 2s

Timeout rate: 15% to 0.1%

User satisfaction: 45% increase

Conversion rate: 22% increase