Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx
  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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux