Re: Deceiding which index to use

[Mezei Zoltán <mezei.zoltan@xxxxxxxxxx>]

Title: Re: [PERFORM] Deceiding which index to use

Richard Huxton wrote:

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?

Hmm... There are about 1.5k subscribers with 100-200 messages each - all the other 19k has an average of 8.9 messages, most of them having only 1 message. I think that's exactly the situation you mention...

If so, you might need to
increase the stats on that column:

ALTER TABLE output_message_log ALTER COLUMN subscriber_id SET STATISTICS
<num>;
ANALYSE output_message_log (subscriber_id);

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

So if I'm correct: this statistics gathering can be fine tuned, and if i set the <num> to 1000 then not only the first 10 subsribers (with most messages) will be stored in pg_stats, but the first 1000? Is 1000 a hard-coded highest-possible-value? I think it would be best to set that to simething like 1800-1900 as I have about that many subscibers with high message count.

Zizi