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

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedNov 4, '98 at 7:33p
activeNov 4, '98 at 7:33p
posts1
users1
websitepostgresql.org...
irc#postgresql

1 user in discussion

Marcus Mascari: 1 post

People

Translate

site design / logo © 2022 Grokbase