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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
