Re: Selecting max(pk) is slow on empty set

Alexander Staubo wrote:
> On 1/22/08, Richard Huxton <dev@archonet.com> wrote:
>> Although the row-estimate still seems quite high. You might want to
>> increase it even further (maximum is 1000). If this is a common query,
>> I'd look at an index on (user,id) rather than just (user) perhaps.
>
> Actually that index (with the same statistics setting as before)
> yields slightly worse performance:
>
> # explain analyze select max(id) from user_messages where user_id = 13604;
>  Total runtime: 0.128 ms
>
> Compare with the plain index on the one attribute:
>
> # explain analyze select max(id) from user_messages where user_id = 13604;
>  Total runtime: 0.085 ms

Ah, but:
1. Those times are so small, I'm not sure you can reliably separate
them. Certainly not from one run.
2. For a range of different user-ids I'd expect user_id_id index to
maintain a near-constant time regardless of the number of messages for
that user.
3. You might be able to reduce your statistics on the user column and
still keep the fast plan.

--
   Richard Huxton
   Archonet Ltd