Subject: Access Methods

From: [email-masked] (Chris Bunting 61-89-895442)
Date: Mon, 28 Jun 93 16:22:16 -0700

I have a problem and was wondering if anyone had any ideas on possible solutions.
It goes like this

I have a class
	create OBJECT( bbox     =     BOX, m_type   =     int4)

and it has the following indices 

define index obj_bbox on OBJECT using rtree (bbox box_ops)
define index obj_mtype on OBJECT using btree (m_type int4_ops)

the idea being to store the bounding box of spatial data of different types
hence the m_type.

The query optimiser seems to always give the rtree index a higher priority
than the btree and I want it to have the opposite behaviour.

Does anyone have any suggestions.

Thanks

Chris Bunting
Database Manager
NT Dept of Mines and Energy
PO Box 2901
DARWIN NT 0801
AUSTRALIA
email    bunting.dme.nt.gov.au
voice    61-89-895442
fax      61-89-817861

---

From: Mike Olson <[email-masked]>
Date: Mon, 28 Jun 1993 16:35:42 -0700

> Does anyone have any suggestions.

if you do

	replace pg_class (relpages = 10000, reltuples = 100000)
		where pg_class.relname = "obj_bbox"

you'll make the rtree less attractive, since the planner will assume
that it'll have to do much more i/o to use the rtree than the btree.
the down side is that you make the rtree less attractive than sequential
scans, as well.  you can adjust the constants up or down to see what
works for you.
					mike

---

From: [email-masked] (Chris Bunting 61-89-895442)
Date: Mon, 28 Jun 93 20:19:48 -0700

mao writes
> scans, as well.  you can adjust the constants up or down to see what

I still want to use the rtee access method in preference to sequential scan
but with less of a priority than btrees.

Could you suggest numbers to achieve this, or even some documentation that 
would allow me to work it out for myself.

Thanks
Chris Bunting
Database Manager