Skip site navigation (1) Skip section navigation (2)

Re: rtree/gist index taking enormous amount of space in 8.2.3

From: Craig James <craig_james(at)emolecules(dot)com>
To: "Dolafi, Tom" <dolafit(at)janelia(dot)hhmi(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org
Subject: Re: rtree/gist index taking enormous amount of space in 8.2.3
Date: 2007-06-29 16:14:08
Message-ID: 46852FD0.2030306@emolecules.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2007-06-29 17:57:44
Subject: Re: rtree/gist index taking enormous amount of space in 8.2.3
Previous:From: Dolafi, TomDate: 2007-06-29 13:51:19
Subject: Re: rtree/gist index taking enormous amount of space in 8.2.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group