Stupid database use the index!

Why is this?

web=# explain select account_nbr from apprsl_accounts where account_nbr =
3315;
NOTICE: QUERY PLAN:

Seq Scan on apprsl_accounts (cost=0.00..10317.24 rows=1 width=8)

EXPLAIN
web=# explain select account_nbr from apprsl_accounts where account_nbr =
'3315';
NOTICE: QUERY PLAN:

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

EXPLAIN

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,

Mike Mascari

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.