| Importance of index unique scan (cost=0) [message #318593] |
Wed, 07 May 2008 04:32  |
santoshorcl
Messages: 7
Registered: August 2007
Location: pune |
Junior Member |
|
|
Hi,
As i saw few times cost of index unique scan shows cost=0. And i found such queries are performing slow. I check stats for all indexes/tables but stats was proper even though queries are not performing well.I am attaching plan. Please suggest me what to do.
Attachment: explan.txt
(Size: 6.44KB, Downloaded 19 time(s))
|
|
|
| Re: Importance of index unique scan (cost=0) [message #318629 is a reply to message #318593 ] |
Wed, 07 May 2008 06:20  |
smartin
Messages: 1758
Registered: March 2005
Location: Jacksonville, Florida |
Senior Member |
|
|
Are you sure your statistics are correct?
Reember that costs are just estimates, not an accurate predictor of absolute performance.
Also that even if an index scan only finds one row, it still must hit the table to get the rest of the data (in your example). BUT, then it must repeat that once for every time through the nested loop. So a one item index fetch that is fairly fast can all of a sudden be quite slow if repeated a million times.
Might want to run a 10046 trace and tkprof on it, and look in particular at the row source operation lines. Check those row counts against the estimated cardinalities in your explain plan. Does each and every step really only return one row?
|
|
|
| Re: Importance of index unique scan (cost=0) [message #318639 is a reply to message #318629 ] |
Wed, 07 May 2008 07:07 |
santoshorcl
Messages: 7
Registered: August 2007
Location: pune |
Junior Member |
|
|
Thanks for quick reply.
I am sure about stats of indexes/tables.
I too don't believe 100% on cost. But I didn't get the reason why the index unique scan cost shows 0 few times.
|
|
|
]
Members
Search
Help
Register
Login
Home
