From: | jtp <john(at)akadine(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | on indexing. |
Date: | 2001-07-02 18:32:37 |
Message-ID: | Pine.BSF.4.21.0107021418140.3759-100000@db.akadine.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Clinton James | 2001-07-02 19:23:32 | RE: pqReadData() -- backend closed on COPY |
Previous Message | Robert Berger | 2001-07-02 18:14:48 | Re: query optimizer questions |