Optimize groupwise maximum query

by

Assuming relatively few rows in options for many rows in records.

Typically, you would have a look-up table options that is referenced from records.option_id, ideally with a foreign key constraint. If you don’t, I suggest to create one to enforce referential integrity:

CREATE TABLE options (
  option_id int  PRIMARY KEY
, option    text UNIQUE NOT NULL
);

INSERT INTO options
SELECT DISTINCT option_id, 'option' || option_id -- dummy option names
FROM   records;

Then there is no need to emulate a loose index scan any more and this becomes very simple and fast. Correlated subqueries can use a plain index on (option_id, id).

SELECT option_id, (SELECT max(id)
                   FROM   records
                   WHERE  option_id = o.option_id) AS max_id
FROM   options o
ORDER  BY 1;

This includes options with no match in table records. You get NULL for max_id and you can easily remove such rows in an outer SELECT if needed.

Or (same result):

SELECT option_id, (SELECT id
                   FROM   records
                   WHERE  option_id = o.option_id
                   ORDER  BY id DESC NULLS LAST
                   LIMIT  1) AS max_id
FROM   options o
ORDER  BY 1;

May be slightly faster. The subquery uses the sort order DESC NULLS LAST – same as the aggregate function max() which ignores NULL values. Sorting just DESC would have NULL first:

The perfect index for this:

CREATE INDEX on records (option_id, id DESC NULLS LAST);

Index sort order doesn’t matter much while columns are defined NOT NULL.

There can still be a sequential scan on the small table options, that’s just the fastest way to fetch all rows. The ORDER BY may bring in an index (only) scan to fetch pre-sorted rows.
The big table records is only accessed via (bitmap) index scan or, if possible, index-only scan.

db<>fiddle here – showing two index-only scans for the simple case
Old sqlfiddle

Or use LATERAL joins for a similar effect in Postgres 9.3+:

More Related Contents:

  1. Select first row in each GROUP BY group?
  2. Optimize GROUP BY query to retrieve latest row per user
  3. Fetch the row which has the Max value for a column
  4. How to select id with max date group by category in PostgreSQL?
  5. SQL query to get most recent row for each instance of a given key
  6. PostgreSQL – fetch the rows which have the Max value for a column in each GROUP BY group
  7. Get values from first and last row per group
  8. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  9. Get most common value for each value of another column in SQL
  10. PostgreSQL: top n entries per item in same table
  11. Conditional lead/lag function PostgreSQL?
  12. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  13. GROUP BY with MAX(DATE) [duplicate]
  14. How to use RETURNING with ON CONFLICT in PostgreSQL?
  15. PostgreSQL DISTINCT ON with different ORDER BY
  16. PostgreSQL: running count of rows for a query ‘by minute’
  17. Split comma separated column data into additional columns
  18. How to delete duplicate entries?
  19. Transpose latest rows per user to columns
  20. subquery in FROM must have an alias
  21. Compute percents from SUM() in the same SELECT sql query
  22. SQL Between clause with strings columns
  23. Insert an image in postgresql database
  24. Does the JDBC spec prevent ‘?’ from being used as an operator (outside of quotes)?
  25. Postgresql Column Not Found, But Shows in Describe
  26. INSERT COMMAND :: ERROR: column “value” does not exist
  27. Return multiple columns and rows from a function PostgreSQL instead of record
  28. Postgres error updating column data
  29. SQL LIKE condition to check for integer?
  30. Temporal database design, with a twist (live vs draft rows)

Leave a Comment