Re: Generic Q about max(id) vs ORDER BY ID DESC LIMIT 1

Dang, that's a lot of answer! :-) and not what I was hoping for. Max
and count both have to look up data records to skip values associated
with other transactions. But count, by definition, has to scan every
single record from one end of the index to the other, so the index is
useless, whereas max will probably scan only a very few records before
finding the first valid one.

I can't see any difference between these two statements:

SELECT MAX(id) FROM table;
SELECT id FROM table ORDER BY id DESC LIMIT 1;

If the planner / optimizer / whatever doesn't optimize them to the
same end result, is there a reason not to? Is there a case for
putting it on the TODO list?

In case it is any help, here is the EXPLAIN ANALYZE results:

EXPLAIN ANALYZE SELECT id FROM transaction ORDER BY id DESC LIMIT 1;

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..1.98 rows=1 width=4) (actual time=22.482..22.485
rows=1 loops=1)
-> Index Scan Backward using transaction_pkey on "transaction"
(cost=0.00..1944638.42 rows=984531 width=4) (actual
time=22.474..22.474
rows=1 loops=1)
Total runtime: 22.546 ms
(3 rows)

----

EXPLAIN ANALYZE SELECT MAX(id) FROM transaction;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=52745.64..52745.64 rows=1 width=4) (actual
time=11500.994..11500.998 rows=1 loops=1)
-> Seq Scan on "transaction" (cost=0.00..50284.31 rows=984531
width=4) (actual time=57.164..8676.015 rows=738952 loops=1)
Total runtime: 11501.096 ms

And that's a good one - I've seen it take as long as 200000 ms...

--
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
Felix Finch: scarecrow repairman & rocket surgeon / felix(at)crowfix(dot)com
GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o