Re: Stupid database use the index! - Mailing list pgsql-general

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.