PostgreSQL中获取每个唯一EAN对应最新dtmod记录的查询优化方案咨询

Problem Statement #

I have a PostgreSQL table my_table with columns ean, price, dtmod, containing the following sample data:

My goal is to fetch the latest dtmod record (including the corresponding price) for each unique ean, sorted by ean. The expected output is:

I currently use a two-step approach that works but feels inefficient:

Current Solution #

1. Extract the latest dtmod for each ean:

SELECT ean, max(dtmod) FROM my_table GROUP BY ean;

2. Join back to the original table to get the matching price and sort the results:

SELECT ean, price, dtmod 
FROM my_table 
WHERE (ean, dtmod) IN (
    SELECT ean, max(dtmod) FROM my_table GROUP BY ean
) 
ORDER BY ean;

I'm looking for more concise and efficient ways to achieve this. What optimization suggestions are there?

Optimization Suggestions #

PostgreSQL has some fantastic built-in features that make this kind of "latest record per group" query way cleaner and faster than your current two-step approach. Let's break down the top options:

1. DISTINCT ON (PostgreSQL's Secret Weapon) #

This is hands down my go-to for this scenario—it's concise, efficient, and tailored specifically to PostgreSQL. DISTINCT ON lets you grab the first row from each group (defined by the column in parentheses) after sorting the rows within each group exactly how you need.

SELECT DISTINCT ON (ean)
       ean, price, dtmod
FROM my_table
ORDER BY ean, dtmod DESC;

Here's how it works:

• First, the ORDER BY clause sorts all rows by ean, then within each ean group, sorts from newest (dtmod DESC) to oldest.
• DISTINCT ON (ean) picks the very first row from each sorted ean group—which is exactly the latest record we want.
• This runs in a single pass over the table (with the right index) and avoids the subquery/join overhead of your original method.

2. Window Functions (ROW_NUMBER()) #

If you want a more standard SQL approach that works across most modern databases, window functions are a solid choice. We'll assign a row number to each row in an ean group, ordered by dtmod descending, then filter to keep only the first row in each group.

SELECT ean, price, dtmod
FROM (
    SELECT ean, price, dtmod,
           ROW_NUMBER() OVER (PARTITION BY ean ORDER BY dtmod DESC) AS rn
    FROM my_table
) AS subquery
WHERE rn = 1
ORDER BY ean;

How this works:

• PARTITION BY ean splits the table into separate groups for each unique ean.
• ORDER BY dtmod DESC ensures the most recent record in each group gets a row number of 1.
• The outer query filters out all rows except those with rn = 1, leaving us with the latest record per ean.

Boost Performance with Indexing #

To make either of these queries fly, add a composite index on (ean, dtmod DESC):

CREATE INDEX idx_my_table_ean_dtmod ON my_table (ean, dtmod DESC);

This index lets PostgreSQL quickly locate and sort the latest row for each ean without scanning the entire table—critical if your table is large.

How These Compare to Your Original Query #

Your current approach uses a subquery with IN, which can be less efficient because PostgreSQL may need to run the subquery first, then perform a lookup for each matching row. The DISTINCT ON and window function methods are generally faster, especially on big datasets, since they minimize the number of table scans needed.

内容的提问来源于stack exchange，提问作者Tms91