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
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) |