X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 49B7FD1B7BC for ; Tue, 15 Jun 2004 10:52:11 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 67145-05 for ; Tue, 15 Jun 2004 13:52:06 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 7F9FED1B34B for ; Tue, 15 Jun 2004 10:52:02 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i5FDq6WX000736; Tue, 15 Jun 2004 09:52:06 -0400 (EDT) To: ken Cc: Postgresql Performance Subject: Re: Index oddity (still) In-reply-to: <1087256828.28557.115.camel@pesky> References: <1086809460.32077.246.camel@pesky> <1086811930.2539.90.camel@jester> <1086814209.32077.252.camel@pesky> <1086814597.2539.92.camel@jester> <1086814928.32077.259.camel@pesky> <19759.1086843718@sss.pgh.pa.us> <1087256828.28557.115.camel@pesky> Comments: In-reply-to ken message dated "Mon, 14 Jun 2004 16:47:08 -0700" Date: Tue, 15 Jun 2004 09:52:06 -0400 Message-ID: <735.1087307526@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/156 X-Sequence-Number: 7214 ken writes: > Is there something wrong with rtree indexes on box data types? Not per se, but the selectivity estimator for && is just a stub :-(. Feel free to take a stab at improving this, or take a look at PostGIS --- I think those guys are working the same problem even now. > However, even if I *drop* the rtree index on the boundingbox > column, so that it can't use that index, the optimizer does not choose > the other index. Instead it reverts to doing a sequential scan of the > entire table and its really slow. This is also a lack-of-statistical-knowledge problem. The optimizer has no stats about the distribution of length(lseg(boundingbox)) and so it does not realize that it'd be reasonable to use that index for a query like "length(lseg(boundingbox)) > largevalue". As of 7.5 this issue will be addressed, but in existing releases I think you could only get that index to be used by forcing it with enable_seqscan = off :-( As a short-term workaround it might be reasonable to store that length as an independent table column, which you could put an index on. You could use triggers to ensure that it always matches the boundingbox. regards, tom lane