Re: Deceiding which index to use

From: Mezei Zoltán
Subject: Re: Deceiding which index to use
Date: ,
Msg-id: 45F180D2.5080201@telefor.hu
(view: Whole thread, Raw)
In response to: Re: Deceiding which index to use  (Richard Huxton)
Responses: Re: Deceiding which index to use  (Richard Huxton)
Re: Deceiding which index to use  (Alvaro Herrera)
List: pgsql-performance

Tree view

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">OK
-so the next place to look is the distribution of values for<br /> subscriber_id on the output_message_log. Does that
havesome subscribers<br /> with many rows and lots with hardly any?</font><small>Hmm... There are about 1.5k
subscriberswith 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...</small><br /><blockquote
cite="mid:"type="cite"><p><font size="2">If so, you might need to<br /> increase the stats
onthat column:<br /><br /> ALTER TABLE output_message_log ALTER COLUMN subscriber_id SET STATISTICS<br />
<num>;<br/> ANALYSE output_message_log (subscriber_id);<br /><br /> The <num> defaults to 10, but can be
setas high as 1000. You want to<br /> try and capture the "big" subscribers.<br /></font></blockquote><small>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
subsciberswith high message count.<br /><br /> Zizi<br /></small> 

pgsql-performance by date:

From: Scott Marlowe
Date:
Subject: Re: configuring new server / many slow disks?
From: Andreas Tille
Date:
Subject: PostgreSQL in virtual machine