Обсуждение: Index Scan Backward

Поиск
Список
Период
Сортировка

Index Scan Backward

От
Luca Fabbro
Дата:
Hi all,
  I'm experiencing a strange problem in the usage of indexes for query
optimization.
I'm runnig a "forum" application that uses a PostgreSQL DB.
The version on the DB is 7.2.3 but I've also tested it under 7.3.1 but I
had no luck :( The problem is always the same.
Linux distro is Slackware 8.1
Since some days ago everithing was working fine but in this last 3 days
something really strnge happened.
Some of the SELECT queries became really slow.
In fact all the queries like this:

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;

Having found that the slow slect queries where this type I've tried the EXPLAIN

explain 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)
    ->  Nested Loop  (cost=0.00..392651.18 rows=391 width=454)
          ->  Index Scan Backward using forum_post_id_key on forum_post
p  (cost=0.00..35615.95 rows=60668 width=450)
          ->  Index Scan using forum_topic_id_key on forum_topic
t  (cost=0.00..5.87 rows=1 width=4)
                Index Cond: ("outer".topicid = t.id)
                Filter: (forumid = 44)
(6 rows)

It seems that the problem is in the Backward scan of the index :(

I've tried so to order the data by 'date' which is like ordering by id as
id is a serial

                                                   QUERY
PLAN
--------------------------------------------------------------------------------------------------------------
  Limit  (cost=1087.72..1087.72 rows=1 width=454)
    ->  Sort  (cost=1087.72..1088.70 rows=391 width=454)
          Sort Key: p.date
          ->  Nested Loop  (cost=0.00..1070.87 rows=391 width=454)
                ->  Index Scan using forum_topic_forumid on forum_topic
t  (cost=0.00..113.40 rows=37 width=4)
                      Index Cond: (forumid = 44)
                ->  Index Scan using forum_post_topicid on forum_post
p  (cost=0.00..25.82 rows=22 width=450)
                      Index Cond: (p.topicid = "outer".id)
(8 rows)

In this way the query is 3 time faster tha the one above wich is using index.
I do a VACUUM VERBOSE ANALYZE every night so de DB is "clean".
I've also tried to VACUUM or ANALYZE but had no benefits.

Did I miss something or is it a bug of postgres?

Thanks in advance

Ciao
    Luca


Re: Index Scan Backward

От
Tom Lane
Дата:
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?

            regards, tom lane

Re: Index Scan Backward

От
Luca Fabbro
Дата:
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


Re: Index Scan Backward

От
Tom Lane
Дата:
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

New User - Please Help

От
"Michael Cupp"
Дата:
I know this is extremely remedial and trival for most of you - but I am a
very new user, and need to know what I have to do to get my database instance
created?  Anyone that has a cheatsheet or something they can send me would
also be greatly appreciated.

I am a 10+ year veteran of Oracle 6.0 to 9i.

Thanks,
Michael


Re: New User - Please Help

От
Jeremy Buchmann
Дата:
Michael,

This is a good place to start:

http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/tutorial-
start.html

If you're still having trouble after reading all the docs, please feel
free to post a question to the list.

--Jeremy

On Monday, January 27, 2003, at 08:44 AM, Michael Cupp wrote:

> I know this is extremely remedial and trival for most of you - but I
> am a
> very new user, and need to know what I have to do to get my database
> instance
> created?  Anyone that has a cheatsheet or something they can send me
> would
> also be greatly appreciated.
>
> I am a 10+ year veteran of Oracle 6.0 to 9i.
>
> Thanks,
> Michael
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>


New User - Please Help

От
"Michael Cupp"
Дата:
I know this is extremely remedial and trival for most of you - but I am a
very new user, and need to know what I have to do to get my database instance
created?  Anyone that has a cheatsheet or something they can send me would
also be greatly appreciated.

I am a 10+ year veteran of Oracle 6.0 to 9i.


Thanks,
Michael

Re: Index Scan Backward

От
Luca Fabbro
Дата:
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


Re: New User - Please Help

От
"Nick Fankhauser"
Дата:
Michael-

This document should get you started:

http://www.postgresql.org/idocs/index.php?tutorial.html

look at section 1.3 in particular.

-Nick

> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Michael Cupp
> Sent: Monday, January 27, 2003 11:33 AM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] New User - Please Help
>
>
> I know this is extremely remedial and trival for most of you - but I am a
> very new user, and need to know what I have to do to get my
> database instance
> created?  Anyone that has a cheatsheet or something they can send
> me would
> also be greatly appreciated.
>
> I am a 10+ year veteran of Oracle 6.0 to 9i.
>
>
> Thanks,
> Michael
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>


Re: New User - Please Help

От
"dr. dave"
Дата:

Michael Cupp wrote:
> I know this is extremely remedial and trival for most of you - but I am a
> very new user, and need to know what I have to do to get my database instance
> created?  Anyone that has a cheatsheet or something they can send me would
> also be greatly appreciated.
>
> I am a 10+ year veteran of Oracle 6.0 to 9i.
>
>
> Thanks,
> Michael


You are probably looking for the initdb and createdb commands.  See, for example

http://www.linuxfocus.org/English/May1998/article38.html

about half-way down the introducton section.