Re: Deceiding which index to use

Mezei Zoltán wrote:
> Richard Huxton wrote:
>>
>> And does the planner know that?
>> SELECT * FROM pg_stats WHERE tablename='subscriber' AND attname='anumber';
>> It's the n_distinct you're interested in, and perhaps most_common_freqs.
>>
> n_distinct is -0.359322 and most_common_vals contains about 10 different 
> anumbers (which are corretct), most_common_freqs are between 0.01 and 0.001. 
> What does n_distinct exactly mean? Why is it negative?

It's saying that it's a ratio, so if you doubled the number of 
subscribers it would expect that the number of unique anumber's would 
double too. So you've got about 36% of the rows with unique values - 
pretty much what you said earlier. That's not bad, since the planner 
only uses an estimate.

OK - so the next place to look is the distribution of values for 
subscriber_id on the output_message_log. Does that have some subscribers 
with many rows and lots with hardly any? If so, you might need to 
increase the stats on that column:

ALTER TABLE output_message_log ALTER COLUMN subscriber_id SET STATISTICS 
;
ANALYSE output_message_log (subscriber_id);

The  defaults to 10, but can be set as high as 1000. You want to 
try and capture the "big" subscribers.

-- 
   Richard Huxton
   Archonet Ltd