Re: Index Scan Backward

Luca Fabbro <lfabbro@conecta.it> writes:
>   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

Hm.  So the reason this is slow is it has to go back quite far in the id
index before it finds something from forumid 44.  The system is in fact
estimating it as a moderately expensive query --- but not quite
expensive enough.  You might try raising RANDOM_PAGE_COST a little to
see if that brings the cost estimates in line with reality.

            regards, tom lane