From: Craig James <craig_james@emolecules.com>
To: Dolafi, Tom <dolafit@janelia.hhmi.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: rtree/gist index taking enormous amount of space in 8.2.3
Date: Fri, 29 Jun 2007 09:14:08 -0700
Message-ID: <46852FD0.2030306@emolecules.com> (raw)
In-Reply-To: <AE9860225100F14D87B26D0D4D6766DB46F3E7@EXCHANGE03.janelia.priv>
References: <AE9860225100F14D87B26D0D4D6766DB46F246@EXCHANGE03.janelia.priv>
<13616.1183046917@sss.pgh.pa.us>
<AE9860225100F14D87B26D0D4D6766DB46F3E7@EXCHANGE03.janelia.priv>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig_james@emolecules.com, dolafit@janelia.hhmi.org, tgl@sss.pgh.pa.us
Subject: Re: rtree/gist index taking enormous amount of space in 8.2.3
In-Reply-To: <46852FD0.2030306@emolecules.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox