Re: [ADMIN] Index Scan Backward

[Luca Fabbro]

At 09.39 27/01/2003 -0500, you wrote:

Luca Fabbro <[EMAIL PROTECTED]> 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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])