Re: Deceiding which index to use

Поиск
Список
Период
Сортировка
От Mezei Zoltán
Тема Re: Deceiding which index to use
Дата
Msg-id 45F179AB.7040000@telefor.hu
обсуждение исходный текст
Ответ на Re: Deceiding which index to use  (Richard Huxton <dev@archonet.com>)
Ответы Re: Deceiding which index to use  (Richard Huxton <dev@archonet.com>)
Список pgsql-performance
Richard Huxton wrote: <blockquote cite="mid:45F1775A.8030701@archonet.com" type="cite"></blockquote><p><font
size="2">Anddoes the planner know that?<br /> SELECT * FROM pg_stats WHERE tablename='subscriber' AND
attname='anumber';<br/> It's the n_distinct you're interested in, and perhaps most_common_freqs.</font><br
/><small>n_distinctis -0.359322 and most_common_vals contains about 10 different anumbers (which are corretct),
most_common_freqsare between 0.01 and 0.001. What does n_distinct exactly mean? Why is it negative?</small><br
/><blockquotecite="mid:45F1775A.8030701@archonet.com" type="cite"><p><font size="2">> Nothing, everything is the
same- the problem lies on the other table's index<br /> > usage, using this index is fine.<br /><br /> The planner
hasto guess how many matches it will have for<br /> subscriber=5555555. Based on that choice, it will either:<br />   
a.Do the join, then find the highest crd values (sort)<br />    b. Scan the crd values backwards and then join<br />
It'schosen (b) because it's estimating the numbers of matches<br /> incorrectly. I'm wondering whether the system can't
seethrough your<br /> function-call (the cast to numeric) to determine how many matches it's<br /> going to get for any
givenvalue.<br /></font></blockquote><small>It can see through the cast - I have just tried to create the same database
omittingthe non-numeric anumbers and the results are the same.</small><br /><blockquote
cite="mid:45F1775A.8030701@archonet.com"type="cite"><p><font size="2">If the system can't be persuaded into getting its
estimatesmore<br /> accurate, it might be worth trying an index on (subscriber_id,crd) and<br /> dropping the index on
(crd)- if that's reasonable for your query patterns.<br /></font></blockquote><small>I'll try that one if the negative
n_distinctvalue can be a correct one :-)<br /><br /> Zizi</small><br /> 

В списке pgsql-performance по дате отправления:

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: Deceiding which index to use
Следующее
От: Richard Huxton
Дата:
Сообщение: Re: Deceiding which index to use