the latest_revision is trigger maintained by an insert trigger on cr_revisions so the first one is better. It is likely to cause a table scan on cr_revisions though since it uses the
max function.
Here are some numbers from openacs.org for looking up the latest bug revision (this is with ~20k cr_items):
explain analyze select name from cr_items where latest_revision = (
select max(latest_revision) from cr_items
where content_type = 'bt_bug_revision');
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using cr_items_by_latest_revision on cr_items (cost=509.17..513.41 rows=1 width=18) (actual time=65.113..65.120 rows=1 loops=1)
Index Cond: (latest_revision = $0)
InitPlan
-> Aggregate (cost=509.17..509.17 rows=1 width=4) (actual time=64.865..64.866 rows=1 loops=1)
-> Seq Scan on cr_items (cost=0.00..503.70 rows=2187 width=4) (actual time=0.203..59.467 rows=2369 loops=1)
Filter: ((content_type)::text = 'bt_bug_revision'::text)
Total runtime: 65.292 ms
And here is the better version:
explain analyze select name from cr_items where latest_revision = (
select latest_revision from cr_items where content_type =
'bt_bug_revision' order by latest_revision desc limit 1);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using cr_items_by_latest_revision on cr_items (cost=0.43..4.67 rows=1 width=18) (actual time=2.029..2.033 rows=1 loops=1)
Index Cond: (latest_revision = $0)
InitPlan
-> Limit (cost=0.00..0.43 rows=1 width=4) (actual time=1.986..1.987 rows=1 loops=1)
-> Index Scan Backward using cr_items_by_latest_revision on cr_items (cost=0.00..936.33 rows=2187 width=4) (actual time=1.980..1.980 rows=1 loops=1)
Filter: ((content_type)::text = 'bt_bug_revision'::text)
Total runtime: 2.143 ms
so using order and limit instead of max is about 30x faster :)
As a general rule you want to avoid these sorts of functions
in postgres if you can since they typically trigger table scans and you want to use an index if one exists.
