Skip site navigation (1) Skip section navigation (2)

Re: Index Scan Backward

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Luca Fabbro <lfabbro(at)conecta(dot)it>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Index Scan Backward
Date: 2003-01-27 15:17:39
Message-ID: 26516.1043680659@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-admin
Luca Fabbro <lfabbro(at)conecta(dot)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

In response to

Responses

pgsql-admin by date

Next:From: Michael CuppDate: 2003-01-27 16:33:08
Subject: New User - Please Help
Previous:From: Luca FabbroDate: 2003-01-27 14:52:03
Subject: Re: Index Scan Backward

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group