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