hello list,
since indexing seems to be a few current threads, a quick question for
those in the mindset of dealing with them.
i have a multiple index on a customer record table.
table a:
id #, first name, last name, zip+4, adress, etc.
i have and index across name and zip+4 and last name for the benefit (read
laziness) of data entry personnel. last name is a varchar(15) and zip+4
is a varchar(10).
if i do a select incorperating both fields it uses an index scan no
problem, but when i do a select using only a portion of a field i get
varying results.
such as.
If i
SELECT * FROM house WHERE lname LIKE 'HU%';
the query planner uses and indexed search, but if i
SELECT * from house where zip+4 like '08035%';
the query planner suggests a sequential search.
What is the threshold, is there a threshold when selecting on the zip+4
would become feasible for an index scan?
The output from the explains is as follows:
EXPLAIN SELECT * FROM house WHERE lname LIKE 'HU%';
Index scan using h_lname_zip_key on house (cost 0.00..2313.05 rows 96
width=121)
EXPLAIN SELECT * FROM house WHERE zip LIKE '08035%';
Seq Scan on house (cost 0.00..14135.48 rows=17 width=121)
Thanks in advance.
.jtp
