Subject: Hoping the optimiser will consider my index

From: Aaron Roydhouse <[email-masked]>
Date: Fri, 11 Mar 1994 09:00:37 +1300

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.

---

From: Aaron Roydhouse <[email-masked]>
Date: Fri, 11 Mar 1994 11:32:52 +1300

From: [email-masked] (Paul M. Aoki)
>
> Aaron Roydhouse <[email-masked]> writes:
> this is going to sound stupid, but what happens when you reverse
> the left/right arguments of &&?

Du-oh, I guess I should have thought of that. This does the trick!

Given an index such as

Then this query

will not use the index, but this query

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

will (I've attached the resulting plans below).

Paul, thanks for your help, perhaps the team should put out an
informal `Rules of Engagement' for the postgres optimiser :-) 

Is there any other gotchas or query forming advice available?
I remember multijoins are better chained that centralised
(a = b, b = c is better than a = b, a = b)

Aaron.
----------------------------------------

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)
        ProcessQuery() at Thu Mar 10 21:52:28 1994

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

Plan is :
#S(indexscan :cost 1183.61 :size 1797 :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 nil  :lefttree nil  :righttree nil  :scanrelid 1 :indxid (546368 ) :indxqual (((#S(oper :opno 500 :opid 125 :oprelationlevel nil :opresulttype 16)#S(var :varno 1 :varattno 1 :vartype 603 :varid (1 8 ))#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)))))

---

From: Gerhard Daub <[email-masked]>
Date: Fri, 11 Mar 1994 08:50:48 +0100

>From [email-masked]

I would be very interested in information about the query optimizer, too!

Gerhard