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
