Re: Index Scan Backward - Mailing list pgsql-admin

At 09.39 27/01/2003 -0500, you wrote:
>Luca Fabbro <lfabbro@conecta.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