Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id CD33D9FB842 for ; Fri, 29 Jun 2007 15:38:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 83053-06 for ; Fri, 29 Jun 2007 15:38:00 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 67EC49FB641 for ; Fri, 29 Jun 2007 15:38:13 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l5TIcBsm006933; Fri, 29 Jun 2007 14:38:12 -0400 (EDT) To: "Dolafi, Tom" cc: pgsql-performance@postgresql.org, "Oleg Bartunov" , "Teodor Sigaev" Subject: Re: rtree/gist index taking enormous amount of space in 8.2.3 In-reply-to: References: <13616.1183046917@sss.pgh.pa.us> <6410.1183139864@sss.pgh.pa.us> Comments: In-reply-to "Dolafi, Tom" message dated "Fri, 29 Jun 2007 14:13:23 -0400" Date: Fri, 29 Jun 2007 14:38:11 -0400 Message-ID: <6932.1183142291@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/648 X-Sequence-Number: 25681 "Dolafi, Tom" writes: > In the mean time I've dropped the index which has resulted in overall > performance gain on queries against the table, but we have not tested > the part of the application which would utilize this index. I noted that with the same (guessed-at) distribution of fmin/fmax, the index size remains reasonable if you change the derived boxes to CREATE OR REPLACE FUNCTION boxrange(integer, integer) RETURNS box AS 'SELECT box (point($1, $1), point($2, $2))' LANGUAGE 'sql' STRICT IMMUTABLE; which makes sense from the point of view of geometric intuition: instead of a bunch of very tall, mostly very narrow, mostly overlapping boxes, you have a bunch of small square boxes spread out along a line. So it stands to reason that a geometrically-motivated index structure would work a lot better on the latter. I don't know though whether your queries can be adapted to work with this. What was the index being used for, exactly? regards, tom lane