Re: Index Scan Backward

At 10.17 27/01/2003 -0500, you wrote:
> > 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.

Thanks again Tom.
I'm trying to find out the best values to assign to the
RANDOM_PAGE_COST and related vars to have the best performances.
You were right in fact queries where the last data insertion was not so
"old" are quick while the "oldest" one using Backwards Indexing were really
slow. The problem is that changing these vaules affects also queries where
the use of Index in standard way improves a lot the speed, in changing the
values sometimes it happens that this kind of queries are made as
sequential scan and so they slow down and sometime the overall computation
time is higher than the one with no indexes at all.
I'll see what I can do :)
P.S. Is there any place where the configuration vars are explained in a
more detailed way than in the offical manual?

Thanks
Luca