Re: Index Scan Backward

At 09.39 27/01/2003 -0500, you wrote:
>Luca Fabbro <lfabbro(at)conecta(dot)it> writes:
> > It seems that the problem is in the Backward scan of the index :(
>
>It looks like a pretty reasonable plan to me. Could we see the output
>of EXPLAIN ANALYZE, not just EXPLAIN?

Thanks Tom for your interest.

It looks resonable also for me, but it's not too efficient.

> regards, tom lane

Here are the detailed explain

explain analyze SELECT p.* FROM forum_post AS p, forum_topic AS t WHERE
p.topicid = t.id AND t.forumid = 44 ORDER BY p.id DESC LIMIT 1;
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..1003.36 rows=1 width=454) (actual time=806.78..2097.61
rows=1 loops=1)
-> Nested Loop (cost=0.00..392651.18 rows=391 width=454) (actual
time=806.77..2097.59 rows=2 loops=1)
-> Index Scan Backward using forum_post_id_key on forum_post
p (cost=0.00..35615.95 rows=60668 width=450) (actual time=0.41..1122.09
rows=42322 loops=1)
-> Index Scan using forum_topic_id_key on forum_topic
t (cost=0.00..5.87 rows=1 width=4) (actual time=0.02..0.02 rows=0 loops=42322)
Index Cond: ("outer".topicid = t.id)
Filter: (forumid = 44)
Total runtime: 2098.14 msec

explain analyze SELECT p.* FROM forum_post AS p, forum_topic AS t WHERE
p.topicid = t.id AND t.forumid = 44 ORDER BY p.date DESC LIMIT 1;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1087.72..1087.72 rows=1 width=454) (actual time=1.89..1.90
rows=1 loops=1)
-> Sort (cost=1087.72..1088.70 rows=391 width=454) (actual
time=1.89..1.89 rows=2 loops=1)
Sort Key: p.date
-> Nested Loop (cost=0.00..1070.87 rows=391 width=454) (actual
time=0.64..1.11 rows=6 loops=1)
-> Index Scan using forum_topic_forumid on forum_topic
t (cost=0.00..113.40 rows=37 width=4) (actual time=0.27..0.28 rows=2 loops=1)
Index Cond: (forumid = 44)
-> Index Scan using forum_post_topicid on forum_post
p (cost=0.00..25.82 rows=22 width=450) (actual time=0.22..0.37 rows=3 loops=2)
Index Cond: (p.topicid = "outer".id)
Total runtime: 2.06 msec