Thread
-
Re: [HACKERS] has anybody else used r-tree indexes in 6.5?
Tom Lane <tgl@sss.pgh.pa.us> — 1999-06-18T23:35:43Z
Jeff Hoffmann <jeff@remapcorp.com> writes: > has something changed with r-tree indexes in 6.5? > ERROR: Operator 500 must have a restriction selectivity estimator to be > used in a btree index What we have here is a big OOOPS. The reference to "btree" is coming from btreesel, which it turns out is called by rtreesel as well --- I missed that the first time around. But that's just a cosmetic problem in the error message. The real problem is that none of the operators that are used for rtree indexes have restriction selectivity estimators. They *used* to have selectivity estimators in 6.4.2 --- they all pointed at intltsel, which is pretty much completely inappropriate for an area- based comparison. I believe I must have removed those links from the pg_operator table during one of my cleanup-and-make-consistent passes. The right fix would be to put in an appropriate selectivity estimator, but we can't do that as a 6.5.* patch because changing pg_operator requires an initdb. It will have to wait for 6.6. (One of my to-do items for 6.6 was to rewrite the selectivity estimators anyway, so I'll see what I can do.) In the meantime, I think the only possible patch is to disable the error check in btreesel and have it return a default selectivity estimate instead of complaining. Drat. Apparently, none of the regression tests exercise rtree indexes at all, else we'd have known there was a problem. Adding an rtree regression test seems to be strongly indicated as well... regards, tom lane
-
Re: [HACKERS] has anybody else used r-tree indexes in 6.5?
Bruce Momjian <maillist@candle.pha.pa.us> — 1999-06-19T00:16:15Z
> The right fix would be to put in an appropriate selectivity estimator, > but we can't do that as a 6.5.* patch because changing pg_operator > requires an initdb. It will have to wait for 6.6. (One of my to-do > items for 6.6 was to rewrite the selectivity estimators anyway, so I'll > see what I can do.) In the meantime, I think the only possible patch is > to disable the error check in btreesel and have it return a default > selectivity estimate instead of complaining. Drat. > > Apparently, none of the regression tests exercise rtree indexes at all, > else we'd have known there was a problem. Adding an rtree regression test > seems to be strongly indicated as well... Sounds like a good fix. Bypass the system tables, since we can't change them, and hard-wire a selectivity. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
-
RE: [HACKERS] has anybody else used r-tree indexes in 6.5?
Stupor Genius <stuporg@erols.com> — 1999-06-19T00:27:16Z
> Jeff Hoffmann <jeff@remapcorp.com> writes: > > has something changed with r-tree indexes in 6.5? > > ERROR: Operator 500 must have a restriction selectivity estimator to be > > used in a btree index > > ... > > The right fix would be to put in an appropriate selectivity estimator, > but we can't do that as a 6.5.* patch because changing pg_operator > requires an initdb. It will have to wait for 6.6. Would it possible to write an sql script to put in the changes? Seems it should be possible to do this for any non-code changes that affect the system tables. Darren
-
Re: [HACKERS] has anybody else used r-tree indexes in 6.5?
Bruce Momjian <maillist@candle.pha.pa.us> — 1999-06-19T01:04:16Z
[Charset iso-8859-1 unsupported, filtering to ASCII...] > > Jeff Hoffmann <jeff@remapcorp.com> writes: > > > has something changed with r-tree indexes in 6.5? > > > ERROR: Operator 500 must have a restriction selectivity estimator to be > > > used in a btree index > > > > ... > > > > The right fix would be to put in an appropriate selectivity estimator, > > but we can't do that as a 6.5.* patch because changing pg_operator > > requires an initdb. It will have to wait for 6.6. > > Would it possible to write an sql script to put in the changes? > > Seems it should be possible to do this for any non-code changes > that affect the system tables. Yes, we could, and have in the past had SQL scripts that are run as part of the upgrade, but this fix is easier to do in C because it doesn't require that difficult step for upgraders. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
-
Re: [HACKERS] has anybody else used r-tree indexes in 6.5?
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-06-19T02:20:18Z
> What we have here is a big OOOPS. > The right fix would be to put in an appropriate selectivity estimator, > but we can't do that as a 6.5.* patch because changing pg_operator > requires an initdb. It will have to wait for 6.6. (One of my to-do > items for 6.6 was to rewrite the selectivity estimators anyway, so I'll > see what I can do.) Uh, I think we *should* do it as a patch, just not one applied to the cvs tree for the v.6.5.x branch. Let's apply it to the main cvs branch once we do the split, and Jeff can use a snapshot at that time (since it will strongly resemble v6.5 and since he wants the capability). In the meantime, can you/we develop a set of patches for Jeff to use? Once we have them, we can post them into ftp://postgresql.org/pub/patches, which probably needs to be cleaned out from the v6.4.x period. Let me know if I can help with any of this... > In the meantime, I think the only possible patch is > to disable the error check in btreesel and have it return a default > selectivity estimate instead of complaining. Drat. ... and let's use this solution for the v6.5.x branch, once it comes into being. - Thomas -- Thomas Lockhart lockhart@alumni.caltech.edu South Pasadena, California -
Re: [HACKERS] has anybody else used r-tree indexes in 6.5?
Bruce Momjian <maillist@candle.pha.pa.us> — 1999-06-19T02:55:33Z
> > In the meantime, I think the only possible patch is > > to disable the error check in btreesel and have it return a default > > selectivity estimate instead of complaining. Drat. > > ... and let's use this solution for the v6.5.x branch, once it comes > into being. He already did this, Thomas. The 6.5.x branch is currently our only active branch. Any patches now applied appear in 6.5.x and 6.6. We will split it when someone needs to start on 6.6-only features. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
-
Re: [HACKERS] has anybody else used r-tree indexes in 6.5?
frankpit@pop.dn.net — 1999-06-19T21:12:11Z
I read through some of the papers about R-trees and GIST about a year ago, and it seems that estimating costs for R-tree searches (and GIST searches) is not so straightforward as B-Trees. Hellerstein et al. 1995 write "...currently such estimates are reasonably accurate for B+ trees and less so for R-Trees. Recently, some work on R-tree cost estimation has been done by [FK94], but more work is required to bring this to bear on GISTs in general...." The reference that they give is [FK94] Christos Faloutsos and Ibrahim Kamel. "Beyond Uniformity and Independence: Analysis of R-trees using the concept of fractal dimension. Proc. 13th ACM SIGACT-SIGMOD-SIGART Symposium on Principles of Database Systems, pp 4--13, Minneapolis, May 1994 I don't have the Faloustos paper. The R-tree code authors, and the GIST authors just used the B-Tree code as an expedient solution. Bernie Frankpitt
-
Re: [HACKERS] has anybody else used r-tree indexes in 6.5?
Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu> — 1999-06-21T05:07:32Z
On Sat, Jun 19, 1999 at 09:12:11PM +0000, Bernard Frankpitt wrote: > I read through some of the papers about R-trees and GIST about a year > ago, > and it seems that estimating costs for R-tree searches (and GIST > searches) is > not so straightforward as B-Trees. > > Hellerstein et al. 1995 write > "...currently such estimates are reasonably accurate for B+ trees > and less so for R-Trees. Recently, some work on R-tree cost > estimation has been done by [FK94], but more work is required to bring > this to bear on GISTs in general...." > > The reference that they give is > > [FK94] Christos Faloutsos and Ibrahim Kamel. "Beyond Uniformity and > Independence: Analysis of R-trees using the concept of fractal > dimension. > Proc. 13th ACM SIGACT-SIGMOD-SIGART Symposium on Principles of Database > Systems, pp 4--13, Minneapolis, May 1994 > Hmm, a quick Google search for these two authors hit on a great index server in Germany: http://www.informatik.uni-trier.de/~ley/db/index.html http://www.informatik.uni-trier.de/~ley/db/indices/a-tree/f/Faloutsos:Christos.htm And that paper in particular: http://www.informatik.uni-trier.de/~ley/db/conf/pods/pods94-4.html Which gives an abstract, access to an electronic version (ACS membership required) and a cite for a more recent (1997) journal paper. HTH, Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
-
Re: [HACKERS] has anybody else used r-tree indexes in 6.5?
Jeff Hoffmann <jeff@remapcorp.com> — 1999-06-21T15:41:09Z
Tom Lane wrote: > > Jeff Hoffmann <jeff@remapcorp.com> writes: > > has something changed with r-tree indexes in 6.5? > > ERROR: Operator 500 must have a restriction selectivity estimator to be > > used in a btree index > > What we have here is a big OOOPS. i guess so. the patch works fine, though, so no big deal. i thought it was weird that i hadn't heard it come up before since it didn't seem like something i could have caused, but you never know. > Apparently, none of the regression tests exercise rtree indexes at all, > else we'd have known there was a problem. Adding an rtree regression test > seems to be strongly indicated as well... i noticed this when i ran the regression tests and everything came out ok, but forgot to mention it. if i recall correctly, what's actually in the geometry regression test is pretty weak. i think it only really tests some of the common cases, not all of the functions. it's probably not a high priority item, though, since, judging by how long it took for this bug to surface, there aren't a lot of people using the geometry functions/types.