

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 unusable.

(NOTE: This occurs regardless of whether or not it
is executed immediately following a VACUUM)

Are there any patches available to fix this problem?
Will this fixed in the 6.4 release?

Thanks for any information,

Marcus Mascari
(mascarim@yahoo.com)                                  



   Date:
       Tue, 27 Oct 1998 17:30:44 -0800 (PST)
  From:
       Marcus Mascari <mascarim@yahoo.com>Add to Address Book
 Subject:
       Please help (Someone?) - subselect not using indexes
    To:
       pgsql-hackers@postgresql.org



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 unusable.

(NOTE: This occurs regardless of whether or not it
is executed immediately following a VACUUM)

Are there any patches available to fix this problem?
Will this fixed in the 6.4 release?

Thanks for any information,

Marcus Mascari
(mascarim@yahoo.com)




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


       Message - Download
                                                                     

          
                                                                Back
to Sent 



Copyright ��� 1997-98 Yahoo! Inc. All rights reserved. 







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


Sat, 31 Oct 1998 11:16:17 -0800 (PST)
Marcus Mascari <mascarim@yahoo.com>
PLEASE...SOMEONE COMMENT: PostgreSQL 6.4BETA not using indexes with
	subselects