Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 4D6589FBC57
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri,  9 Mar 2007 11:52:43 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 53053-01 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri,  9 Mar 2007 11:52:19 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by postgresql.org (Postfix) with ESMTP id 89E989FBC67
	for <pgsql-performance@postgresql.org>;
	Fri,  9 Mar 2007 11:52:25 -0400 (AST)
Received: from server3.office.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id F07C773BCF;
	Fri,  9 Mar 2007 15:52:23 +0000 (GMT)
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by server3.office.archonet.com (Postfix) with ESMTP id CB5A827403D;
	Fri,  9 Mar 2007 15:52:23 +0000 (GMT)
Message-ID: <45F182B7.30507@archonet.com>
Date: Fri, 09 Mar 2007 15:52:23 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: IceDove 1.5.0.9 (X11/20061220)
MIME-Version: 1.0
To: =?UTF-8?B?TWV6ZWkgWm9sdMOhbg==?= <mezei.zoltan@telefor.hu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Deceiding which index to use
References: <45F15F0F.8080403@telefor.hu> <45F1715E.1050000@archonet.com>
	<45F174CF.8010302@telefor.hu> <45F1775A.8030701@archonet.com>
	<45F179AB.7040000@telefor.hu> <45F17B70.60001@archonet.com>
	<45F180D2.5080201@telefor.hu>
In-Reply-To: <45F180D2.5080201@telefor.hu>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.202 tagged_above=0 required=5 tests=AWL, BAYES_50,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200703/199
X-Sequence-Number: 23604

Mezei Zoltán wrote:
> 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...

[snip alter table ... set statistics]

> 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.

There is a cost to increasing the stats values, otherwise it'd already 
be set at 1000. In your case I'm not sure if 100-200 vs 8-9 messages is 
enough to skew things. Only one way to find out...

-- 
   Richard Huxton
   Archonet Ltd