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

Re: Index Scan Backward

From: Luca Fabbro <lfabbro(at)conecta(dot)it>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Index Scan Backward
Date: 2003-01-28 08:58:06
Message-ID: 5.2.0.9.0.20030128095146.02b7ac10@mail.conecta.it (view raw or flat)
Thread:
Lists: pgsql-admin
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


In response to

pgsql-admin by date

Next:From: Nick FankhauserDate: 2003-01-28 09:27:24
Subject: Re: New User - Please Help
Previous:From: Jerry AsherDate: 2003-01-28 04:53:39
Subject: Re: restricting identd to just the loopback adapter.

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