[postgis-users] Help with Bad Query Plan

[Obe, Regina]

>  i) PostgreSQL 8.3 has a new costing model which we have seen in some 
> cases causes evaluation of the more expensive _st_intersects()
function 
> above the && operator. As yet we don't really have a system that we
can 
> use to verify this properly :(

> However, if you still have your original .sql scripts around, I would 
> suggest trying to load them into a PostgreSQL 8.2 installation and 
>  posting the results back to the list so we can determine if this is
the 
>  case or not.

Mark,

Slight correction.  I don't believe this statement to be true.

&& from all my experience in 8.3 still evaluates first before
_ST_Intersects.  Where you get bitten is 
that _ST_Intersects sometimes evaluates before other things.

I think like if you had something like

WHERE  ST_Intersects(...) AND somedate between a and b


Then in 8.2 if you had an index on somedate,  ST_Intersects would run
second regardless of the order of your
conditions.  In 8.3 if you put ST_Intersects first because we didn't
define a costing for ST_Intersects -- it often evaluates first.
which would be bad since an indexed somedate would be a better choice to
check first.
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.