Improving PostgreSQL Aggregate Performance

Question

What's the best way to increase the speed of a query in PostgreSQL that's performing a MAX(id) aggregation?

I have a modest number of records associated with an id, which I can COUNT() in a second e.g.

select count(id) as cnt from mytable where ref_id=2660

row   cnt
1     2844

However, when I try and find the most recent record id using MAX(), the query takes nearly 5 minutes.

select max(id) as id from mytable where ref_id=2660

This is surprising, because I've otherwise found PG surprisingly fast with much more complicated queries. Why would there be such a difference in the query times, especially for such a relatively small number of records? What would be the best way to improve this performance?

EDIT: This is the query plan for the above MAX() select:

"Result  (cost=219.84..219.85 rows=1 width=0)"
"  InitPlan 1 (returns $0)"
"    ->  Limit  (cost=0.00..219.84 rows=1 width=4)"
"          ->  Index Scan Backward using mytable_pkey on mytable  (cost=0.00..773828.42 rows=3520 width=4)"
"                Filter: ((id IS NOT NULL) AND (ref_id = 2660))"

The (ref_id, id) index worked! Set that as your answer and I'll accept it. — Cerin
– Cerin, Commented Feb 20, 2011 at 22:33

S-Man · Accepted Answer · 2018-09-22 18:49:11Z

3

I googled around, seems like PostgreSQL (up to 8.4) doesn't like MAX and MIN, it does a sequential scan of the table to get the result. It's hard to say that it's your case without the query plan and the version.

You can try this workaround.

SELECT id from mytable WHERE ref_id=2660 ORDER BY id DESC LIMIT 1

Edit: Make sure you have an index with (ref_id, id), otherwise a table scan/sort is inevitable.

edited Sep 22, 2018 at 18:49

S-Man

24k9 gold badges51 silver badges78 bronze badges

answered Feb 20, 2011 at 22:04

arthurprs

4,6473 gold badges28 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cerin Over a year ago

This takes about a minute to run, which is a lot faster than my query, but still otherwise slow.

Pikachu · Accepted Answer · 2011-06-18 18:46:31Z

0

I am using Postgres 8.4 and can say it may be a bug in Postgres optimizer to not using indexes for queries envolving min and max agregation functions. After changing my queries from
Select max(field) from table to
Select field from table order by field limit 1
my query execution time improved from 10s to less than a second. Of course You might define and index for the column in question, otherwise postgres will do a seq_scan.

answered Jun 18, 2011 at 18:46

Pikachu

77413 silver badges15 bronze badges

Collectives™ on Stack Overflow

Improving PostgreSQL Aggregate Performance

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related