subselect BROKEN under 6.4: postgres v. Oracle

I was hoping someone could shed some light on the
following problem:

The subselect under 6.4 ignores indexes built on
tables.  For example, consider the following two
tables:

\d sales;

Table    = sales

supplysource   varchar() not null  16
supply         varchar() not null  16
supplyunit     varchar() not null  2
quantity       float8    not null  8
target         varchar() not null  16
costcntr       varchar() not null  8
saletype       varchar() not null  16
saledate       datetime  not null  8

Indices:  k_sales
          k_sales_saledate
          k_sales_supply
          k_sales_target


\d locations;

Table    = locations
location       varchar() not null   16
costcntr       varchar() not null   5
supplypath     varchar() not null   64
formpath       varchar() not null   64
engineerpath   varchar() not null   64

Indices:  k_locations
          k_locations_costcntr
          k_locations_location

with the following query:

explain select supply from sales where target in
(select location from locations);
NOTICE:  QUERY PLAN:

Seq Scan on sales  (cost=5738.60 size=116806 width=12)
  SubPlan
    ->  Seq Scan on locations  (cost=7.49 size=136 width=12)

EXPLAIN

This results in a full table scan on the outer table
where there are 150K rows.  As a result, it is the
equivalent of a full table scan on 150K (sales) *
100 (locations) rows = 15,000,000 row table scan.

Oracle 8.0.5.00 for Linux Early Adaptor Program
correctly uses the index on the same two tables
resulting on a sequential access on the subselected
table (locations) and an index look-up on the
select table (sales), according to EXPLAIN PLAN.

Due to the failure of PostgreSQL 6.4 to correctly
use indexes with subselects, it makes subselects
practically useless.

Are there any patches available to fix this problem?

Thanks for any information,

Marcus Mascari
(mascarim@yahoo.com)














_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com