Index Scan Backward

Поиск
Список
Период
Сортировка
От Luca Fabbro
Тема Index Scan Backward
Дата
Msg-id 5.2.0.9.0.20030127140822.02fe5828@mail.conecta.it
обсуждение исходный текст
Ответы Re: Index Scan Backward
Список pgsql-admin
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


В списке pgsql-admin по дате отправления:

Предыдущее
От: Michiel Lange
Дата:
Сообщение: Re: Cannot connect to the database (PG 7.3)
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Cannot connect to the database (PG 7.3)