Message-ID: <46852FD0.2030306@emolecules.com>
Date: Fri, 29 Jun 2007 09:14:08 -0700
From: Craig James <craig_james@emolecules.com>
User-Agent: Thunderbird 1.5.0.12 (X11/20070509)
MIME-Version: 1.0
To: "Dolafi, Tom" <dolafit@janelia.hhmi.org>
CC: Tom Lane <tgl@sss.pgh.pa.us>,  pgsql-performance@postgresql.org
Subject: Re: rtree/gist index taking enormous amount of space in
 8.2.3
References: <AE9860225100F14D87B26D0D4D6766DB46F246@EXCHANGE03.janelia.priv>
	<13616.1183046917@sss.pgh.pa.us>
	<AE9860225100F14D87B26D0D4D6766DB46F3E7@EXCHANGE03.janelia.priv>
In-Reply-To: <AE9860225100F14D87B26D0D4D6766DB46F3E7@EXCHANGE03.janelia.priv>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Dolafi, Tom wrote:
> min(fmin) |   max(fmin)    |    avg(fmin)      
>    1      |   55296469     |    11423945     
> 
> min(fmax) |   max(fmax)    |    avg(fmax)
>   18      |   55553288     |    11424491
> 
> There are 5,704,211 rows in the table.

When you're looking for weird index problems, it's more interesting to know if there are certain numbers that occur a LOT.  From your statistics above, each number occurs about 10 times in the table.  But do some particular numbers occur thousands, or even millions, of times?

Here is a query that will print a list of the highest-occuring values.  You might expect a few occurances of 20, and maybe 30, but if you have thousands or millions of occurances of certain numbers, then that can screw up an index.

   select fmax, c from
    (select fmax, count(fmax) as c from your_table group by fmax) as foo
   where c > 3 order by c desc;

Craig