How Can I Optimize a Query to Efficiently Find the Groupwise Maximum?

Optimized Query for Efficient Groupwise Maximum

Original Problem:

The query

select * 
from records 
where id in ( select max(id) from records group by option_id )

performs a sequential scan of the entire records table to determine the maximum ID for each option_id. This approach is inefficient, especially for large tables.

Solution: Lateral Join

One solution is to leverage a lateral join to fetch the maximum ID for each option_id within the subquery:

select r.*
from records r
cross join lateral (
    select max(id) as max_id
    from records
    where option_id = r.option_id
) m
where r.id = m.max_id

This query uses a lateral join to calculate the maximum ID in a separate subquery. The result is joined with the original records table to filter for only the rows with the maximum ID.

Creating a Specialized Index

Another optimization is to create a specialized index on the records table that stores the maximum ID for each option_id:

CREATE INDEX idx_max_id ON records (option_id, max(id))

This index enables a direct lookup of the maximum ID for a given option_id, eliminating the need for the original subquery:

select * 
from records r
where (option_id, id) in (
    select option_id, max(id) from records group by option_id
)

The index-based approach significantly reduces the number of table accesses, making the query more efficient for large tables.

The above is the detailed content of How Can I Optimize a Query to Efficiently Find the Groupwise Maximum?. For more information, please follow other related articles on the PHP Chinese website!