Message-ID: <48039A4C.2000304@archonet.com>
Date: Mon, 14 Apr 2008 18:54:20 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.12 (X11/20080213)
MIME-Version: 1.0
To: Adam Gundy <adam@starsilk.net>
CC: pgsql-performance@postgresql.org
Subject: Re: varchar index joins not working?
References: <6f55f1270804092013n73ee1f81t2c6d72cee49655a3@mail.gmail.com>	
	<47FDD3D1.9070208@archonet.com> <47FE29AF.6010906@starsilk.net>
	<6f55f1270804101154t3d4a01eco2b7bff7a0053af38@mail.gmail.com>
	<48030439.9040309@archonet.com> <48038E21.50606@starsilk.net>
In-Reply-To: <48038E21.50606@starsilk.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Adam Gundy wrote:
> I doubt we could get stats stable enough for this. the number of groups 
> will hopefully be much larger at some point.

The pg_stats table should be recording the n most-common values, so if 
you have 1 million groups you track details of the 1000 most-common. 
That gives you a maximum for how common any value not in the stats can be.

>> No, but let's see what's in pg_stats.
> 
> no real help there. either it hits the group being read, and does a good 
> plan, or it doesn't, and tries to seqscan (unless I disable it). even 
> forcing stats to 1000 only bandaids the situation, given the number of 
> groups will eventually exceed that..

Like I say, that's not the point of gathering the stats. If one group 
represents 95% of your rows, then its group-id should be almost certain 
to occur in the stats. Are you saying that's not happening with your data?

-- 
   Richard Huxton
   Archonet Ltd