Read-only
WITH past_year_rentals AS (
SELECT
r.rental_id,
r.rental_date,
r.return_date,
r.customer_id,
i.store_id,
i.film_id
FROM rental AS r
JOIN inventory AS i
ON i.inventory_id = r.inventory_id
WHERE r.rental_date >= CURRENT_TIMESTAMP - INTERVAL '1 year'
AND r.rental_date < CURRENT_TIMESTAMP
),
top_films AS (
SELECT
film_id,
COUNT(*) AS rental_count_past_year
FROM past_year_rentals
GROUP BY film_id
ORDER BY rental_count_past_year DESC, film_id
LIMIT 10
)
SELECT
pr.film_id,
f.title,
tf.rental_count_past_year,
pr.rental_id,
pr.rental_date,
pr.return_date,
pr.customer_id,
c.first_name,
c.last_name,
a.latitude,
a.longitude,
pr.store_id
FROM past_year_rentals AS pr
JOIN top_films AS tf
ON tf.film_id = pr.film_id
JOIN film AS f
ON f.film_id = pr.film_id
JOIN customer AS c
ON c.customer_id = pr.customer_id
LEFT JOIN address AS a
ON a.address_id = c.address_id
ORDER BY
tf.rental_count_past_year DESC,
pr.film_id,
pr.rental_date DESC,
pr.rental_id;
●Reviewing your schemadone156ms
●Preparing the analysisdone725ms
●Checking query behaviordone215ms
●Checking query behaviordone1.4s
●Evaluating optimization optionsdone479ms
●Evaluating optimization optionsdone440ms
●Checking query behaviordone1.0s
●Evaluating optimization optionsdone457ms
●Evaluating optimization optionsdone421ms
📊 Estimated impact
| Metric | Original | Optimized | Improvement |
|---|---|---|---|
| Execution time | 137.8 ms | 92.3 ms | 1.49× speedup; 33% less time |
Indicative results, not a production latency guarantee. Benefits depend on data distribution and existing indexes, especially how small a share of rentals belongs to the top 10 films.
🔎 What changed
- Count first, retrieve details second. The original materializes every past-year rental with its detail columns, then scans that intermediate result twice.
- Retrieve details only for the winning films. The rewrite uses indexed lookups by film, inventory, and rental date.
- Preserve the original semantics: identical date boundaries, rental counts, top-10 tie-breaking, optional addresses, and final ordering. Film and customer joins remain outside the ranking calculation.
🗂️ Recommended indexes
Skip any index already covered by an equivalent existing index. Run these statements outside a transaction block.
-- Supports the past-year counting step.
CREATE INDEX CONCURRENTLY rental_date_inventory_idx
ON rental (rental_date)
INCLUDE (inventory_id);
-- Supports rental-detail retrieval for selected inventory items.
CREATE INDEX CONCURRENTLY rental_inventory_date_idx
ON rental (inventory_id, rental_date)
INCLUDE (rental_id, return_date, customer_id);
-- Supports finding inventory for the top films.
CREATE INDEX CONCURRENTLY inventory_film_inventory_idx
ON inventory (film_id, inventory_id)
INCLUDE (store_id);These covering indexes add storage and write overhead.
✅ Optimized query
WITH top_films AS (
SELECT
i.film_id,
COUNT(*) AS rental_count_past_year
FROM rental AS r
JOIN inventory AS i
ON i.inventory_id = r.inventory_id
WHERE r.rental_date >= CURRENT_TIMESTAMP - INTERVAL '1 year'
AND r.rental_date < CURRENT_TIMESTAMP
GROUP BY i.film_id
ORDER BY rental_count_past_year DESC, i.film_id
LIMIT 10
)
SELECT
i.film_id,
f.title,
tf.rental_count_past_year,
r.rental_id,
r.rental_date,
r.return_date,
r.customer_id,
c.first_name,
c.last_name,
a.latitude,
a.longitude,
i.store_id
FROM top_films AS tf
JOIN inventory AS i
ON i.film_id = tf.film_id
JOIN rental AS r
ON r.inventory_id = i.inventory_id
AND r.rental_date >= CURRENT_TIMESTAMP - INTERVAL '1 year'
AND r.rental_date < CURRENT_TIMESTAMP
JOIN film AS f
ON f.film_id = i.film_id
JOIN customer AS c
ON c.customer_id = r.customer_id
LEFT JOIN address AS a
ON a.address_id = c.address_id
ORDER BY
tf.rental_count_past_year DESC,
i.film_id,
r.rental_date DESC,
r.rental_id;1m 14s
Shared threads are read-only. Start your own chat to keep working.
Start a new chat