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

Alexander Staubo wrote:
> # explain analyze select max(id) from user_messages where user_id = 13604;
>
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------
> Result (cost=633.19..633.20 rows=1 width=0) (actual
> time=339160.704..339160.704 rows=1 loops=1)
> InitPlan
> -> Limit (cost=0.00..633.19 rows=1 width=4) (actual
> time=339160.700..339160.700 rows=0 loops=1)
> -> Index Scan Backward using user_messages_pkey on
> user_messages (cost=0.00..633188.12 rows=1000 width=4) (actual
> time=339160.697..339160 Filter: ((id IS NOT NULL) AND
> (user_id = 13604))
> Total runtime: 339160.770 ms
> (6 rows)
>
> Note that it's using the correct index -- user_messages_pkey is on the
> id attribute. (Why rows=1000 here?)

1000 looks suspiciously like a default estimate if the planner knows no
better. Odd since you say that you've just analysed.

Do you have an index on user_id? Presumably that's what's being used in
the case of SELECT * or count(*).

What cost does the count(*) come up with?

Can you trick it with a sub-query (to see the explain)?
SELECT max(id) FROM (SELECT id FROM user_messages WHERE user_id = 13604)
AS foo;

--
Richard Huxton
Archonet Ltd