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

Re: Index oddity (still)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: ken <southerland(at)samsixedd(dot)com>
Cc: Postgresql Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Index oddity (still)
Date: 2004-06-15 13:52:06
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
ken <southerland(at)samsixedd(dot)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

In response to

pgsql-performance by date

Next:From: AaronDate: 2004-06-15 15:16:06
Subject: Re: 50 000 000 Table entries and I have to do a keyword search HELP NEEDED
Previous:From: Tom LaneDate: 2004-06-15 13:23:19
Subject: Re: why is a constraint not 'pushed down' into a subselect when this subselect is using a 'group by' ??

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