Return-Path: owner-postman
Received: from localhost (localhost [127.0.0.1]) by nobozo.CS.Berkeley.EDU (8.6.4/8.6.3) with SMTP id MAA19031 for postgres-dist; Thu, 10 Mar 1994 12:01:07 -0800
Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199403102001.MAA19031@nobozo.CS.Berkeley.EDU>
X-Authentication-Warning: nobozo.CS.Berkeley.EDU: Host localhost didn't use HELO protocol
Sender: owner-postman@postgres.Berkeley.EDU
X-Return-Path: owner-postman
Received: from kaukau.comp.vuw.ac.nz (kaukau.comp.vuw.ac.nz [130.195.5.20]) by nobozo.CS.Berkeley.EDU (8.6.4/8.6.3) with ESMTP id MAA19022 for <postgres@nobozo.CS.Berkeley.EDU>; Thu, 10 Mar 1994 12:01:02 -0800
Received: from downstage.comp.vuw.ac.nz (downstage.comp.vuw.ac.nz [130.195.6.10]) by kaukau.comp.vuw.ac.nz (8.6.4/8.6.4) with ESMTP id JAA02047 for <postgres@nobozo.berkeley.edu>; Fri, 11 Mar 1994 09:00:25 +1300
From: Aaron Roydhouse <Aaron.Roydhouse@Comp.VUW.AC.NZ>
Received: from downstage.comp.vuw.ac.nz (aaron@localhost) by downstage.comp.vuw.ac.nz (8.6.4/8.6.4) with ESMTP id JAA08182 for <postgres@nobozo.berkeley.edu>; Fri, 11 Mar 1994 09:00:40 +1300
Message-Id: <199403102000.JAA08182@downstage.comp.vuw.ac.nz>
To: postgres@postgres.Berkeley.EDU
Subject: Hoping the optimiser will consider my index
Date: Fri, 11 Mar 1994 09:00:37 +1300
Resent-To: postgres-dist@postgres.Berkeley.EDU
Resent-Date: Thu, 10 Mar 94 12:01:07 -0800
Resent-XMts: smtp

I have a relation with a field of type "box"

Relation = region

  +-----------------+----------+
  |     Field       |   Type   |
  +-----------------+----------+
  | bounds          | box      |
  | centre          | point    |
  | classification  | int4     |
  | clipped         | bool     |
  | constant        | float8   |
  | depth           | float8   |
  | parent          | oid      |
  | shape           | polygon  |
  | situation       | oid      |
  | unit            | char16   |
  +-----------------+----------+

If I try the following query 

retrieve (r.bounds) from r in region where "(130,-45,135,-50)"::box && r.bounds

I get the instances I expect (about 100 from ~5500 instances)

Here's the plan:

Plan is :
#S(seqscan :cost 346284 :size 0 :width 32 :fragment 0 :parallel 1 :state nil :qptargetlist ((#S(resdom :resno 1 :restype 603 :rescomplex nil :reslen 32 :resname "bounds" :reskey 0 :reskeyop 0 :resjunk 0)#S(var :varno 1 :varattno 8 :vartype 603 :varid (1 8 )))) :qpqual ((#S(oper :opno 500 :opid 125 :oprelationlevel nil :opresulttype 16)#S(const :consttype 603 :constlen 32 :constisnull nil :constvalue  32 { 64 96 -32 0 0 0 0 0 -64 70 -128 0 0 0 0 0 64 96 64 0 0 0 0 0 -64 73 0 0 0 0 0 0 }  :constbyval nil)#S(var :varno 1 :varattno 8 :vartype 603 :varid (1 8 )))) :lefttree nil  :righttree nil  :scanrelid 1)

Now I define an index as follows

define index reg_bounds on region using rtree (bounds box_ops)

and repeat the query and get the same results, however when I look at
the query plan I find it doesn't seem to have used the index, in fact
the plan is identical:

Plan is :
#S(seqscan :cost 346284 :size 0 :width 32 :fragment 0 :parallel 1 :state nil :qptargetlist ((#S(resdom :resno 1 :restype 603 :rescomplex nil :reslen 32 :resname "bounds" :reskey 0 :reskeyop 0 :resjunk 0)#S(var :varno 1 :varattno 8 :vartype 603 :varid (1 8 )))) :qpqual ((#S(oper :opno 500 :opid 125 :oprelationlevel nil :opresulttype 16)#S(const :consttype 603 :constlen 32 :constisnull nil :constvalue  32 { 64 96 -32 0 0 0 0 0 -64 70 -128 0 0 0 0 0 64 96 64 0 0 0 0 0 -64 73 0 0 0 0 0 0 }  :constbyval nil)#S(var :varno 1 :varattno 8 :vartype 603 :varid (1 8 )))) :lefttree nil  :righttree nil  :scanrelid 1)

I had hoped postgres would consider the rtree index for the &&
operator, as discussed on p.77 of the reference manual. Is there
something wrong?

Thanks,
Aaron.