From: Tom Lane <tgl@sss.pgh.pa.us>
To: ken <southerland@samsixedd.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Index oddity (still)
Date: Tue, 15 Jun 2004 09:52:06 -0400
Message-ID: <735.1087307526@sss.pgh.pa.us> (raw)
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>
ken <southerland@samsixedd.com> 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
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: tgl@sss.pgh.pa.us, southerland@samsixedd.com
Subject: Re: Index oddity (still)
In-Reply-To: <735.1087307526@sss.pgh.pa.us>
* 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