From: | Marcus Mascari <mascarim(at)yahoo(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | PLEASE...SOMEONE COMMENT: PostgreSQL 6.4BETA not using indexes with subselects |
Date: | 1998-10-31 19:16:17 |
Message-ID: | 19981031191617.12680.rocketmail@send1d.yahoomail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-general pgsql-hackers |
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(at)yahoo(dot)com)
Date:
Tue, 27 Oct 1998 17:30:44 -0800 (PST)
From:
Marcus Mascari <mascarim(at)yahoo(dot)com>Add to Address Book
Subject:
Please help (Someone?) - subselect not using indexes
To:
pgsql-hackers(at)postgresql(dot)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(at)yahoo(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | T.Steubesand | 1998-11-02 14:05:33 | Outer joins |
Previous Message | Andy Lewis | 1998-10-30 17:22:52 | Authentication Problem |
From | Date | Subject | |
---|---|---|---|
Next Message | Troy Hanson | 1998-10-31 19:30:24 | Re: [GENERAL] SCO Unix 3.2 support? |
Previous Message | Troy Hanson | 1998-10-31 19:11:51 | SCO Unix 3.2 support? |
From | Date | Subject | |
---|---|---|---|
Next Message | Nick Bastin | 1998-10-31 19:33:49 | A couple of questions... |
Previous Message | The Hermit Hacker | 1998-10-31 18:10:48 | Re: [HACKERS] HPUX 10.01 build issues and solutions. |