Re: Stupid database use the index!

On Thu, 24 Aug 2000, Mike Mascari wrote:

> I suspect there is a type conversion issue here. I believe 3315 will
> get converted implicitly to an int4 (in the parser). If the type of the
> field isn't an int4, then the database has to use a sequential scan
> because the indexes are built using non-int4 comparitive functions.
> Meanwhile, the quoted version gets automatically type-cast
> appropriately after parsing and can then use the index.
>
> Hope that helps,

It did. I've got a new one now.

I changed the type to integer, it didn't need to be bigint.

Here is another problem:

First some schema:

Table "apprsl_accounts"
Attribute | Type | Modifier
--------------------+---------------+----------
account_nbr | integer |
old_account_nbr | char(20) |
owner_nbr | integer |
geo_nbr | char(30) |
mpin_nbr | char(30) |
street_nbr | numeric(8,0) |
street_sub_nbr | char(4) |
direction_cd | char(3) |
street_name | char(22) |
city_locn_cd | char(2) |
state_cd | char(2) |
zip_code | char(10) |
emer_911_locn | char(7) |
section_cd | char(2) |
township_cd | char(3) |
range_cd | char(3) |
subdivision_cd | char(7) |
lot_nbr | char(4) |
block_nbr | char(4) |
plat_ind | char(1) |
roll_cd | char(1) |
neighborhood_cd | char(10) |
zoning_cd | char(10) |
map_nbr | char(5) |
business_as | char(30) |
business_type_cd | char(4) |
owner_type_cd | char(2) |
account_type_cd | char(4) |
multiple_owner_ind | char(1) |
reason_cd | char(2) |
inactive_year | char(4) |
added_dt | date |
agent_cd | char(6) |
payor_cd | char(6) |
mortgage_loan_nbr | char(15) |
interest_type_cd | char(1) |
owner_interest | numeric(7,6) |
assoc_account | integer |
division_order_nbr | char(15) |
tract_nbr | char(6) |
change_dt | date |
operator | char(10) |
market_area | char(4) |
ceiling_value | numeric(11,0) |
sinking_ceiling | numeric(11,0) |
appraisal_dt | date |
appraiser_cd | char(3) |
contact_nm | char(30) |
supp_dt | date |
supp_reason_cd | char(2) |
millage_cd | char(4) |
bldg_permit_ind | char(1) |
ceiling_year | char(4) |
ceiling_amt | numeric(8,2) |
right_survivor | char(1) |
remove_exem_ag | char(1) |
state_reporting_cd | char(6) |
render_dt | date |
notice_date | date |
notice_reason | char(2) |
sales_indicator | char(1) |
multi_values | integer |
multi_taxval | integer |
assign_apr_cd | char(3) |
assign_apr_dt | date |
assign_status_cd | char(2) |
will_ref | char(9) |
deed_ref | char(9) |
deeded_acreage | numeric(14,3) |
upd_operator | char(10) |
upd_date | date |
Index: apprsl_accounts_account_nbr

(not many indexes because I just recreated the tables, only doing
important indexes first)

Table "apprsl_featval"
Attribute | Type | Modifier
----------------+---------------+----------
account_nbr | integer |
property_group | char(1) |
card_nbr | integer |
line_nbr | integer |
feature_cd | char(10) |
quantity | numeric(14,3) |
added_value | numeric(11,0) |
added_ag_value | numeric(11,0) |
asset_nbr | char(12) |
year_acquired | char(4) |
screensequence | integer |
ukey | bigint |
Indices: apprsl_featval_account_nbr,
apprsl_featval_feature_cd,
apprsl_featval_feature_cd_quant,
apprsl_featval_quantity

This looks good:

explain select
apprsl_accounts.geo_nbr,apprsl_accounts.street_nbr,apprsl_accounts.direction_cd,apprsl_accounts.street_name
from apprsl_accounts where apprsl_accounts.account_nbr = 1001;
NOTICE: QUERY PLAN:

Index Scan using apprsl_accounts_account_nbr on apprsl_accounts
(cost=0.00..4.80 rows=1 width=48)

EXPLAIN

Why this:

explain select
apprsl_accounts.geo_nbr,apprsl_accounts.street_nbr,apprsl_accounts.direction_cd,apprsl_accounts.street_name
from apprsl_featval,apprsl_accounts where apprsl_featval.feature_cd ~
'^THEATER' and apprsl_accounts.account_nbr = apprsl_featval.account_nbr;
NOTICE: QUERY PLAN:

Hash Join (cost=19.62..15626.53 rows=5879 width=56)
-> Seq Scan on apprsl_accounts (cost=0.00..10108.59 rows=83459
width=52)
-> Hash (cost=4.93..4.93 rows=5879 width=4)
-> Index Scan using apprsl_featval_feature_cd_quant on
apprsl_featval (cost=0.00..4.93 rows=5879 width=4)

EXPLAIN

The tables have been indexed. Why doesn't it use the index on
apprsl_accounts rather then Seq scanning it.

--
The comments and opinions expressed herein are those of the author of this
message and may not reflect the policies of the Martin County Board of
County Commissioners.