Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id C4C859FA281
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri,  9 Mar 2007 11:21:44 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024)
	with ESMTP id 96696-02-6
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri,  9 Mar 2007 11:21:21 -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 28C0A9FB593
	for <pgsql-performance@postgresql.org>;
	Fri,  9 Mar 2007 11:21:22 -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 BEE6173C4E;
	Fri,  9 Mar 2007 15:21:20 +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 A5FBA27403D;
	Fri,  9 Mar 2007 15:21:20 +0000 (GMT)
Message-ID: <45F17B70.60001@archonet.com>
Date: Fri, 09 Mar 2007 15:21:20 +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>
In-Reply-To: <45F179AB.7040000@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/197
X-Sequence-Number: 23602

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

-- 
   Richard Huxton
   Archonet Ltd