Index Scan Backward

From: Luca Fabbro <lfabbro(at)conecta(dot)it>
To: pgsql-admin(at)postgresql(dot)org
Subject: Index Scan Backward
Date: 2003-01-27 13:26:07
Message-ID: 5.2.0.9.0.20030127140822.02fe5828@mail.conecta.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2003-01-27 14:10:50 Re: Cannot connect to the database (PG 7.3)
Previous Message Michiel Lange 2003-01-27 13:19:36 Re: Cannot connect to the database (PG 7.3)