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)
Ответы: Re: Deceiding which index to use  (Richard Huxton)
Список: pgsql-performance

Скрыть дерево обсуждения

Deceiding which index to use  (Mezei Zoltán, )
 Re: Deceiding which index to use  (Richard Huxton, )
  Re: Deceiding which index to use  (Mezei Zoltán, )
   Re: Deceiding which index to use  (Richard Huxton, )
    Re: Deceiding which index to use  (Mezei Zoltán, )
     Re: Deceiding which index to use  (Richard Huxton, )
      Re: Deceiding which index to use  (Mezei Zoltán, )
       Re: Deceiding which index to use  (Richard Huxton, )
        Re: Deceiding which index to use  (Mezei Zoltán, )
         Re: Deceiding which index to use  (Richard Huxton, )
       Re: Deceiding which index to use  (Alvaro Herrera, )
        Re: Deceiding which index to use  (Mezei Zoltán, )
         Re: Deceiding which index to use  (Richard Huxton, )

Richard Huxton wrote: <blockquote cite="mid:" 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:" 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:"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 по дате сообщения:

От: Scott Marlowe
Дата:
Сообщение: Re: configuring new server / many slow disks?
От: Andreas Tille
Дата:
Сообщение: PostgreSQL in virtual machine