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