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 по дате отправления: