Обсуждение: Index Scan Backward
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
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
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
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
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
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 >
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
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
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 >
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.